Import Excel sheets in all files in a folder, different structure on each sheet

Razorman

New Member
Joined
Aug 19, 2013
Messages
19
Hi guys

I'm really struggling here, hope you can point me in the right direction. I am OK with Excel VBA code but am now looking at Access for the first time.

I pasted my code below and need to combine all sheets in all excel files into one table. Sheets all have different structures i.e. used range is different for each sheet by rows and by columns.

_________________________
CONTEXT
I tried using PowerQuery but it is taking waaaaaayyyyy too long.

Here is the scenario - I have about 150 excel workbooks.
Each workbook has between 40 and 90 sheets.

I want to
1. import all data from each sheet in each workbook into one single database
2. include a column with the filename
3. include a column with the sheetname

Once imported into access, I can then filter the data as needed. I have a separate Excel file listing every single file name, sheet name and a description of that sheet. This will help me to group the sheets together (into 150 group types).
_________________________________

My CODE to get files from one folder. I can't get the loop to work correctly. It is repeatedly bringing back data from the first file

VBA Code:
Option Compare Database


Sub CombineAllsheets()


'   code works but requires first sheet to have the maximum column used
    
    Dim Starttime As String
    Dim EndTime As String
    Starttime = Time
    
    Dim strPath As String
    Dim strFile As String
    
    strPath = _
        "C:\2021.01\AR analysis\5\"
    strFile = Dir(strPath & "*.xls*")
        
    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
          
'_________________
    Do While strFile <> ""
              
        ' 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
        
        blnHasFieldNames = False    'no headers in Excelfile
        strPathFile = strPath & strFile     'strPath & strFile     Excel filename
        strTable = "Folder5_2"    'new access tablename
        strPassword = vbNullString  'no password
        blnReadOnly = False ' 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, updatelinks:=False)
        
        For lngCount = 1 To objWorkbook.Worksheets.Count
                objWorkbook.Sheets(lngCount).Activate
                'objWorkbook.Sheets(lngCount).Visible = xlsheetvisible  'this does not seem to work
              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, acSpreadsheetTypeExcel12Xml, _
                        strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
            Next lngCount
            
        Set colWorksheets = Nothing     ' Delete the collection
        
    Loop
    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPathFile
        
    
    EndTime = Time
    MsgBox "Done" & vbCrLf & Starttime & vbCrLf & EndTime


End Sub


NOTE: I created a dummy excel file with 1 line and 80 columns which gets imported first. All subsequent workbooks will have <80 columns.
I did this so that the Table is created with enough columns to accommodate all subsequent files that are imported.

Any help appreciated.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
There is nothing in that code at all that would loop over files (I see a few hints of trying to get there). You need to add the code to do that. Also I would begin with just three or four files only (you could hard code the names in that case) - to see if it works. Generally speaking importing data with mismatched structure is not something to spark much optimism.

Note that if power query works I would use it. I'm not sure what you mean by waaaayyy to long. If you started it, went to lunch (or bed) and came back and it was done - I'd consider that a great success and get on to bigger and better things.
 

Razorman

New Member
Joined
Aug 19, 2013
Messages
19
Hi Xenou

Thanks for the reply.

Way too long = when I used Power Query to import a subfolder with 5 files (each with 40-90 sheets), it took 12 minutes for the query to refresh. Then I insert another column which references a 2nd table and that takes another 8 minutes to process. Then I apply a filter to that column to eliminate items that don't match and again - 8 minutes.

There's 100 subfolders in total :(

The only reason I'm importing mismatched data into one place is because there are 158 different possible structures that exist (I actually ran a macro to extract the header row from each file without opening the file - took 6hours). Once I have the consolidated data in place, then I plan to use my reference table to filter and manipulate the database into the 158 different structures.

To demystify this discussion a bit - here's the context - I have 3 years worth of accounts receivable data. 1 file per week. Within each file, each sheet has 1 customer's list of outstanding invoices, sheet names are manually typed in and don't follow a strict convention. Some sheets have 10 columns, other have 12 and it goes up to 19 columns (as the files evolved over time) - 158 possible combinations of headings.

I need to consolidate all of this into one place.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,066
Office Version
  1. 365
Platform
  1. Windows
Have never done this but here's what strikes me.
I don't see the need to open each file and append the names to a collection, then close them all, then have transfer function access them again. Why not just open the sheet and while looping though the sheets by sheet index and retrieving the name, do the transfer right away? That might speed things up a bit. You also need an outer loop that will iterate over every file in a folder. If there are subfolders to that folder, you'd need a recursive function - not easy AFAIC. I would also cut the number of table fields. If the max is 19 why have 80? If you create this table manually and set the field data types properly you might prevent issues down the road when it comes to fields being created as text when they should be numbers and vice versa. An alternative is to transfer to a staging table then append to the main table whose fields have been properly data typed.

Make sure you select "require variable declaration" in vb editor options and add Option Explicit to your existing modules. You're asking for grief without that option.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

You will need a way to loop the files in the folder, regardless. What have you tried? It is relatively easy to to do this with vba FileSystemObject methods. I'm pretty sure with no exaggerations there would be hundreds of sites explaining it by now, as well as many more detailing other methods.
 

Razorman

New Member
Joined
Aug 19, 2013
Messages
19
You will need a way to loop the files in the folder, regardless. What have you tried? It is relatively easy to to do this with vba FileSystemObject methods. I'm pretty sure with no exaggerations there would be hundreds of sites explaining it by now, as well as many more detailing other methods.
I've tried hours of searching Google, Excelforum and MrExcel for "VBA Access to import all sheets " and "VBA Access to import all files in a folder". Then I tried to combine the code. Problem is I'm not a programmer so I wouldn't have thought to search for tutorials on VBA FileSystemObject. Let me give that a go and see if I can find something that points me in the right direction.


Some thoughts on your previous post
Generally speaking importing data with mismatched structure is not something to spark much optimism.
On the contrary, if I can pull this off it will be a major achievement. This is indeed a tedious exercise and I'm the only one who recognizes that it is indeed possible by using VBA. Many others have tried and given up simply at the thought of doing this. I believe that I am more than halfway there already.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,066
Office Version
  1. 365
Platform
  1. Windows
Searching on "ms access vba loop through files in a folder and transferspreadsheet" I got this in about 68 results
If you leave out the transferspreadsheet keyword, the results are in the six figures. So what you do is find one that looks close, copy and try it and if it doesn't work, you post your attempted code here with details of what happens.

EDIT- Dir function is considered to be more efficient that FSO so don't shy away from Dir.
 

Razorman

New Member
Joined
Aug 19, 2013
Messages
19
Searching on "ms access vba loop through files in a folder and transferspreadsheet" I got this in about 68 results
If you leave out the transferspreadsheet keyword, the results are in the six figures. So what you do is find one that looks close, copy and try it and if it doesn't work, you post your attempted code here with details of what happens.

EDIT- Dir function is considered to be more efficient that FSO so don't shy away from Dir.
Thanks, let me have a go with this. Will provide an update on thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top