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?
Pull Data Into Table From Excel File With Changing Sheet Name
-
- StarLounger
- Posts: 88
- Joined: 14 Aug 2019, 00:12
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pull Data Into Table From Excel File With Changing Sheet Name
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 88
- Joined: 14 Aug 2019, 00:12
Re: Pull Data Into Table From Excel File With Changing Sheet Name
Thanks Hans! I think what you've provided is something I can work with!