How to filter columns when adding data into MS Access using DoCmd.TransferSpreadsheet?-CodePudding

I have an excel file with two sheets. The first one I want to add the entire sheet to an MS Access table - and so far this code is accomplishing that. But with the second sheet (sheet2) I want to be able to filter the columns and create two tables from that sheet. So I'd like to make the first table range from columns A1:B1,E1:F1 and include any data that is in the rows below it. Then for the other table I'd like the columns to be C1:D1.

Here is my code that I have embedded into an import button on an Access form:

Private Sub btnImportSpreadsheet_Click()
    Dim FSO As New FileSystemObject
    
    If FSO.FileExists(Nz(Me.txtFileName, "")) Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Sheet1", _
            Me.txtFileName, True, "Sheet1!"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Sheet2", _
            Me.txtFileName, True, "Sheet2!"
    Else
        MsgBox "File not found"
    End If
    
End Sub

Upon clicking import this creates two tables inside Access, sheet1 and sheet2.

What I've tried to do is filter it by adding in A1:B1,E1:F1 into the Range area (where Sheet2! is) like so:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Sheet2", _
        Me.txtFileName, True, "A1:B1,E1:F1"

But when I run that code it tells me that: "The Microsoft Access engine could not find the object "A1:B1,E1:F1" - I think it doesn't allow multi-ranges like this.

The other issue is that when I just use "A1:B1" that only gives me back the columns, and none of the data in the rows below it.

How can I create two tables from Sheet2 with the first being A1:B1,E1:F1 and the second table being C1:D1?

Thank you

Edit: with the help of June7 the solution to this is to add a currentDb() and then from that execute an SQL query that removes a specific field name:

If FSO.FileExists(Nz(Me.txtFileName, "")) Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Sheet2", _
        Me.txtFileName, True, "Sheet2!"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Sheet3", _
        Me.txtFileName, True, "Sheet3!"

Set dbs = CurrentDb()
dbs.Execute "ALTER TABLE Sheet3 DROP COLUMN a_field_name"

Else
    MsgBox "File not found"
End If

End Sub

CodePudding user response：

Import the full worksheet then modify table. Options:

If resulting field names are known, import full range to Table1 then use SQL action statements:

CurrentDb.Execute "SELECT Field3, Field4 INTO Table2 FROM Table1"`  
CurrentDb.Execute "ALTER TABLE Table1 DROP COLUMN Field3, Field4"

If field names are unknown, import full range to Table1 and range C:D to Table2. Then Use DAO TableDefs to remove unneeded fields from Table1.

For x = 2 to 3
CurrentDb.TableDefs("Table1").Fields.Delete CurrentDb.TableDefs("Table1").Fields(x).Name
Next