External table not in expected format???????

[educate889]

DoCmd.TransferSpreadsheet acExport, 8, "XCM101_ICTOTALS", "H:\XCM_RawData.xls", False

Using this code to create a worksheet in Excel and dump the data for the XCM101_ICTOTALS qry.

However, I get the error External table not is expected format. What does this mean????

Any help or suggestions would be appreciated.

Thank you inadvance!

 

[oharab]

I hate having to import XL into Access. It almost never goes right. Access tries to be too helpful & will not give you the control you need.
I usually convert it into a text file then import it, then delete the text file:

Function ImportExcel(strFileName As String, strTableName As String, strSpecName As String) As Boolean
Dim strTextFileName As String 'temporary text file name
Dim XL As Object
Dim xlWB As Object

Const CSV As Integer = 6 'CSV file

strTextFileName = Replace(strFileName, ".", "&quot & ".txt"
'get rid of . and add .txt onto end of file name. Only works if you don't have . in a directory name
Set XL = CreateObject("Excel.Application&quot
Set xlWB = XL.Workbooks.Open(strFileName)
'open file in XL
xlWB.SaveAs strTextFileName, CSV
'save it as a csv file
xlWB.Close False

XL.Quit
Set xlWB = Nothing
Set XL = Nothing
'close XL

DoCmd.TransferText acImportDelim, strSpecName, strTableName, strTextFileName, True
'import the text file
Kill strTextFileName
'delete the temporary text file.
End Function


You need to set up your import specification first and save it.

hth

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[PaulBricker]

Ben, we are working two threads here and I've been helping in the other thread. We are exporting an Access Query to Excel, not the other way around. The Tables aren't linked and the query brings the info from two other queries together. Any other thoughts. My only thought is that the Query doesn't fit in the TransferSpreadsheet format. In the Help file it says for the TableName (the object you are exporting)
"A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet."

I'm kind of at a loss at this point.

Paul

 

[oharab]

Oops!
I guess I ought to read things a bit more carefully!
Are there any strange field types in the query, Memo or Replication ID? They shouldn't cause a problem, but you never know!
Will it work exporting it to a text file? Excel can import a CSV or tab delimited file no problem.
Failing that, I would be tempted to create a short routine that creates the file. This is what I regularly use:

Function ExportExcel(strQueryName As String, Optional blnHasFieldNames As Boolean = False, Optional strFileName As String = "&quot
Dim objXL As Excel.Application, xlWB As Excel.Workbook
Dim db As DAO.Database, rs As DAO.Recordset, fld As DAO.Field

Set db = CurrentDb
Set rs = db.OpenRecordset(strQueryName, dbOpenSnapshot)

Set objXL = CreateObject("Excel.Application&quot
Set xlWB = objXL.Workbooks.Add

If blnHasFieldNames Then
For Each fld In rs.Fields
objXL.Range("A1&quot.Offset(0, fld.OrdinalPosition).Value = fld.name
objXL.Range("A1&quot.Offset(1, 0).Select
Next fld
End If

Do Until rs.EOF
For Each fld In rs.Fields
objXL.Range("A1&quot.Offset(0, fld.OrdinalPosition).Value = rs(fld.name)
objXL.Range("A1&quot.Offset(1, 0).Select
Next fld
rs.MoveNext
Loop

If strFileName = "" Then
objXL.Visible = True
Else
xlWB.SaveAs strFileName
objXL.Quit
End If

rs.Close
Set rs = Nothing
Set db = Nothing
Set xlWB = Nothing
Set objXL = Nothing
End Function


hth

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[Praxden]

Ben,

Don't be sorry you helped me. I am importing 400 excel file and it always misses something.I will try your function today.


Thanks


Praxden

 

[oharab]

Good luck! I do a similar run every month & usually find the worst part is checking everything has imported correctly!
There is no error checking in the code, so you may want to add some if you are doing such a big run.

Enjoy!

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[phil009]

Hi- I was just curious if this problem was ever solved because I am having difficulty with exactly the same thing and can not seem to get it to work for the life of me. If anyone can help me out with this it would be much appreciated.

Thanks,
Phil

 

[phil009]

If anyone needs it my query's name is "specificCompany" and I am trying to export it to "G:\Compete\test.xls"

Many thanks in advance,
Phil