External Table is not in the expected format

[Legacy 15162]

I am getting this error when trying to perform a transferspreadsheet command. It ends up corrupting the file and i have to start anew.

This is the output function
Function SFTOutput()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpActSales", "H:\WkyLbr Report\SFT\@SFT.xls", , "tmpActSales"


MsgBox "Export Complete"

End Function

This creates the tmpActSales query

Function GetActSales()

Dim rs As DAO.Recordset
Dim lngMaxCol As Long
Dim stgetsales As String
Dim tempDate As Date
Dim lngMinPd As Long
Dim lngMinWk As Long
Dim lngMinYr As Long

On Error GoTo actErr
lngMinPd = Me.SFTpd
lngMinWk = Me.sftwk
lngMinYr = Me.SFTYr
SubtractWeeks lngMinYr, lngMinPd, lngMinWk, 4

stgetsales = "TRANSFORM Sum(dbo_fmsDaily.TTL_NET) AS SumOfTTL_NET" & _
" SELECT dbo_fmsDaily.storeno, Divisions.DivDescription, Areas.AreaDescription" & _
" FROM Divisions INNER JOIN (dbo_fmsDaily INNER JOIN (Areas INNER JOIN Stores ON Areas.AreaID = Stores.AreaID) ON dbo_fmsDaily.storeno = Stores.Storeno) ON Divisions.DivisionID = Areas.DivisionID" & _
" WHERE dbo_fmsDaily.year & format([period], ""00"") & Format([week],""00"") Between " & lngMinYr - 1 & Format(lngMinPd, "00") & Format(lngMinWk, "00") & " AND " & lngMinYr - 1 & Format(Me.SFTpd, "00") & Format(Me.sftwk, "00") & " OR dbo_fmsDaily.year & format([period], ""00"") & Format([week],""00"") Between " & Me.SFTYr & Format(lngMinPd, "00") & Format(lngMinWk, "00") & " AND " & Me.SFTYr & Format(Me.SFTpd, "00") & Format(Me.sftwk, "00") & _
" GROUP BY Divisions.DivDescription, Areas.AreaDescription, dbo_fmsDaily.storeno" & _
" ORDER BY dbo_fmsDaily.storeno" & _
" PIVOT ""Y"" & dbo_fmsDaily.year & ""P"" & Format([period],""00"") & ""W"" & Format([week],""00"")"

CurrentDb.CreateQueryDef "tmpActSales", stgetsales

actErr:
Select Case Err.Number
Case 3012
DoCmd.DeleteObject acQuery, "tmpActSales"
Resume
Case Else
End Select
End Function

Function SubtractWeeks(lngYr As Long, lngPd As Long, lngWk As Long, lngVal As Long)

For i = 1 To lngVal
If lngWk = 1 Then
If lngPd = 1 Then
lngYr = year(DateAdd("y", -1, "1/1/" & lngYr))
lngPd = 13
lngWk = 4
Else
lngPd = lngPd - 1
lngWk = 4
End If
Else
lngWk = lngWk - 1
End If
Next i

End Function

I am running access & excel 2000

 

[Joe4]

If you are getting a "External Table is not in the expected format" error, then the Excel file and Access file you are transferring between may not be in the same format. You may be trying to import a text field into a numeric field, or something like that.

Make sure the format of the columns in Excel matches the format of the fields in Access, and make sure you have valid entries for all records.