CODEWITHSUNDEEP
vbams-accesscsv
Roy Prins
Roy Prins

Reputation: 3080

Export from VBA/Access to CSV with a semicolon delimiter

I am on a restricted corporate network and I have some trouble programmatically exporting a CSV file from a query. The following command:

DoCmd.TransferText acExportDelim, , "query_name", strPath

tries to export a CSV with a comma [ , ] as delimeter. This results in an error since it equals the decimal separator in my region. Somehow the windows localization settings are not applied correctly in this case. The Excel "save as..." command does give a proper CSV file with [ ; ] delimiter.

Following from this post, I tried to create a specification (SpecName) to apply the semicolon. The dialog box prevented me from unchecking all boxes and saving a specification was not an option. My guess is that this is due to restrictions on my account.

So, the local settings are not applied properly and a custom specification is out of the question. What are my options here?

Upvotes: 1

Views: 4607

Answers (1)

Oliver
Oliver

Reputation: 3255

Two approaches are possible here:

  1. Roll your own csv-Export. It's not too difficult. Open your Query to obtain a Recordset, then iterate over rows with Do While Not rs.EOF and over Fields with For Each myField In rs.Fields and concatenate the data into a String. With this, you can specify your own separators and quotes around the values, if you have the separator inside the values.

  2. Create an Excel-Instance and copy the recordset into a sheet using Worksheet.QueryTables.Add(). Then you can use Worksheet.SaveAs sCSVName, xlCSVWindows to write that to a csv-file.

Upvotes: 2

Related Questions