Import From Excel

[jmenche]

Howdy,

I slapped together a couple of routines from the web and came up with this sub to import multiple worksheets from. It works, but does not work as fast as when I just do the File/BetExternalData/Import thing and import each sheet individually.

Can anyone see why my routine is so slow?

Thanks :beerchug:

ub MultiWStoAccess()
Dim objXL As Object
Dim objXLWB As Object
Dim objXLWS As Object
Dim objXLRNG As Object
Dim strRange As String
Dim strPath As String
Dim strFile As String
Dim MyBook As String
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MyBook = vrtSelectedItem
Next
Else: GoTo ExitSub
End If

End With

Set objXL = CreateObject("Excel.Application")
Set objXLWB = objXL.Workbooks.Open(MyBook)

For Each objXLWS In objXLWB.Worksheets
On Error GoTo ExitSub
strRange = objXLWS.Name & "!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyData", MyBook, True, strRange
Next

ExitSub:
objXLWB.Close
objXL.Quit
Set objXLWB = Nothing
Set objXL = Nothing
Set fd = Nothing

End Sub

[XLGibbs]

I am not sure you need to create a nwe instance of Excel to handle the new files being open, this creates and extra step and may slow it down. Simply opening the files in sequence and/or closing them as you go should be fine.

[Norie]

xld

I think the code is being run from Excel, and the reason an instance of Excel is needed is to open the workbook.

The reason the workbook is being opened is to get all the sheet names to use in the TransferSpreadsheet.

I've a feeling there is another way to get the sheetnames without opening the workbook, perhaps using ADO, but that's not my strong point.

I think if that was possible it might speed things up.

Mind you only 1 instance of Excel would be required if this was being used with multiple files, which it currently isn't.

[XLGibbs]

xld


Mind you only 1 instance of Excel would be required if this was being used with multiple files, which it currently isn't.

That was my point. Thanks for re-stating it.

It is being run of Access as I read it, to import multiple worksheets from excel.

[Norie]

xlgibbs, not xld (sorry for the wrong name):oops:

There isn't actually a loop creating multiple instances of Excel.

When I first looked at the code I thought that might be the case, and possibly the causing the problem, but it looks like this makes the dialog only return 1 filename/workbook.

.AllowMultiSelect = False
And there isn't actually anything in the code that does create multiple instances.