Excel Import Errors Out from Bad Header Name (code is supplied)

Question

This piece of code has been working great to go out and retrieve a bunch of excel files and append to a table in the Access database.  But, I'm having issues where sometimes the user wiped out the header/column name which makes it fail to download.  We have to manually go into each one that didn't go and find the one that caused the problem and fix it.  Then it will download properly after that.  I'm trying to see if there is a way to catch and fix errors.

Can I bypass header names?  Or is there a way to somehow correct it if the database does not recognize a header name to append it properly into the assigned table?

Here is my code:

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 = "\mfad.mfroot.org\dept\CompleteWorklists"

strNewPath = "\mfad.mfroot.org\dept\ 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"

'Range("A1:" & lastcell)

'"A:O" 'change to this when the revised spreadsheets begin

'strTable, strPathFile, blnHasFieldNames, "A:M"

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

Answer 1

Hi John,

Are you still having concerns with this? Let us know.

Thanks!

Answer 2

This appears to be a PEBKAC error (Problem Exists Between Keyboard And Chair). If the sheet does not have the columns identified, how can Access possibly tell which field should get the data? Suppose the user blanks out three column headers (and maybe even rearranges the columns, or adds or deletes columns)? How can Access determine what the user intends?

Answer 3

Hi,

For better assistance, we suggest that you also post this concern in the TechNet forum.

Regards.