CODEWITHSUNDEEP
ms-accessvba
Isaac Reefman
Isaac Reefman

Reputation: 597

Create .csv file from query through vba

I've created a macro that calls up the saved exports menu, so that a query can be exported as a .csv ready for importing to another database.

I'd like to reduce the number of steps for the user though - not just avoiding having to click ok etc, but also the exported file has currency formatting attached to some of the data.

So far my coding looks like this:

Public Function ExportProducts()
DoCmd.TransferText acExportDelim, "ExportSpecs", "ActProductInfo", _
"T:\Documents\Isaac Reefman\Product List", -1
MsgBox "Remember to clear formatting in the .csv file before importing it into Act", _
vbExclamation, "Export Reimnder"
End Function

This leaves me with 2 problems.

  1. I get an error saying the database or object is read only. Which it isn't, to my knowledge. For this reason it refuses to execute.
  2. It looks like it's going to export it with formatting (some of it has currency format). I need it to end up without any formatting for the database it's going to.

Upvotes: 0

Views: 2629

Answers (1)

Rene
Rene

Reputation: 1093

As for the error, you should add the file's extension like:

"T:\Documents\Isaac Reefman\Product List.csv"

As for formatting, you can opt out for formatting when you use the Export Wizard like, don't tick this box:

Export data with formatting and layout

You may also need to re-format in a query, by using CDbl(). Or Str() as Gustav suggested (which is a much much better suggestion!).

So: Str([tblTable].[Field1]) AS NameOfExportColumn, Str([tblTable].[Field2]) AS SecondColumnName

Then export the query instead of a table.

Upvotes: 2

Related Questions