Need Help Getting VBA to Export Query as .txt then .zip final Folder
Hey Everyone,
I'm looking to create an Access 365 sub that does the following things:
1.) Creates a Folder
2.) Exports the results of a query to that folder in a .txt tab delimited format
3.) Copies a file from the C drive to this folder
4.) Zips the folder
I'm a novice with coding in general so I'm being stopped at step 2. My code to create the folder is:
That seems to work well
The query I want to export is based on a single table. So getting the query to work is easy, but exporting as a .txt file has tripped me up. Everything I try fails.
Here is an example table:
I've also attached that example table here as an excel sheet.
This is my basic select query with some slight formatting:
The resulting query looks like this:
If anyone could help me with this I would really appreciate it. Thank you and have a wonderful day.
FlowersTable.xlsx
I'm looking to create an Access 365 sub that does the following things:
1.) Creates a Folder
2.) Exports the results of a query to that folder in a .txt tab delimited format
3.) Copies a file from the C drive to this folder
4.) Zips the folder
I'm a novice with coding in general so I'm being stopped at step 2. My code to create the folder is:
Dim rstemp As String
rstemp = "//SharedDrivePath/" & Me.OrderID.Value & ".FlowerOrder"
If Dir(rstemp, vbDirectory) = "" Then MkDir rstempThat seems to work well
The query I want to export is based on a single table. So getting the query to work is easy, but exporting as a .txt file has tripped me up. Everything I try fails.
Here is an example table:
I've also attached that example table here as an excel sheet.
This is my basic select query with some slight formatting:
SELECT [FlowerName] & "_" & [Color] AS Flower_Color, Format([SproutDate],"yyyy/mm/dd") & "_" & Format([SproutDate],"hhnn") AS FormatSproutDate, FlowersTable.ScanCode, FlowersTable.FrostDate
FROM FlowersTable;The resulting query looks like this:
If anyone could help me with this I would really appreciate it. Thank you and have a wonderful day.
FlowersTable.xlsx
ASKER
Hey Gustav, thanks so much for responding to my post. I'm trying your excellent looking zip function code now and it looks like I'm getting a variable not defined error regarding "ForWriting"
Is there another function that I need to add first?
With FileSystemObject.OpenTextFile(ZipTem p, ForWriting, True)
Is there another function that I need to add first?
Perhaps you miss the scripting reference?
If you don't use early binding, the enum that holds ForWriting is declared here:
If you don't use early binding, the enum that holds ForWriting is declared here:
#If EarlyBinding = False Then
Public Enum IOMode
ForAppending = 8
ForReading = 1
ForWriting = 2
End Enum
#End IfASKER
Ah, thank you! That works perfectly now!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's it! You saved the day! Thank you for all of your help!
You are welcome!
Zip and unzip files and folders with VBA the Windows Explorer way