Import Excel Worksheets and Skip Certain Columns

[vcs1161]

I have the following Event Procedure working well but now I need to skip a column and can't get it to work. I need to skip column E. And I don't want to use the field names and begin on Row 2.

Private Sub cmd_Import_Wklst_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim strNewPath As String
Dim strNewPathFile As String
Dim strRange As String
'Dim blnHasFieldNames As Boolean
' Change this next line to False if the first row in EXCEL worksheet does not have column/field names
blnHasFieldNames = True
' shared folder path that contains the EXCEL files
strPath = "\\maindirectory\subdirectory\directory\Folder\FileName\"
strNewPath = "\\maindirectory\subdirectory\directory\Folder
 Quality\CompleteWorklists\Downloaded_Into_Access\"
' the database table where the worklists are to be imported
strTable = "import_ready_for_download"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      strNewPathFile = strNewPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
      strTable, strPathFile, blnHasFieldNames, "A:O"  'want to change this to A:D then F:O
      
strFile = Dir()
' Copy file to new location:
  FileCopy strPathFile, strNewPathFile
  
  ' Delete the EXCEL file(s) after it's been imported Or you comment this Kill command out if you don't want to
       Kill strPathFile
Loop
End Sub

Any help is greatly appreciated.

[June7]

So unless a named range in Excel can be discontinuous and import that range, don't think can use TransferSpreadsheet.

http://www.accessmvp.com/KDSnell/EXC...m#WriteFileRst

[vcs1161]

Okay. Thank you. It seems that the error comes when the column name is missing but is in the right field order and data type. I'm hoping maybe I can come up with way to handle that error when they come up. Can I do an IF statement when that happens?

If it's column C without the column name then that field name causing it to fail then this column field name= 'this field'? And so on, with the other columns to check?

[June7]

With TransferSpreadsheet? I don't think so.

But I don't really understand what you mean by "column name is missing but is in the right field order and data type".

[vcs1161]

blnHasFieldNames = True. The end user clears out the field name (column name in the spreadsheet) which now had nothing to read. So that is not identified in the database table it is importing to. The field names in the table are the same as the worksheet column names.

The rest of the data is there starting in row 2 for that column/field name.

[June7]

Again, I don't think TransferSpreadsheet can deal with this circumstance.

User deletes column header in Excel but leaves data? Educate users?