Export Access- Table (with format) and Report(with conditional format) to Excel by using VBA code<--need help?

[bnfkru4567]

Dear Experts

I could use DoCmd.TransferSpreadsheet to export Access-table to Excel . However, all the formats were gone. (i.e. number/date format )

1) How could I transfer those formats from Access-table to Excel by using VBA directly ? (Note: no need to change the format again by using VBA code as use the format from Access-table)

2a) The Report in Access (with conditional format) --how can I transfer those rules from Access to Excel by using Excel ?
2b) If could not transfer those rules, is it possible to transfer the color format( For example : Red words only if fulfill the requirements) from the Report to Excel by using VBA?

Thanks

 

[oleronesoftwares]

1) How could I transfer those formats from Access-table to Excel by using VBA directly ? (Note: no need to change the format again by using VBA code as use the format from Access-table

It has been answered in an older thread

Export Query to Excel Keeping Formatting

Hi, I have several queries that I want to export to Excel. However when I i use the DoCmd.TransferSpreadsheet acExport command it exports but removes all formatting. A few of the columns are Currency is there any way to export and maintain the format that they are in access? Thanks Mike

www.access-programmers.co.uk

The way to keep formatting is to:

1) Create a empty spreadsheet with all of the formatting in place
2) In Access VBA, make a copy of that template file to the target filename (I use the MSO SaveAs dialog box to accept a target filename), and make a copy of the template to that filename
3) Then use this suggestion to transfer the data to the spreadsheet:

How to drive Excel with VBA (Access) in order to transfer values into spreadsheet
http://www.access-programmers.co.uk/forums/showthread.php?t=233104#post1190025"

 

[Pat Hartman]

OR, export a query instead of the table. Use the Format() function in the query and Excel will "see" the formatting.

 

[bnfkru4567]

OR, export a query instead of the table. Use the Format() function in the query and Excel will "see" the formatting.

My Table has number format and Report has conditional formatting . So how can I transfer from Report with conditional formatting to Query ?

 

[Pat Hartman]

Clearly you didn't try my suggestion. As you have discovered, it doesn't matter what formatting you have on your table. In fact, you should use NO formatting on the table since formatting just obfuscates the actual data.

Use a query and use the Format() function to format the fields you want formatted. Do NOT use the format property.

Keep in mind that you have much more control over formatting if you use a spreadsheet template or use OLE to automate Excel to do the formatting. Just depends on what you actually need and how much work you want to do to achieve your goal.

 

[sxschech]

If you use the template approach suggested by Pat and oleronesoftwares, here is some code that will give an idea of applying the format. As they said create or make a copy of the excel file that has all the formatting the way you want it to look. For my application, I created an excel file with the header row and one row of data, the data row contains the formatting and conditional formatting. Then the code will open this file and use the copy from recordset to insert the data on row 3. After that, it copies the formatting from row 2 and applies it to all the newly added rows and then removes the second row since it was only needed for layout purposes.
Since the code was customized for my spreadsheet, you would need to make some code adjustments. The code is somewhat generic in that it allows being able to choose the file (template) and query as well as do different customizations so can be used for different scenarios where other spreadsheets may be formatted differently.
The other option is code that pastes data into an excel file and then uses Access to apply formatting, colours, bold, etc after the fact, when conditional formatting isn't needed. I can provide an example of doing that way if you prefer.

Paste code below into a Module. I have included the helper functions for file naming, but may have missed something.
Code uses late binding, so won't need to worry about setting it up in Tools References.

--For some reason, when I pasted the code was informed exceeded 1000 characters, so attaching as a txt file--