Export Access Query to Excel including Conditional Formatting

[blime]

I am new to Access, so please forgive me if this has been asked before. I have carried out a search on the site but could not find a clear answer.

I would like to export a datasheet form to Excel including conditional formatting.

- I have a query with several lookup tables.
- A form is based on the query with a datasheet view only.
- I have set up conditional formatting where a field changes color in certain cells based on the selected from the pick list.

I would like the user to export this via a button to a spreadsheet. I have tried this, but the exported data in the spreadsheet does not include conditional formatting or the data type.

Would be grateful for advice on how I do this.

 

[June7]

Well, export won't carry over CF. Will have to independently code the Excel sheet with conditional formatting.

Excel doesn't really have 'data types'. Cells can be formatted for preferred display structure but they can hold anything regardless of format or whatever is in other cells of same column.

Review http://accessmvp.com/KDSnell/EXCEL_MainPage.htm

 

[GPGeorge]

Long story short, any conditional formatting in Excel will have to be added in Excel, or by using Excel automation in the Access function which creates the export.

 

[Gasman]

You could use an Excel template with the CF set in that, but regardless, it has to be created again.

 

[blime]

Long story short, any conditional formatting in Excel will have to be added in Excel, or by using Excel automation in the Access function which creates the export.

Long story short, any conditional formatting in Excel will have to be added in Excel, or by using Excel automation in the Access function which creates the export.

How do I use Excel automation in the Access function?

 

[Gasman]

How do I use Excel automation in the Access function?

If you are asking that, then a template might be a better option?

 

[blime]

If you are asking that, then a template might be a better option?

As I cannot export file with conditional formatting, I would like to use the Do.Cmd button so the user on click can export and open the excel file and the user can then decide also where they would like to save it on their OneDrive.

Would you know the VBA for this on the DoCmd please t?

 

[June7]

There are couple parts to your requirement;

1. export data to Excel - usually a query or report, not form - did you review the Ken Snell link in post 2

2. user selects destination folder (should they also assign file name) - usually involves File System Object

Both are common topics and many code examples are available.

 

[blime]

There are couple parts to your requirement;

1. export data to Excel - usually a query or report, not form - did you review the Ken Snell link in post 2

2. user selects destination folder (should they also assign file name) - usually involves File System Object

Both are common topics and many code examples are available.

I tried the suggested:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "MyName"

But sadly, it did not work.

 

[June7]

What does "did not work" mean - error message, wrong result, nothing happens?

 

[Gasman]

I tried the suggested:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "MyName"

But sadly, it did not work.

Have you even bothered to look at the syntax?

DoCmd.TransferSpreadsheet method (Access)

Access VBA reference

learn.microsoft.com

From that link

Range

Optional

Variant

A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

 

[apr pillai]

You may use the following syntax to avoid errors while exporting to Excel.

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True

Give Excel File Extension to .xlsx

A detailed discussion of TransferSpreadSheet Command, sample VBA Code and some formatting tips you will find here: Exporting Table/Query Data into Excel.