Transferspreadsheet by Sheet number, not sheet name

[Monsora83]

The sheet number 2 is constantly made with different names and I was wondering if the transferspreadsheet command would be able to pick out data by the sheet number instead of the sheet name.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_EXCELDATA", "MyFile.xls", True, "Sheet2!" (Sheet is name Data_1***, *** is constantly changing)

Thanks.

 

[Beetle]

AFAIK you have to use the sheet name, but you could prompt the user for it each time (just a thought anyway);

Dim sSheetName As String

sSheetName = InputBox("Enter Workbook Sheet Name")

If Nz(sSheetName, "") <> "" Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Tbl_EXCELDATA", "MyFile.xls", True, sSheetName
End If

 

[Monsora83]

I should have specified. there are a few thousand of these excel sheets and I'm trying to get it done automatically.

If it is not possible to reference the sheet number, is there a way to read the sheet names in an excel workbook. I could then extract the proper sheet name since they all start with the same basic 'DATA_1' setup for each sheet.

AFAIK you have to use the sheet name, but you could prompt the user for it each time (just a thought anyway);

Dim sSheetName As String
 
sSheetName = InputBox("Enter Workbook Sheet Name")
 
If Nz(sSheetName, "") <> "" Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Tbl_EXCELDATA", "MyFile.xls", True, sSheetName
End If

 

[Monsora83]

Ah, I think I figured out a workaround.

1. I will make a workbook called: 'Master Workbook.xls' (Bleh whatever)
2. Write a macro in the workbook called: 'SheetDeleteMacro'
The code for this will be:

sub SheetDeleteMacro
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

3. I will have access scan all the folders/subfolders for the names of all the files I want and populate a table with their names and locations (already have in my database for other uses).
4. I will make a copy of the excel file and paste it into a new location.
5. I will open this newly copied workbook and run the macro 'Master Workbook!SheetDeleteMacro'
6. Save and close the new workbook, then do the trasnfer spreadsheet with no sheet name defined, thusly importing 'Sheet2' as if it was 'Sheet1'.
7. Get some peace and quiet from data requests.