Importing sheets from Excel with VBA (code in here just need help)

[Vexorg]

Hello,

Originally I used the below code to import sheets from Excel into Access... The sheets were named as follows 'Result 1', 'Result 2' etc ..

Now I want to import a range of sheets (3-15) that have all different types of names. I don't understand how to use a changing variable as the name to import.

Please help

Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 11
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "\\msad\root\NA\NY\LIB\fid\FIDSTP\Centralized Reference Data\Clear Exports\core.xls", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub

 

[Norie]

You need to add $ not ! after the sheet name.

 

[Vexorg]

Still confused.. should this


strSheet = "Result " & x & "!"


be

strSheet = "$"

because that didnt work

</pre>

 

[Norie]

Why have you replaced what you had with the single character '$'?

What I suggested, and what you need, is to replace "!" with "$".

 

[Vexorg]

Hey yea i did that first but it didnt work. I get an error that pairs up the first part of the name "Result " with the sheet range. So IE my error says 'Cannot find Sheet "Result 1"

This is what i did:

Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 11
        strSheet = "Result " & x & "$"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "\\msad\root\NA\NY\LIB\FID\FIDSTP\Confirm Renov\Catch All\FCO DB\5-2011 MF VS FCO Comparison.xlsx", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub

I removed everything but as you can see its concatenating Result and $. My sheets names are similar to below:
UK FID
US IED
AUS

 

[Norie]

If the sheet names are like that why are you using this to get the sheet names?

strSheet "Result " & x & "$"

Which will return Result 1$, Result 2$....

Or are you actually trying to import named ranges?

 

[Vexorg]

Sorry maybe i was unclear. I orginally used the code to import sheets that were named Result 1, 2, etc. I would now like to adapt this code to pull the sheets that have names like those mentioned above, that have no uniform. I would like to import sheets 3 to xx, so i was asking what i need to change to import mutiple sheets with unique names.

 

[Norie]

Quite a lot.

Where are the sheet names stored?

If they aren't stored anywhere the you would need code to get them.

There are a few ways to do that in code.

I've only really ever used two:

- ADOX

- automating Excel

Obviously if you do have the names stored somewhere or don't mind typing them in you wouldn't need either of those.

PS You would be typing them in to create an array.

 

[Vexorg]

The sheet names are stored in the sheet. Ultimately I want to import sheets 3-13, regardless of the name.