CODEWITHSUNDEEP
excelms-accessexportvba
MAW74656
MAW74656

Reputation: 3539

Access outputTo Excel template and formatting

So I'm using VBA code to export a query to excel. This works, but the resulting file is ugly, and I'm trying to apply some simple formatting to it (thousands separators and dollar signs, column width, etc). I've seen code to do it manually in VBA, but this is ugly and will be difficult to change in the future, so I'm looking at using an Excel template file instead.

My problem is that setting a template file seems to have no effect on the resulting spreadsheet. What are template files supposed to do here? What other ways are there to apply simple formatting to this spreadsheet?

 DoCmd.OutputTo acOutputQuery, QueryName, "ExcelWorkbook(*.xlsx)", FileName & ".xlsx", OpenFile, template, 0, acExportQualityPrint

Upvotes: 0

Views: 7889

Answers (1)

RichardC
RichardC

Reputation: 794

You can't use a template file when using OutputTo with Excel. From the MSDN documentation:

DoCmd.OutputTo Parameters

  • Name: TemplateFile
  • Required/Optional: Optional
  • Data Type: Variant
  • Description: A string expression that's the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.

If you want to format your output, you will probably have to either do it manually in VBA (which can be a hassle, as you mentioned) or maybe get a third-party control/library/class that can do it for you, however I'm not aware of any personally.

Upvotes: 1

Related Questions