Access to Excel export

[lloyd2002]

I'm trying to export tables to Excel using the following code:-



Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Light", "W:\STOCK AVAILABILITY\Iveco Stock Availability.xls", True, "Light"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Medium", "W:\STOCK AVAILABILITY\Iveco Stock Availability.xls", True, "Medium"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Heavy", "W:\STOCK AVAILABILITY\Iveco Stock Availability.xls", True, "Heavy"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Driveaway", "W:\STOCK AVAILABILITY\Iveco Stock Availability.xls", True, "Driveaway"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Platinum", "W:\STOCK AVAILABILITY\Iveco Stock Availability.xls", True, "Platinum"
End Sub


The code creates the file, but I get an error message saying the data is corrupt. The files size for the excel file is also very small considering the amount of data I'm trying to export.

Any ideas?

 

[Joe4]

Welcome to the Board!

acSpreadsheetTypeExcel12 is Excel 2007 format. The extention for Excel 2007 files is "xlsx" not "xls".
See: AcSpreadSheetType | Access | SS64.com

 

[lloyd2002]

Hi Joe,

Thanks for the advice, I changed the extension, but still receive the same problem.

The file size is 32KB, whereas with the amount of data I would have expected it to be a few hundred. And I receive an error when I try to open that states "the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

I also tried changing the type to 12Xlm as I'm using Excel 2010 with xlsx extension, but still the same issue.

 

[Joe4]

Why are you populating the last argument for named range? I believe that is only for Imports (there are not named ranges in Access).

Try this for the first one:

[COLOR=#333333]DoCmd.TransferSpreadsheet acExport, 10, "Light", "W:\STOCK AVAILABILITY\Iveco Stock Availability.xlsx", True, ""
[/COLOR]

Here is a little trick I used. Get one working in a Macro first, and then use "Convert your Macros to Visual Basic" to get the syntax you need to apply.

 

[lloyd2002]

That's done it - thanks Joe

 

[Joe4]

You are most welcome!