title: How to filter columns when adding data into MS Access using DoCmd.TransferSpreadsheet?
body: <p>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.</p>
  <p>Here is my code that I have embedded into an import button on an Access form:</p>
  <pre><code>Private Sub btnImportSpreadsheet_Click()
      Dim FSO As New FileSystemObject
      
      If FSO.FileExists(Nz(Me.txtFileName, &quot;&quot;)) Then
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, &quot;Sheet1&quot;, _
              Me.txtFileName, True, &quot;Sheet1!&quot;
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, &quot;Sheet2&quot;, _
              Me.txtFileName, True, &quot;Sheet2!&quot;
      Else
          MsgBox &quot;File not found&quot;
      End If
      
  End Sub
  </code></pre>
  <p>Upon clicking import this creates two tables inside Access, sheet1 and sheet2.</p>
  <p>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:</p>
  <pre><code>    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, &quot;Sheet2&quot;, _
          Me.txtFileName, True, &quot;A1:B1,E1:F1&quot;
  </code></pre>
  <p>But when I run that code it tells me that: &quot;The Microsoft Access engine could not find the object &quot;A1:B1,E1:F1&quot; - I think it doesn't allow multi-ranges like this.</p>
  <p>The other issue is that when I just use &quot;A1:B1&quot; that only gives me back the columns, and none of the data in the rows below it.</p>
  <p>How can I create two tables from Sheet2 with the first being A1:B1,E1:F1 and the second table being C1:D1?</p>
  <p>Thank you</p>
  <p><strong>Edit:</strong> 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:</p>
  <pre><code>If FSO.FileExists(Nz(Me.txtFileName, &quot;&quot;)) Then
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, &quot;Sheet2&quot;, _
          Me.txtFileName, True, &quot;Sheet2!&quot;
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, &quot;Sheet3&quot;, _
          Me.txtFileName, True, &quot;Sheet3!&quot;
  
  Set dbs = CurrentDb()
  dbs.Execute &quot;ALTER TABLE Sheet3 DROP COLUMN a_field_name&quot;
  
  Else
      MsgBox &quot;File not found&quot;
  End If
  </code></pre>
  <p>End Sub</p>