Import Excel with VBA and one different column name

[killerflappy]

Hi,

I have an automated Excel-file import where the user can choose the excel-file with routes to drive.

DoCmd.TransferSpreadsheet acImport, 8, "tblRoutes", selectFileWithoutDoubleCheck(), True, "!A1:L1000"

The headers all aready there in tblRoutes.

Now every day column K1 of the Excel file changes to the name of the day.
So then Access refuses to import.

I tried to set True to False. Still don't work.
Also I change the the range from A1:L1000 ti A2:L1000. Also don't work.

The Excel files to import have different names, so changing the Excel-file from access Is not possible.

The selectFileWithoutDoubleCheck() function is this. Maybe it's possible to use this to change the K1?

Function selectFileWithoutDoubleCheck()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = "Z:"
 
    With fd
        If .Show Then
            selectFileWithoutDoubleCheck = .SelectedItems(1)
        Else
            'stop execution if nothing selected
            End
        End If
    End With
 
    Set fd = Nothing
End Function

Another possible solution would be to avoid the column K from Excel.
I don't see how to do this

 

[Gasman]

Move K and L and only import what will then be A:K, then some process to calculate what should be in column L in the table.?

 

[killerflappy]

Move K and L and only import what will then be A:K, then some process to calculate what should be in column L in the table.?

Thought about that. But it's not the solution. The L column is has a long text in it. And the K only a time. So when this full text is shown it's nog clear anymore at whitch row the time belongs.

 

[Gasman]

Thought about that. But it's not the solution. The L column is has a long text in it. And the K only a time. So when this full text is shown it's nog clear anymore at whitch row the time belongs.

Eh!

If column K has times in it, surely that is in the same row, whether the time is to the left or the right of the long text.?

Can you upload the workbook and table layout? I am having difficulty envisaging your worksheet layout.

You could link the worksheet and use a query to update the table as well.