DoCmd.TransferSpreadsheet Problem

[twcaddell]

My organization contracts out to a 3rd party for a service. They send a list of invoices during the month, listing their job number, our Purchase Order Number, a description, the cost and date completed.

I have a file that contains 5 excel worksheets. My routine determines the names of the sheets, loops through each worksheet, loading it into a temporary table for processing. The first four sheets worked as designed, but the fifth worksheet imported all the rows of the sheet, but after row 15, the job number result was a null for the following records (@70). I went into excel and after some looking, found out that the cell format changed from number to text so thought that may be the reason it errored out. I then added to my code to change the format of the columns dynaically to what I wanted. I still get the same results. Here is slice of code that does the transfer. I checked the format of the excel cells and they did change from text to number.

Anyone have any ideas

    'xxxx Get the valid worksheet names into a table
    strSQL = "SELECT WSName from ExcelTmpNameTable ORDER BY WSName;"
    Debug.Print strSQL
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveFirst
 
    TheSheet = Trim(rst!WSName)
    xlBk.Sheets(TheSheet).Visible = True
    Set xlSht = xlBk.Sheets(TheSheet)
    WkShtName = xlSht.Name
 
    LastRow = xlSht.Cells(Rows.count, "B").End(xlUp).Row
    xlSht.Cells(3, 5).value = "PONUM"
    xlSht.Cells(3, 2).value = "WMI Inv Num"
    xlSht.Cells(3, 3).value = "Job Num"
    xlSht.Cells(3, 1).value = "Index Num"
    xlSht.Range("B4:B" & LastRow).NumberFormat = "0"
    xlSht.Range("C4:C" & LastRow).NumberFormat = "0"
    xlSht.Range("G4:G" & LastRow).NumberFormat = "mm/dd/yy;@"
    xlBk.Close savechanges:=True
    xlApp.Quit
 
    TblName = "tmpTableName"
 
    If DoesTblExist(TblName) Then
        strSQL = "Delete * from " & TblName & ";"
        db.Execute strSQL, dbFailOnError
    End If
 
    DoCmd.TransferSpreadsheet transfertype:=acImport, _
                tableName:=TblName, _
                FileName:=thePath & TheFile, Hasfieldnames:=True, _
                Range:=WkShtName & "!A3:G" & LastRow, SpreadsheetType:=8

 

[twcaddell]

In reading further on in the forum, someone suggested that convert the column to text. I tried it and it worked. Not sure why the other way wouldn't work though, but it is solved for now.