Can I edit the results of a query in VBA?

[Splinters]

I have a query that gives me data from a table, and I then export it as text to Excel. In Excel I do some edits, insert columns with special characters as delimiters, then copy it into a JScript data set.

This all takes some time, and has a number of steps that need to be done in a certain order - prone to errors on my part...Is it possible to use VBA to make the changes in the query to reduce the manual steps perhaps even to remove Excell from the process entirely and just save the query to a text file?

The query has 5 fields:
TxtProductCode Unique to each item
curCurrentPrice The listed price
txtHeading The Main description of the item
txtDetails Additional details
txtVender A name of who has the item

In Excel I change all instances of '"' to ' inch', then insert the delimiter fields, and the final line for the item looks like this:

["TxtProductCode","curCurrentPrice","txtHeading","txtDetails","txtVender"],

Can VBA do this, or even read the query and modify each record then write it to a text file?

 

[tehNellie]

Yes, you could remove Excel from the process completely.

You can simply export the query results as a csv instead of an excel file, for example.

Some of the work can be done directly in the query. Have a look at the Replace() function which could be used to format """ into "inch" for example.

If you need further re-formatting that cannot be done directly in the query itself you can always open the query within VBA using a recordset and directly manipulate then export the information, line by line if necessary, as required.

 

[Splinters]

Yes, you could remove Excel from the process completely.

You can simply export the query results as a csv instead of an excel file, for example.

Some of the work can be done directly in the query. Have a look at the Replace() function which could be used to format """ into "inch" for example.

If you need further re-formatting that cannot be done directly in the query itself you can always open the query within VBA using a recordset and directly manipulate then export the information, line by line if necessary, as required.

Thanks for the response.

I could use the Replace() function - but that replaces the characters in the table as well...something I don't want to do.

I'll take a shot at using VBA to make the changes, not my best suit but I'll play with it a bit & see what happens.

Thanks.