Export to Excel (xlsx)

[islu0102]

I am having a very frustrating time working with exports to Excel since my company upgraded from Office 2003 to Office 2010.

My current issue is as follows: I have an Excel workbook with two worksheets. One of the worksheets contains conditional formatting. I import the other worksheet into Access, run a few dozen queries, then I need to export two new tables (replacing both worksheets).

Everything works fine except the exporting. When I attempt to export directly (using the ribbon, macro or VBA), without formatting, nothing happens. the export seems to run, but the original workbook is unchanged.

When I attempt to do the same, re-naming each of the tables to be exported, nothing happens to the original. In 2003, a new worksheet would be created. If I attempt to export with formatting via the ribbon, Access seems to want to replace the entire workbook - which i do not want.

I would just delete the original before exporting the new, but i don't want to have to re-write all of the conditional formatting each day (this will be a re-occurring report).

I'm not a master of Access or Excel, but I'm not a newbie, either. This issue sure makes me feel like one, though. Does anyone have any insight, or can you point me to additional documentation on the export to Excel variations from 2003 and 2010, because I have searched and read dozens of explanations, but none answer my problem.

Thanks in advance.

 

[Alansidman]

Post your VBA code for the export. I had some similar issues when I first ugraded that required a small change in the VBA when I used the DoCmd.TransferSpreadsheet.

 

[islu0102]

I think I solved it...If I change the file to an xlsb file, I can do all of the exporting like I used to with xls. that's good, in case I need to add macros to the Excel file later.

Here is the original code, anyway - in case I need to use xlsx, again:

Option Compare Database
Option Explicit
Sub expFiles()
Dim curPath As String
curPath = CurrentProject.Path & "\All_Items.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "All_Items_1", curPath, -1
End Sub

To make it work, all I did was change the original file to xlsb and re-write the import and export code to match. Feel free to let me know what I screwed up.

Thanks!

 

[Alansidman]

change the DoCmd.TransferSpreadsheet line as below

DoCmd.TransferSpreadsheet acExport, 10, "All_Items_1", curPath, -1

and this should export an .xlsx as you wish.

Look at this link

http://msdn.microsoft.com/en-us/library/office/bb225982(v=office.12).aspx

 

[Frothingslosh]

Excel 2010 is acSpreadsheetTypeExcel12XML, not acSpreadsheetTypeExcel12. (That's the constant for the type of 10 that Alansidman listed above, iirc.)

 

[Alansidman]

Further to Frothingslosh's explanation


http://ss64.com/access/acspreadsheettype.html

 

[islu0102]

Thank you, all! It works great, now.