Imporint Excel Spreadsheet with Multiple Sheets

[Nigel Bennett]

Is it possible to automate a procedure to import data from
a spreadsheet with multiple sheets. Each sheet must be
imported individually

Nigel

 

[Ken Snell]

Yes, using VBA code or macros.

Something like this could be used (assumes each sheet is being imported into
the same table, and that your sheets do not have header text in the first
row):

(* not tested *)

Dim lngCount As Long
Dim strSheets() As String
Dim xls As Object, xlw As Object
Set xls = CreateObject("Excel.Application")
Set xlw = xls.Workbooks("C:\MyFolder\Filename.xls").Open
ReDim strSheets(0 To xlw.Worksheets.Count - 1))
For lngCount = 0 To xlw.Worksheets.Count - 1
strSheets(lngCount) = xls.Worksheets(lngCount + 1).Name
Next lngCount
xlw.Close False
Set xlw = Nothing
xls.Quit
Set xls = Nothing
For lngCount = LBound(strSheets) To UBound(strSheets)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TableName", "C:\MyFolder\Filename.xls", , _
"'" & strSheets(lngCount) & "'!"
Next lngCount

 

[Guest]

I am trying to accomlish the same thing. When I try to use the example code I get a RunTime Error 9 - Subscript out of Range on the line 'Set xlw = xls.Workbooks(strTestName).Open'



----- Ken Snell wrote: -----

Yes, using VBA code or macros.

Something like this could be used (assumes each sheet is being imported into
the same table, and that your sheets do not have header text in the first
row):

(* not tested *)

Dim lngCount As Long
Dim strSheets() As String
Dim xls As Object, xlw As Object
Set xls = CreateObject("Excel.Application")
Set xlw = xls.Workbooks("C:\MyFolder\Filename.xls").Open
ReDim strSheets(0 To xlw.Worksheets.Count - 1))
For lngCount = 0 To xlw.Worksheets.Count - 1
strSheets(lngCount) = xls.Worksheets(lngCount + 1).Name
Next lngCount
xlw.Close False
Set xlw = Nothing
xls.Quit
Set xls = Nothing
For lngCount = LBound(strSheets) To UBound(strSheets)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TableName", "C:\MyFolder\Filename.xls", , _
"'" & strSheets(lngCount) & "'!"
Next lngCount

 

[Ken Snell]

What is the value of strTestName variable? It needs to be the entire path to
an existing file.


--
Ken Snell
<MS ACCESS MVP>

I am trying to accomlish the same thing. When I try to use the example

code I get a RunTime Error 9 - Subscript out of Range on the line 'Set xlw =
xls.Workbooks(strTestName).Open'

 

[Ken Snell]

If you're sure that the file exists in the place where your path sends it,
then it's possible that the path length is too long. Test this by moving the
file to a location with a shorter path and try it again.

--
Ken Snell
<MS ACCESS MVP>

It contains the entire path I even tried hard coding the path and

received the same error message.

 

[Ken Snell]

In your code, by not specifying the Sheet Name in the import step, how do
you loop through and import one sheet at a time? In my testing, omitting the
spreadsheet name means that ACCESS will import *only* the first sheet in the
file.

--
Ken Snell
<MS ACCESS MVP>

Got it. I changed the code as follows below. The only down side is I get

a lot of extra columns. I do not have the sheetnames anywhere in the table
but I can always just add a column to the spreadsheet to do this. I took
the last line out of the DoCmd because it was giving me a runtime error,
Error 3125 "Q'$' is not a valid name. "Q" is what I named the first sheet.
What exactly does having the sheet name get me when I import into the table?
Thanks for your help Ken.

Dim lngCount As Long
Dim strSheets() As String
Dim xls As Excel.Application, xlw As Excel.Workbook
Set xls = New Excel.Application
Set xlw = xls.Workbooks.Open("C:\test.xls")
ReDim strSheets(0 To xlw.Worksheets.Count - 1)
For lngCount = 0 To xlw.Worksheets.Count - 1
strSheets(lngCount) = xls.Worksheets(lngCount + 1).Name
Next lngCount
xlw.Close False
Set xlw = Nothing
xls.Quit
Set xls = Nothing
For lngCount = LBound(strSheets) To UBound(strSheets)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TableName", "C:\test.xls"
Next lngCount

----- Ron wrote: -----

Still no good. I have the file at "C:\test.xls" and get the same

error. I replaced my variable with the string "C:\text.xls" and received
the same error message.

 

[Ken Snell]

Those quotes are needed only if there are spaces in the sheet name. I
usually put in a code step that tests for this before putting the quotes
around the sheet name, but I apparently left that step out of the post when
I pasted it into my message. If you wanted to use this extra test, you can
insert this line of code right after the line "strSheets(lngCount) =
xls.Worksheets(lngCount + 1).Name":

If InStr(strSheets(lngCount), " ") > 0 Then strSheets(lngCount) = "'" &
strSheets(lngCount) & "'"

Glad you got it to work!
--
Ken Snell
<MS ACCESS MVP>

You are right. I only got the first sheet. Even worse I got it twice

since there are 2 sheets in my array.

I messed around with the code a bit and got it working. I got rid of the

single quotes where the sheet is named. Below is what I have now. The
cdlg.getname variable holds the path retrieved by the Common Dialog control.
Thanks again for all your help.