Importing from excel column headers in row 5

[Drand]

Hi all

My client has modified their excel spreadsheets so the headers are now in row 5 instead of row 1. How can I modify the following function to reflect the changes?

Public Function ImportHierarchyFunctionFiles()

    Dim MyFile As String
Dim MyPath As String
Dim db As Database

Set db = CurrentDb ' Initialize db object
        db.Execute "DELETE FROM tblFunction"
        db.Execute "DELETE FROM tblLocation"

MyPath = "C:\KPMG\Hierarchy data\"
MyFile = Dir(MyPath & "*.xlsx")
Do While MyFile > vbNullString
    
    
    DoCmd.TransferSpreadsheet acImport, 10, "tblFunction", MyPath & MyFile, True, "Function Hierarchy!"
    DoCmd.TransferSpreadsheet acImport, 10, "tblLocation", MyPath & MyFile, True, "Location Hierarchy!"
    MyFile = Dir
   ' MsgBox "Raw Data Imported for " & MyFile & " From " & MyPath
    
    
Loop


End Function

Many thanks

 

[June7]

Use Range argument to specify range to import https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet. Example:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TableName", "filepath\filename.xlsx", True, "Sheet1!A5:C20"

 

[Drand]

Thanks for your response. The function loops through 134 spreadsheets and each has a different range of data, albeit each starting in cell A5.

How would I select the individual ranges in each spreadsheet?

 

[June7]

Options:

1. design workbook with name assigned to range for import - it can be the same name in each

2. VBA Excel automation to determine last populated row and column on sheet or delete first 4 rows

For more info, review http://accessmvp.com/KDSnell/EXCEL_Import.htm

 

[ebs17]

a different range of data

If the number of rows is different, you can import using an append query, making the range large enough and filtering out empty rows.

    sSQL = "INSERT INTO AccessTable( Key1, Key2, Value1, Value2, Timestampfield )" & _
           " SELECT DISTINCT E.Key1, E.Key2, E.Value1, E.Value2, Now()" & _
           " FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & ExcelFile & "].[" & SheetName & "$A5:D1000] AS E" & _
           " LEFT JOIN AccessTable AS A ON E.Key1 = A.Key1 AND E.Key2 = A.Key2 WHERE A.Key1 Is Null AND E.Key1 > 0"

Four columns were used here, with two columns in combination being the key for duplicates (which are to be excluded).