Importing Multiple Worksheets from Excel to Access

[monrosal]

Does anyone know how I can specify which worksheet to import when I use the TransferSpreadsheet? In the code, there is no argument to specify a workbook. Is there another method I can use? Thanks

Ramon

 

[rollerjk]

Try this:
docmd.transferspreadsheet acimport, 8, _ strtable, "c:\samples\" & strfile, False

8 is for the version of the worksheet. strtable is the name of the table your importing to. In this case it's a variable i created normally it would have "" around it. the "c:\sample\" & strfile is the path and name of the file. Once again strfile is a variable in the example, and false refers to if the worksheet has headers. There is another value you can set at the end to only import certain parts of the file. Enter a range such as "A1:g15" if you need that.

 

[tlbroadbent]

TransferSpreadsheet has a "range" argument. The range can refer to a range of cells or an entire worksheet. Suppose you wanted to import the worksheet "Quarter1" in the workbook "C:\sales\Sales2001.xls." The TransferSpreadsheet nethod of DoCmd would look like this.

docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Sales_Qtr1", "C:\sales\Sales2001.xls" , true, "Quarter1!"

Note the ! after the worksheet name. It must be included. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Ronze55]

1)

I have a similar problem but would like to iterate through multiple worksheets in one excel file.

does anyone know how to do this?

2)

Also I would like to create a base folder/directory: "C:\test_folder\"
and iterate through subfolders to import excel files within them. excel has code to do this but the program always crashes on me.

here is the excel code, i'm looking for the access vba equivalent:

strDir1 = "C:\home\top_folder\"

MyFile = Dir(strDir1, vbDirectory)
i = 0
Do While MyFile <> ""
If MyFile Like "*.xls" Then

Else
MsgBox strDir1 & MyFile
MyFile = Dir()
i = i + 1
End If
Loop

 

[GrimmerII]

Thanks you all I'm almost there,
Now what if I want to import the sheet into a report.. (The sheet is a chart)

Can someone please help me and tell me how I transfer the sheet to an object?