Export to Excel12

[LB79]

Hi all,

Happy Friday!
Wonder if someone could take a look at this code for me and tell me whats wrong with it. Ive looked at a few examples online but I think it should be working. When I try to open the exported file, it tells me taht the format doesnt match the file extension. Im using 2007. Does this look right?

Thanks

[COLOR=#1f497d][FONT=Arial]FileName = "MyFileName (" & Format([Today], "MMYYYY") & ").xlsx"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]Path = "C: \"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]VarSQL = "MyTbl"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, VarSQL, Path & FileName[/FONT][/COLOR]

 

[hgus393]

Hi all,

Happy Friday!
Wonder if someone could take a look at this code for me and tell me whats wrong with it. Ive looked at a few examples online but I think it should be working. When I try to open the exported file, it tells me taht the format doesnt match the file extension. Im using 2007. Does this look right?

Thanks

[COLOR=#1f497d][FONT=Arial]FileName = "MyFileName (" & Format([Today], "MMYYYY") & ").xlsx"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]Path = "C: \"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]VarSQL = "MyTbl"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, VarSQL, Path & FileName[/FONT][/COLOR]

Hi instead of acSpreadsheetTypeExcel12 use 10 ie.

[COLOR=#1f497d][FONT=Arial]FileName = "MyFileName (" & Format([Today], "MMYYYY") & ").xlsx"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]Path = "C: \"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]VarSQL = "MyTbl"[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]DoCmd.TransferSpreadsheet acExport, 10, VarSQL, Path & FileName[/FONT][/COLOR]

Hope this helps

Bob

 

[LB79]

Thanks for the suggestion but Excel 2007 is version 12.
Ive since discovered that this could be a bug in Excel - Some guys have been discussing it in another thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=203671

 

[vbaInet]

Yes, it seems to be a bug.

I would go for version 9. It should be backwards compatible.