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

[Razorman]

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

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.

 

[xenou]

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]

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]

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]

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]

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]

Searching on "ms access vba loop through files in a folder and transferspreadsheet" I got this in about 68 results

Import Excel files into Access whilst Looping

I am comfortable with using VBA to import excel sheets into access, and to loop through a given folder to bring back everything in there. However, I want to loop through a folder and only import a

stackoverflow.com

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]

Searching on "ms access vba loop through files in a folder and transferspreadsheet" I got this in about 68 results

Import Excel files into Access whilst Looping

I am comfortable with using VBA to import excel sheets into access, and to loop through a given folder to bring back everything in there. However, I want to loop through a folder and only import a

stackoverflow.com

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.