Import a particular excel sheet from list of sheets in workbook using Access VBA

[ria.arora]

I'm importing data from Excel to Access using Access VBA which works fine that sheet name is first sheet in workbook. However, if multiple sheets exists in the workbook and that particular sheet can be 2 sheet, 3 sheet or any sheet. In that case how to search particular sheet name and import in access. Below is the code I have used for importing the data.

    objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    sTmpTableName, sInput_Dir & "\" & sInputFileName, True, sFile1Wks & "!"

 

[Trevor G]

If you know the data will always be on the actual sheet you can use the real sheet name rather than the Tab Sheet name.

So rather than refer to sheets("Sheet1") which is the tab sheet name you can use sheets(1) which is the real sheet name.

When you look at the workbook in the VBA screen in the Project window you will sheet sheet names like this:

Sheet1(Sheet1)
Sheet2(Sheet2)

If you renamed the sheet in the tab it would look like this

Sheet1(Test)
Sheet2(Test2)

So to use the real name it would be

Sheets(1)
Sheets(2)

 

[Richard1980]

Hello,
I would suggest to create a combobox with a valuelist containing all worksheets of the file to be opened.
To do so, you should look into the workbook and then loop the worksheets collection to get all worksheets' names.
When they are loaded in the combobox you can then select one desidered and launch the file opening using the "DoCmd.TransferSpreasheet" command. It requires the filename and
Here below an example code (hope no errors in it) to search for worksheets names:

[FONT=Arial][SIZE=2]Dim xlApp As Object Dim Wkb As Object Dim Sh As Object Dim strName As String
 
Set xlApp = CreateObject("Excel.Application") Set Wkb= xlApp.Workbooks.Open("HereGoesYour complete path & file name", , False)   For Each Sh In Wkb.Sheets Sh.name
strName = strName & Sh.Name ";"
Next

YourComboBoxName.rowsourcetype = "Value list"
YourComboBoxName.rowsource = strName

oWkb.Close True xlApp.Quit Set oWkb = Nothing Set xlApp = Nothing[/SIZE][/FONT]

Please correct me if there are mistakes in the code. Bye.
Riccardo

 

[vbaInet]

You've got all the code you need now but how do you identify which worksheet you wish to import?

 

[Richard1980]

You've got all the code you need now but how do you identify which worksheet you wish to import?

If you populate your combobox with all worksheets, you can select one of them and then import it using DoCmd.TransferSpreadsheet.
Instead of using a range (used for example if you have in a named range in a sheet) you can put the worksheet's name with a final exclamation point (!) or a $ mark, I don't know precisely.
Example:

DoCmd.TransferSpreadsheet acImport, "YourTableNameinAccess", "Path&FileName", "SheetName!"

Bye.

 

[vbaInet]

If you populate your combobox with all worksheets, you can select one of them and then import it using DoCmd.TransferSpreadsheet.
Instead of using a range (used for example if you have in a named range in a sheet) you can put the worksheet's name with a final exclamation point (!) or a $ mark, I don't know precisely.
Example:

DoCmd.TransferSpreadsheet acImport, "YourTableNameinAccess", "Path&FileName", "SheetName!"

Bye.

Richard, that question was directed at the poster.