Excel file won't import unless open

[socasteel21 via AccessMonster.com]

I'm importing 3 sheets from an Excel file. All of the sheets pull data from
one other sheet inside the workbook.

The import works perfectly if the Excel file is open. Here is the basic
outline of what I am doing:

1. I have a split database
2. I press a command button which opens the standard file dialog box.
3. I select the file to be imported.
4. 3 TransferSpreadsheet actions are ran (1 for each sheet) to 3 seperate
temporary tables.
5. 3 Append queries are ran to append data from each of the 3 temp tables to
my main tables.
6. 3 Delete queries are ran to delete data from the temp tables.

Here is my exact code:

Private Sub cmdImportClaim_Click()

Me!txtImportFile = LaunchCD(Me)

Dim FileName1 As String
FileName1 = Me.txtImportFile

If FileName1 = "" Then
GoTo Exit_cmdImportClaim_Click
End If


DoCmd.TransferSpreadsheet acImport, , "Import: tblClaim", FileName1, -1,
"tblClaim"

Dim stDocName As String

stDocName = "Append: tblClaim"
DoCmd.OpenQuery stDocName, acNormal, acEdit



DoCmd.TransferSpreadsheet acImport, , "Import: tblPartPerClaim",
FileName1, -1, "tblPartPerClaim!A1:C31"

Dim stDocNameB As String

stDocNameB = "Append: tblPartPerClaim"
DoCmd.OpenQuery stDocNameB, acNormal, acEdit




DoCmd.TransferSpreadsheet acImport, , "Import: tblJobCodesPerClaim",
FileName1, -1, "tblJobCodesPerClaim!A1:C31"

Dim stDocNameC As String

stDocNameC = "Append: tblJobCodesPerClaim"
DoCmd.OpenQuery stDocNameC, acNormal, acEdit

Dim stDocNameH As String
stDocNameH = "DELETE: Import: tblClaim"
DoCmd.OpenQuery stDocNameH, acNormal, acEdit

Dim stDocNameJ As String
stDocNameJ = "DELETE: Import: tblPartPerClaim"
DoCmd.OpenQuery stDocNameJ, acNormal, acEdit

Dim stDocNameK As String
stDocNameK = "DELETE: Import: tblJobCodesPerClaim"
DoCmd.OpenQuery stDocNameK, acNormal, acEdit



DoCmd.Close
DoCmd.OpenForm "frmClaim"



Exit_cmdImportClaim_Click:
Exit Sub

End Sub

Any help you could offer would be appreciated. Thanks for your help.

Shannan

 

[John Nurick]

Hi Shannan,

What error do you get and on which line of code?

Is it just one of the TranferSpreadsheet operations that fails (if so,
which?) or all of them?

Is "tblClaim" the name of a worksheet or a range in the workbook?