Import Excel Sheet

[Jim]

How do I reference the Sheets(Index) in a VBA
Docmd.Transferspreadsheet command? Thanks.

 

[Ken Snell]

You don't do it directly.

What you need to do is to use a variable that gets the actual name of the
each sheet in the index and then use that variable to concatenate with the
"!" ending delimiter (and perhaps to surround the text string with ' marks
if it contains spaces), and then use that concatenated variable as the
argument for the sheet range in the TransferSpreadsheet command.

Something like this:

For lngSheet = 1 To Excel.Workbooks("NameOfFile").Worksheets.Count
strSheet = Excel.Workbooks("NameOfFile").Worksheets(lngSheet).Name
If InStr(strSheet, " ") > 0 Then strSheet = "'" & strSheet & "'"
strSheet = strSheet & "!"
DoCmd.TransferSpreadsheet Range:=strSheet, .....
Next lngSheet

(.... should be replaced by the other arguments).

 

[jim]

Thanks, worked great.

-----Original Message-----
You don't do it directly.

What you need to do is to use a variable that gets the actual name of the
each sheet in the index and then use that variable to concatenate with the
"!" ending delimiter (and perhaps to surround the text string with ' marks
if it contains spaces), and then use that concatenated variable as the
argument for the sheet range in the TransferSpreadsheet command.

Something like this:

For lngSheet = 1 To Excel.Workbooks ("NameOfFile").Worksheets.Count
strSheet = Excel.Workbooks("NameOfFile").Worksheets (lngSheet).Name
If InStr(strSheet, " ") > 0 Then strSheet = "'" & strSheet & "'"
strSheet = strSheet & "!"
DoCmd.TransferSpreadsheet Range:=strSheet, .....
Next lngSheet

(.... should be replaced by the other arguments).


--
Ken Snell
<MS ACCESS MVP>




.