Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
10 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
vba access query export to text
Posted on 2014-12-17
This should be so straight forward. I'm trying to use vba to export an access query to text 'with formatting and layout.' If I do this manually (External Data ribbon > Text Export), it only allows a certain number of rows. I can create an export spec but since there are so many different layouts I need, I would have to create a multitude of them. I'm really looking for something which mirrors a generic 'with formatting and layout' export. Looks like this:
--------------------------
| GB_ENT_SKEY | AUD_INSRT_ID | AUD_INSRT_TMSTP | AUD_UPDT_ID |
--------------------------
| 16389278 | HIXRC_ETL | 7/24/2014 4:02:44 | |
--------------------------
| 16389318 | HIXRC_ETL | 7/24/2014 4:02:44 | |
--------------------------
Is there something simple without acExportFixed? . . . without Export Spec?
DoCmd.TransferText acExportFixed, "Export Spec", "qSel_xxxx", "yyyyy.txt", False, ""
--------------------------
| GB_ENT_SKEY | AUD_INSRT_ID | AUD_INSRT_TMSTP | AUD_UPDT_ID |
--------------------------
| 16389278 | HIXRC_ETL | 7/24/2014 4:02:44 | |
--------------------------
| 16389318 | HIXRC_ETL | 7/24/2014 4:02:44 | |
--------------------------
Is there something simple without acExportFixed? . . . without Export Spec?
DoCmd.TransferText acExportFixed, "Export Spec", "qSel_xxxx", "yyyyy.txt", False, ""
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2-
2
6 Comments
This is much more complex that perhaps you'd like but it's an excellent tool to have in the shed
Now, you can knock together as many
BatFile.WriteLine ("some string")
as required
So, you can open a recordset, use the fields in each record to construct a string and then write it
Absolute granularity and control for you.
You also need to do all the work.
It's a trade-off
'declare an object to be a filesystem object
Dim fs As Object
Dim BatFile As TextStream
Set fs = CreateObject("Scripting.FileSystemObject")
Set BatFile = fs.CreateTextFile("c;\temp\test.txt", True) 'some valid path
BatFile.WriteLine ("I am Open")
BatFile.Close
Set fs = Nothing
Set BatFile = Nothing
Now, you can knock together as many
BatFile.WriteLine ("some string")
as required
So, you can open a recordset, use the fields in each record to construct a string and then write it
Absolute granularity and control for you.
You also need to do all the work.
It's a trade-off
Active today
Wouldn't it be easier to export a .csv file with a header row?
Text files are not useful as reports because of the standard use of non-proportional fonts so nothing lines up. Plus, putting all the formatting characters in the text file makes it more difficult to be computer readable so the formatted files are pretty much good for nothing.
Text files are not useful as reports because of the standard use of non-proportional fonts so nothing lines up. Plus, putting all the formatting characters in the text file makes it more difficult to be computer readable so the formatted files are pretty much good for nothing.
I'm not that good with vba but if I read correctly, I'd have to create an export spec for each query I need to output. I don't mind some complexity but would need more guidance through it.
That said, I did test this approach which will work just fine.
DoCmd.TransferText acExportDelim, , "qSel_xxxx", "yyyyy.txt", False, ""
Should we close this chapter and I'll thank you profusely? Or are there other ideas?
That said, I did test this approach which will work just fine.
DoCmd.TransferText acExportDelim, , "qSel_xxxx", "yyyyy.txt", False, ""
Should we close this chapter and I'll thank you profusely? Or are there other ideas?
Active today
Fixed width files are a PITA and you have only the two options.
1. an export spec for each DIFFERENT format.
2. Learn how to use functions to coerce the columns to be fixed width in the query you use for export.
Select textfld1 & Space(15 - Nz(Len(textfld1),0)), Format(datefld1, "yyyymmdd"), Format(numericfld1, "000000000.00"), etc.
This is just three examples that show how to do it with the three basic data types. The first example uses the Nz() and Len() function to get the length of the contents of textfld1 and ASSUMING you want the output to be 15 characters subtracts the length from 15. So if textfld1 = "abcd", the length would be 4 and so 11 spaces would be appended to the end. You just have to use the correct length for each column. Dates are generally exported as yearmonthday when fixed length files are created and numeric fields are zero filled. So in this case, the output field is defined as 9.2. If you need something different, adjust the number of zeros and include/omit the decimal point.
1. an export spec for each DIFFERENT format.
2. Learn how to use functions to coerce the columns to be fixed width in the query you use for export.
Select textfld1 & Space(15 - Nz(Len(textfld1),0)), Format(datefld1, "yyyymmdd"), Format(numericfld1, "000000000.00"), etc.
This is just three examples that show how to do it with the three basic data types. The first example uses the Nz() and Len() function to get the length of the contents of textfld1 and ASSUMING you want the output to be 15 characters subtracts the length from 15. So if textfld1 = "abcd", the length would be 4 and so 11 spaces would be appended to the end. You just have to use the correct length for each column. Dates are generally exported as yearmonthday when fixed length files are created and numeric fields are zero filled. So in this case, the output field is defined as 9.2. If you need something different, adjust the number of zeros and include/omit the decimal point.
Should we close this chapter and I'll thank you profusely? Or are there other ideas?
CSV or XLS work for me, and are easy to crank out.
But you haven't said what the end purpose of these exports is.
And that kind of matters a great deal
CSV or XLS work for me, and are easy to crank out.
But you haven't said what the end purpose of these exports is.
And that kind of matters a great deal
PatHartman, I'm now getting your message that really the output requires some kind of export specification with either option 1 or 2. Since there were so many queries, I cajoled the recipients to accept a docmd.transferspreadsheet method.
Nick67,
The purpose was to accommodate and auditor request. I think they're satisfied with my deliverable and so . . .
THANKS TO ALL, AND TO ALL A GOOD NIGHT. . . . and Merry X-Mas and Happy New Year!
Nick67,
The purpose was to accommodate and auditor request. I think they're satisfied with my deliverable and so . . .
THANKS TO ALL, AND TO ALL A GOOD NIGHT. . . . and Merry X-Mas and Happy New Year!
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 15 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.