Importing Multiple Excel Worksheets into An Access Table

[Problem Importing >1 Excel Worksheet 1 Excel Wor]

I tried using the script from a January 2008 Scripting Guy posting. It is
supposed to import multiple worksheets from an Excel workbook into an Access
table. My test Excel workbook has 2 worksheets. The first worksheet gets
imported but the second worksheet does not.

Any advice?


Steve


Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strFileName = "C:\Scripts\ImportData.xls"

Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set colWorksheets = objWorkbook.Worksheets

For Each objWorksheet in colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False,
False)
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Employees", strFileName, True, strWorksheetName
Next

 

[Ken Snell \(MVP\)]

What are the names of the worksheets? Do you have any spaces and/or special
characters in the name of the second worksheet? Do you have a range in the
workbook with the same name as the second worksheet?

This line of code
For Each objWorksheet in colWorksheets

can be changed to this
For Each objWorksheet in objWorkbook.Worksheets


No need for an intervening object such as colWorksheets.

 

[Problem Importing >1 Excel Worksheet]

Ken,

Thank you for the prompt reply. I made the changes, but unfortunately it
still does not work. The worksheet names are Sheet1 and Sheet2. I made it
very basic to try to get the code to work. There are no named ranges in the
workbook.

The program stops after successfully importing Sheet1. It never gets to
Sheet2. Does Excel have to be closed somehow after each transfer and then
reopened?

 

[Ken Snell \(MVP\)]

Am traveling for work, which is delaying my reply to your post. I'll post as
soon as I can.
--

Ken Snell
<MS ACCESS MVP>


"Problem Importing >1 Excel Worksheet"

 

[Ken Snell \(MVP\)]

I've never used TransferSpreadsheet with a file that has been opened by
ACCESS, so I don't have experience about how it behaves. From what I've rad
on newsgroup posts, using TransferSpreadsheet with an open file may have
unexpected results because of file-locking problems. I don't have time right
now to try to set up a test case, either.

There is no need to put the two Const statements in the code; those
intrinsic constants are already defined within ACCESS.

However, at times, when you set an object and then reset it without setting
it to Nothing inbetween, you can get strange results. Let's try changing the
code to this:


\Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strFileName = "C:\Scripts\ImportData.xls"

Set objWorkbook = objExcel.Workbooks.Open(strFileName)

For Each objWorksheet in objWorkbook.Worksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False,
False)
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Employees", strFileName, True, strWorksheetName
Set objRange = Nothing
Next



Or perhaps this approach:

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strFileName = "C:\Scripts\ImportData.xls"

Set objWorkbook = objExcel.Workbooks.Open(strFileName)

For Each objWorksheet in objWorkbook.Worksheets
strWorksheetName = objWorksheet.Name & "!" &
objWorksheet.UsedRange.Address(False,
False)
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Employees", strFileName, True, strWorksheetName
Next



Have you stepped through the code to see if it's looping through all the
worksheets? Or is it "jumping" out of the loop after the first worksheet? Do
you have error handling in the code?

--

Ken Snell
<MS ACCESS MVP>




"Problem Importing >1 Excel Worksheet"