0

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.
Improve this question
3
  • Rene has already fixed the first problem - I left the extension out of the file name... Silly me. Commented Apr 27, 2018 at 6:10
  • Can you mark his answer as correct? Commented May 2, 2018 at 23:53
  • He solved the first part of the question in that answer, but the answer to the second part is still only in the comments. I said I'd accept that answer as correct if he put that element into the answer itself, but he hasn't done that - I don't feel like I can select an answer as accepted if it doesn't fully answer the question... Commented May 3, 2018 at 1:27

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Fantastic on the first count. So obvious, can't believe I did that. As for the formatting, I have not ticked that box, yet the currency fields still export to currency format in the .csv.
Will I have to code in something to open the .csv document, select the text and remove formatting, before closing the whole thing again? That seems unwieldy...
Create a query based on your table. Include all columns. Use the CDbl function to convert your currency columns. Save your query. Export your query rather then your table.
How do you mean? I've added that to the sql for the query (it already was a query) like this: CDbl([qryAddCodes].[Cost]) AS Cost, CDbl([qryAddCodes].[Price]) AS Price but that doesn't follow through to the .csv...
Works for me. You might have to dump and recreate your export specifications.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.