DoCmd.TransferSpreadsheet - error message

[Crookshanks]

Hello guys,

I am having trouble with the TransferSpreadsheet Method. I worked fine but for a reason unknown for me now I getting the following error message:

Runtime error 3274 - External table is not in the expected format.

The code that raises the error is:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_PROJECTEN", diroutput, True

tbl_Projecten is an internal table (not a linked table) and diroutput is the filename with path.

Yesterday I installed the Office2007 conversion pack. After that the error occurred. So, I removed the conversion pack but the problem persists.

Q: Does anybody know that this error means exactly and has a possible solution? The strangest thing is that it worked before.

Regards,

 

[Remou]

It seems that the workbook already exists and has been edited. Either rename or delete the existing workbook.

 

[Crookshanks]

Thanks for your answer. I've checked it. De outputfile (diroutput) does not exists. When running the export the systeem creates the excel-sheet and then comes up with the error. Leaving me with a blank Excelworkbook.

 

[Remou]

Does diroutput include .xls?

 

[Crookshanks]

Yes, I included the following:

f Right(diroutput, 4) <> ".xls" Then diroutput = diroutput & ".xls"

 

[Remou]

Can you export to something simple, such as:

diroutput = "C:\Data\Test.xls

 

[Crookshanks]

Thanks. Yes, to my surprise that works. Getting confused.
What is the thought about the hint?

 

[Remou]

I am not sure as you did not include the value of diroutput. It may include some unexpected character, an extra ", or an &; or the path may be particularly long.

 

[Crookshanks]

Sounds logical. I.e. the following path causes an error

C:\Documents and Settings\Administrator\Desktop\Projecten_110907.xls

and this one not

C:\test\projecten_1213.xls

does not

Perhaps the [space] in Documents and Settings?

 

[Remou]

In this case I think it is possible that you do not have write permissions for the directory. You could try creating a new text document manually.

 

[Crookshanks]

I do not think so. I have administrator rights. I will do some further testing. Thanks for putting me in the right direction, I will keep you posted?

Perhaps anybody knows the criteria of the outputpath?

 

[Remou]

Yes, please, it will be useful for anyone experiencing similar problems.

 

[zollo9999]

Any New on this post?
I also have problems using this method.

Thanks


Zollo VBA Developer

 

[zollo9999]

I think I have solved my problem.
It seems I can't write to the C: drive on this PC, (although I can create folders).

I've successfully exported the xls to another folder on the C Drive.

I've added code to kill/delete any existing versions of the xls (turn off error handling in case their is no xls)

On Error Resume Next
  Kill "C:\Database-Front-Ends\TempDocs\RptTaskStatusByStaffMember.xls"
On Error GoTo ErrorHandler
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRptTaskStatusByStaffMember", _
      "C:\Database-Front-Ends\TempDocs\RptTaskStatusByStaffMember.xls"

I'll have to use automation to open the xls and reformat it.

Pitty this is extra work when it is much easier using the VBE/GUI File>Export.



Zollo VBA Developer