Error when creating additional fields during data upload (Excel to Access)

Question

I am using this code to copy the data from excel sheets to access db. For each table i am creating additional 2 columns to hold the excel file name (Data.xlsx) and run date.

I am getting "ByRef argument type mismatch error" when the routine gets called.

Welcome any suggestions to address this issue.

Sub ImportExcelSheetsToAccessTables()
    Dim excelFilePath As String
    Dim runDate As Date
    Dim sheetName As String
    
    excelFilePath = "D:\Migration\Data.xlsx"
    runDate = Now() ' Get the current date and time

    ' Array of sheet names and corresponding table names
    Dim sheetTableMapping As Variant
    sheetTableMapping = Array( _
        Array("Main Table!", "Agr"), _
        Array("Assets!", "Assets"), _
        Array("Rentals!", "Rentals"), _
        Array("CFlows!", "CFlows"), _
    )

    Dim i As Integer
    For i = LBound(sheetTableMapping) To UBound(sheetTableMapping)
        sheetName = sheetTableMapping(i)(0)
        ' Import each sheet into its corresponding table without deleting existing data
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, sheetTableMapping(i)(1), excelFilePath, True, sheetName
        ' Append DataSource and RunDate to each table
        AppendTrackingData sheetTableMapping(i)(1), sheetName, runDate
    Next i

    MsgBox "Data import complete!", vbInformation
End Sub

Sub AppendTrackingData(tableName As String, dataSource As String, runDate As Date)
    Dim sql As String
    sql = "UPDATE " & tableName & " SET DataSource = '" & dataSource & "', RunDate = #" & Format(runDate, "yyyy-mm-dd hh:nn:ss") & "# WHERE DataSource IS NULL;"
    DoCmd.RunSQL sql
End Sub

AppendTrackingData sheetTableMapping(i)(1), sheetName, runDate - Error in this line. — Prdp
– Prdp, Commented May 24 at 8:02
I guess changing the signature of AppendTrackingData will be sufficent for the error you mention. AppendTrackingData(ByVal tableName As String, dataSource As String, runDate As Date) should do it. And it is always a good idea to run Debug/Comilebefore you run the code. — Storax
– Storax, Commented May 24 at 9:41
This question is similar to: ByRef argument type mismatch in Excel VBA. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — Storax
– Storax, Commented May 24 at 9:46
The error does not happen "when creating additional fields during data upload". This is a compilation error which happens before the Sub is run for the first time. I.e., it is not running yet, when you get the error. — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented May 26 at 16:51

FunThomas · Accepted Answer · 2025-05-27 06:55:01Z

When you use the Array-function, the array elements are always of type Variant. That means that the element that defines the table name is a Variant holding a String. Remember, a Variant can hold anything

Now you want to pass this Variant as arguments to the routine AppendTrackingData which expects a String as first parameter. This parameter is expected to be passed by Reference. At the address of sheetTableMapping(i)(1), a Variant is stored, not a String, and therefore the types are not compatible.

There are four ways to solve this:

Convert the argument explicitly into a String:

    AppendTrackingData CStr(sheetTableMapping(i)(1)), sheetName, runDate

Write the tablename into an intermediate variable of type string first (as you do with the sheet name).

    Dim tableName As String
    tableName = sheetTableMapping(i)(1)
    AppendTrackingData tableName, sheetName, runDate

Change the parameter declaration of tableName to ByVal

     Sub AppendTrackingData(ByVal tableName As String, dataSource As String, runDate As Date)

Change the parameter declaration of tableName to Variant

     Sub AppendTrackingData(tableName As Variant, dataSource As String, runDate As Date)

Collectives™ on Stack Overflow

Error when creating additional fields during data upload (Excel to Access)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related