Exporting to .txt with Specific Format

[KrenzyRyan]

Using this code, I am able to export a .txt file as needed, but I need it to export in a different (more specific) format.



Here is the code:

DoCmd.TransferText acExportFixed, "myExportSpec", "qryFFRDeFile", "C:\qryFFRDeFile.txt", True

This is what it is exporting:

3822391,0.008,0.010,0.000,0.000,0.000,0.000,0.000, 0.000

This is what I need it to export (the format is very important):

"3822391", 0.008, 0.010, 0.000, 0.000, 0.000, 0.000, 0.000, 0.000


Any help would be appreciated, thanks so much!

[hertfordkc]

From the example shown, I'd be inclined to do a Word macro on the exported file.
However, if the table from which you are exporting has all text fields, you could use VBA to insert the quotes and spaces.

[KrenzyRyan]

From the example shown, I'd be inclined to do a Word macro on the exported file.
However, if the table from which you are exporting has all text fields, you could use VBA to insert the quotes and spaces.

Do you have an example of how the VBA might look to add quotes and spaces? This would be perfect.

[hertfordkc]

There are multiple routes to get there, depending on your comfort level with SQL and VBA. The most transparent and simplest would be to query the data in your example and create calculated fields.
Here is an example of calculated values for the first two colums:
F1:"""" & [OF1] & """"
F2:" " & [OF2]
This query can serve as the basis for a "make table" query, which table can then be exported

[KrenzyRyan]

My VBA and SQL skills are very very minimal - is there a full example of this somewhere on the forum?

[hertfordkc]

You don't need SQL or VBA skills for what I posted. Create a query based on the table which you wish to export. Do not use a wizard, use a blank query and include the table. For each field which you wish to export, create a calculated field using the examples which I posted. In other words, use the F1:""""& [URFLD1]&"""" in the first field to put quotes around the contents, and my example for F2 for the rest of the fields to put one blank in front of your record fields. Run the query. Once the appearance is ok, save that query. Then open another query based on the one you just save. Designate it a "Make Table" query. Run it and it will ask for a table name. Give it a name, run the make table, save it and then export it. It's that simple.

[KrenzyRyan]

Perfect, that explanation helps a lot, I will try it tonight and post if successful! Thank you!

[KrenzyRyan]

Okay, it kind of works... this is what I end up with:

"3822391"," 0.008"," 0.010"," 0.000"," 0.000"," 0.000"," 0.000"," 0.000"," 0.000"

The space is perfect, now I just need the quotes gone (with the exception of the "3822391")...

[KrenzyRyan]

Still haven't figured it out, but I think I am getting closer:

I set an "Export" save file, removing the text qualifier. Therefore, I added the code you provided as follows: Expr1: """" & [field1] & """"

An error code then pops up saying: Run time error 3011, Access cannot find the object 'test.dat' Make sure it exists, etc.

What next?

[hertfordkc]

Still haven't figured it out, but I think I am getting closer:

I set an "Export" save file, removing the text qualifier. Not sure what you are telling me here. I described running a make table query, and saving the new table with an Access table name. It gets another name when you actually do the export. Since you are exporting via VBA, you might first use a DoCmd to run the make table query, with the new table named e.g. "tblexport". Then

DoCmd.TransferText acExportFixed, "myExportSpec", "tblexport", "C:\qryFFRDeFile.txt", True

Therefore,???? I added the code you provided as follows: Expr1: """" & [field1] & """"

An error code then pops up saying: Run time error 3011, Access cannot find the object 'test.dat' Make sure it exists, et

What next?

----------------------------------------------------------------------------------------------------------------------------------
used something like F1, i.e. you used quotes before and after all of the field variables.

Your post at 12:07 suggests that you were very close to having created the basic query, which was my focus. My earlier post addressed manually making a table, saving it and then exporting it. Looking again at your first post, I am assuming that, once you have a query producing the data you want, you will want to execute one or more DoCmd to produce a table to be exported, and then execute the DoCmd shown in your first post.

[KrenzyRyan]

Would you mind taking a look at my test enviornment...I just can't seem to figure it out. See attached:

[KrenzyRyan]

Okay, after re-making the database slowly, I figured it out. Attached is the final version for future referenes. Thank you hertfordkc

[hertfordkc]

I modified your test database and was about to send it when I saw your post. We had the same modifications.