VBA Import Excel Files - Update Worksheet Names?

[Dulanic]

Hello,

I have 4 excel files I run /w VBA to import. The issue is 2 of the files have their worksheet name change every day by the system I am exporting them from. My original macro was very simple...

DoCmd.RunSavedImportExport "123"
DoCmd.RunSavedImportExport "456"
DoCmd.RunSavedImportExport "789"

So the problem would be the saved imports dont work for those 2 files since the worksheet name changed. So I was able to create a file in excel /w this macro...

Sub Update()
    Const fPath As String = "C:\Access\Volumes\"
    Dim sh As Worksheet
    Dim sName As String
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    sName = Dir(fPath & "*.xls*")
 
    Do Until sName = ""
        If Not sName = "UpdateSheets.xlsx" Then
            With GetObject(fPath & sName)
                .Sheets(1).Name = "1"
                .Close True
            End With
 
        End If
 
        sName = Dir
    Loop
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub

So now my problem is it is in excel... I don't know if it is even possible to write that up in access to be honest. I tried patching that up by doing this....

Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:\Access\Volumes\UpdateSheets.xlsm")
xl.Visible = True
xl.Run "UpdateSheets.xlsm!Update"
xl.ActiveWorkbook.Close (True)
xl.Quit
Set xl = Nothing

But when I run this I get a runtime error 440 Automation Error /w
xl.Run "UpdateSheets.xlsm!Update". The macro runs... but once it is done is when it throws the automation error. If I run it myself from excel it works so I am stumped.... any easier way to work around the changing worksheet name issue?

 

[spikepl]

Put in an error handler into your Excel code, to see what it is unhappy about. Also, why doing all this in blind, while testing? Comment out
Application.ScreenUpdating = False Application.DisplayAlerts = False. Actually - this would be the first step.

 

[Dulanic]

Well i found a big issue that my xl.workbook open code causes my excel to go crazy and it makes excel open with a blank window. No idea why but the only fix is to delete my windows profile. So i need to remove all that code. So going back, is there any way to import excel files with a static name but variable worksheet name?

 

[spikepl]

TransferSpreadsheet method allows you to pick file and sheet.

Otherwise you can link to an Excel file, or go the whole hog and read it from Access. I don't have any "read" code, but here is Bob Larson's export code. You should be able to do something with it. Perhaps rename or get the names of each sheet at least. Or else google.