Import Excel Sheet

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

How do I reference the Sheets(Index) in a VBA
Docmd.Transferspreadsheet command? Thanks.
 
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).
 
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>




.
Click to expand...
 
Back
Top