Pull Data Into Table From Excel File With Changing Sheet Name

[EnginerdUNH]

Hi,

I have a database that I am working with that I am looking to set up some VBA code that will pull data into a temporary table in the database from an excel file. The piece that I am working with is that the excel file is updated every day with new data being added on a new sheet so the sheet name that the data would be pulled in from is not constant. I feel like setting up the code for this should be fairly easy to figure out but for the life of me I am not seeing it in my head. I know that the sheet to be pulled from will always be the last sheet in the sheet index. Could it be as simple as specifying the sheet name by the index? Or am I off track?

[HansV]

Here is a template, it uses late binding so you don't have to set a reference to the Excel object library.

Code: Select all

Sub Test()
    Dim objXL As Object ' Excel.Application
    Dim objWB As Object ' Excel.Workbook
    Dim objWS As Object ' Excel.Worksheet
    Dim f As Boolean

    On Error Resume Next
    Set objXL = GetObject(Class:="Excel.Application")
    If objXL Is Nothing Then
        Set objXL = CreateObject(Class:="Excel.Application")
        f = True
    End If
    On Error GoTo ErrHandler

    Set objWB = objXL.Workbooks.Open("...")
    Set objWS = objWB.Worksheets(objWB.Worksheets.Count)
    ' Do something with objWS
    ' ...

ExitHandler:
    On Error Resume Next
    objWB.Close SaveChanges:=False
    If f Then
        objXL.Quit
    End If
    Exit Sub

ErrHandler:
    MsgBox Err.deq, vbExclamation
    Resume ExitHandler
End Sub

[EnginerdUNH]

Thanks Hans! I think what you've provided is something I can work with!