Export Access Query to Excel including Conditional Formatting (1 Viewer)

[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.

 

[Pat Hartman]

How do I use Excel automation in the Access function?

Excel has a much more complex object model than Access does and I don't do enough automation to be able to write it off the top of my head. Soooooooooo, what I do is when I want to automate Word or Excel, I open a document and turn on the macro recorder. Then I perform the action I want. Turn off the macro recorder and look at the generated code. Sometimes you can just copy and paste but most of the time,you have to adjust the way the Word/Excel object is referenced because the macro generates "internal" references but you are working "externally" so you have to use a different style. The concept is similar to writing code in a form's class module and using Me.SomeControlName to reference a control. But if you wanted to reference that same control from a different form,you would need to use Forms!someformname!SomeControlName.

 

[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)

Office VBA reference topic

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.