Transferspreadsheet Working Intermittently

[mmogul]

I am using the Transferspreadsheet function to import excel worksheets into an access table. I am loading all the worksheets in a listbox and then read through the list box to import each worksheet into a temporary table, do some processing, and then move to the next worksheet and repeat. I delete the temporary table before I import the next worksheet.

The problem I am having is that sometimes, the import (transferspreadsheet) does not start at the designated row as defined in the Import function (see code). This problem is not always repeatable. I can import the same spreadsheet a second time and all the worksheets will import correctly -- or I might find a different spreadsheet imports starting at an incorrect row.

Has anyone ever run into this issue? (I am assuming that this is not an Access / Excel bug but I am doing something wrong -- I just can't find it). I am running Access 2010.

Here is the procedure that calls the transferspreadsheet:

Public Sub ImportXLSheet(xlApp As Excel.Application, sWorkbook As String, sSheetName As String, sTableName As String, _]

Public Sub ImportXLSheet(xlApp As Excel.Application, sWorkbook As String, sSheetName As String, sTableName As String, _
bHasFieldNames As Boolean, Optional sRange As String)



Call TableDelete(sTableName)

DoCmd.TransferSpreadsheet _
acImport, _
8, _
sTableName, _
sWorkbook, _
bHasFieldNames, _
sSheetName

End Sub


For example, sWorkbook will be the workbook name along with the directory. sSheetName will include the range to be imported. ex: LFI!A11:AB49 (where LFI is the spreadsheet name and A11:AB49 is the range to import. I might find that this instead of starting on Row 11, the import might start at Row 30.

[/code]
This is called by (there is other stuff that goes on here but I simplified it for this discussion):

Do While i < Me.lstWorksheets.ListCount                
     Call ImportXLSheet(xlApp, sImportWorkbook, sSheetName, sAccessTable, False, sRange)
I = I +1
Loop

 

[PHV]

Have you tried this ?
"[LFI$]!A11:AB49"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmogul]

Thanks. I'll give that a try. But what I can't figure is the intermittent nature of this problem.