Transfer Excel Worksheet

[Guest]

When transfering data in from excel is there a way to refer to a specific
sheet and not a specific range. The excel file I want to import from has
several worksheets and I do not want to have the user set a named range as
the range changes. I just want to be able to say the file name and worksheet
name.

The stCombine below is the file path with subdirectories.

DoCmd.TransferSpreadsheet acImport, 8, "Ssaintv", stCombine, True, ""


Thank you for any information...

 

[Guest]

This did not work.. I am in Windows 2000 and Office xp.

It says no such object.

i know if you do it manually then you can select a worksheet.. but do not
see it with the docmd. ???
Any more info.

 

[Guest]

Hey FGM, try using "Sheet1$":

DoCmd.TransferSpreadsheet acImport, 8, "Ssaintv", stCombine, True, "Sheet1$"

This will transfer sheet 1 regardless of the name. If you get another error,
post the rest of the code as it may be linked to something else.

 

[Guest]

I don't understand why. Here is one I use regularly. Windows XP Pro, Office
2003, Access 2003. It was also used when we were on Office 2000 and Access
2000. Note the spreadsheet type is 9. I tried using 8 and it still worked.
I believe 8 is pre 97, or at least pre 2000.

docmd.TransferSpreadsheet
acImport,acSpreadsheetTypeExcel9,"_Toss","E:\Vxxxxx 2005\2005
Actuals\September\Vxxxxx Invoice September 2005.xls",true,"actuals_res_export"

 

[Guest]

The Range argument can refer to a sheet. Put the sheet name you want to
import where I have "SheetName"
DoCmd.TransferSpreadsheet acImport, 8, "Ssaintv", stCombine, True,
"SheetName"

 

[Guest]

Thank you for all your help. The $ was what was needed...

 

[Guest]

I am trying to import multiple (more than 100) sheets from a workbook into an
Access Table. All sheets have identical structure.
I use the following VBA code:
'*******************************************
' *** declairing path, excel filename and Access Table****
FilePath = "G:\LIB\Trailers\"
MyWorkBook = "Sample.xls"
MyTable = "SampleTable"

MyExcel = (FilePath & MyWorkBook)
' *** After having a reference to MS Excell 11.0 Object Library ****
Workbooks.Open Filename:=(FilePath & MyWorkBook), ReadOnly:=False
Windows(MyWorkBook).Visible = True

For i = 1 To Workbooks(MyWorkBook).Sheets.Count ' I can take sheet name
for every sheet in the workbook
MySheetName = Workbooks(MyWorkBook).Worksheets(i).Name ' Keep the
original sheet name
Workbooks(MyWorkBook).Worksheets(i).Name = "Sheet" & i ' and rename
it to avoid "strange" chars (+,-,/, etc)
MySheet = Workbooks(MyWorkBook).Worksheets(i).Name ' the new sheet
name
MySheetA = Workbooks(MyWorkBook).Worksheets(i).Name & "!A2:K1441" '
the sheet range

DoCmd.TransferSpreadsheet acImport, , MyTable, MyExcel, True, MySheet
'to declaire it here
Next i
'*******************************************

Could you please help me to understand what is wrong about ?
Thanks in advance,
gm