First post on the forum! How to modify this code to work for multiple sheets at one time?

[jackedrabbit]

First post here! Woo hoo!

I found this code here: http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile as String, strTable as String
Dim strPassword As String

' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Filename.xls"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "passwordtext"

blnReadOnly = True ' open EXCEL file in read-only mode

' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

' Import the data from each worksheet into the table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
Next lngCount

' Delete the collection
Set colWorksheets = Nothing

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile



I would like to modify it to run multiple sheets, not just one. How do I do this?

 

[xenou]

Hi welcome. You're in luck:

' Import the data from each worksheet into the table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
Next lngCount

It's already written to include more than one sheet from a workbook.

 

[jackedrabbit]

Hi welcome. You're in luck:



It's already written to include more than one sheet from a workbook.

Sorry, I meant for multiple workbooks.

Thanks for the quick response. I hope to learn a lot here to help my career as a financial analyst.

 

[xenou]

the i to -1 doesn't change. It just means all the sheets in the workbook (last to first, specifically) All your tabs of data are being imported into the same table?

 

[jackedrabbit]

There must be something I can do to this chunk of code here:

' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Filename.xls"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"


I've seen code that downloads excel files stored on the internet/intranet of a company server.

You need to specify each file you want it to look up, like "dim sourcefile (1)...(2)...(3)...

and then specify the destination, such as "dim destfile (1)...(2)...(3)...

and I saw something with "NDX" in it...didn't understand what that was all about but I think a similar principle can be applied here.

How do I go about modifying the existing code to accomodate my 49 files?

 

[jackedrabbit]

the i to -1 doesn't change. It just means all the sheets in the workbook (last to first, specifically) All your tabs of data are being imported into the same table?

I have 49 files, each with 5 tabs.

Basically I have 49 sales people...each of the 5 tabs is for a different customer end market.

I want 49 tables in Access...each with the 5 tabs consolidated into 1 table.

OR BETTER YET, 1 table in access...comprised of all 49 files with 5 tabs each. The whole thing in 1 table.

I'd like to show my boss both methods, and see how he would like to pursue it.

So, going with the 49 tables approach each sales person would have his or her own table...comprised of the 5 excel tabs. I'd like to try this first.

It is for a sales pipeline file. We don't have a CRM and have patchworked it in Excel but it's terrible for reporting purposes.

If I can get it all into access I can run queries and other fancy reports to make the data more useful!

 

[xenou]

How are the files named? Are the file names (without the .xls extensions) valid table names?

Note: I've no idea what NDX is, btw.

 

[jackedrabbit]

How are the files named? Are the file names (without the .xls extensions) valid table names?

Note: I've no idea what NDX is, btw.

Each file is named with the Region and Sales Person's name. For example:

"HL - Arave.xlsx"

"SE - Moen.xlsx"

We have 6 regions and a number of sales persons for each region. Total number of sales people = 49.

Each file has the same 5 tabs named the same way. The column headers are the same for every file and every tab.

I have not created any tables yet in my Access db. I think in order for this to work, I'd need to create 49 tables first, to correspond to each of the 49 excel files I want to import. That's easy though.

Does this give you more information as to how to help? I appreciate it a lot. Nobody else (in real life) has been able to help.



I think what may need to be done is just code all 49 files into the module. Since the file names are all different, they would have to be spelled out, right?

 

[xenou]

It's a bit of a rotter having to deal with 49 tables too. I don't have time to create a whole script for you, though in principle you could strip the data out in one big go at it. Short term, I'd probably take the following route:

• Import each file one at a time
• Import it into the same table (a staging table)
• From the staging table append the records to a real table, including a sales rep identifier or region or what have you - now you'll have all the data in one table, which will help further down the road.
• clear the staging table and do the next file.

With a few queries saved (for the appending and clearing of data), you could do this pretty fast. Though not any faster than just importing the data from Excel via the import menu so I guess the VBA is out the window in this scenario.