Export Text, Delimited, VBA (1 Viewer)

SFRonin

New member
Local time
Today, 10:14
Joined
Dec 28, 2015
Messages
3
Greetings. New to Access (about 2 weeks) and I've run into a situation that I've been unable to find an answer to by searching. Please keep in mind that I'm still a little bit Access-illiterate but I'm working on that. :banghead: So any code that's commented out and idiot proof is greatly appreciated.

I need to export the results of a query to a comma delimited text document. I need this operation done with a button, not the data-export-text-wizard available on the ribbon.

---Info---

The query in question only generates two columns of data.

I would like the column headings included in the text document.

Text document File Name would include "query name" and date stamp format "mmddyyyy"

Ideally, explorer.exe would be called to open file save location in new window, however, I believe I can accomplish this task if you don't feel like adding the code for that. I did another button with this feature awhile back that I can probably gain insight from.

Thanks for any help or insight you have. Also, if I need to clarify anything please let me know.
 

SFRonin

New member
Local time
Today, 10:14
Joined
Dec 28, 2015
Messages
3
Thanks for the nudge in the right direction. Between those and a few Google searches with my newly acquired proper terminology, I managed to get this working.


Code:
Private Sub Command[COLOR=Red]11[/COLOR]_Click()
DoCmd.TransferText acExportDelim, , "[COLOR=Red]MyQuery[/COLOR]", CurrentProject.Path & "\[COLOR=Red]Folder\Folder[/COLOR]\" & Format(Date, "mm-dd-yyyy") & ".txt", True

Dim Foldername As String

Foldername = CurrentProject.Path & "[COLOR=Red]\Folder\Folder[/COLOR]"

Shell "C:\WINDOWS\explorer.exe """ & Foldername & "", vbNormalFocus

End Sub
For future readers, RED TEXT, is what you would need to change for your specific database/file structure/query.


Thanks again.
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:14
Joined
Oct 17, 2012
Messages
3,276
Not quite how I'd have done the file save portion, but as long as it works, it's good!

At some point, you should look into the FileDialog object, though. It's part of the Office library, and it allows you to use the standard file windows to select files and folders (for any number of uses including opening) as well as (with, I believe, Access 2007 or later) the standard Windows save dialog window.

I've attached a text file with a small function library you may find useful going forward - it includes basic 'file open' and 'folder open' procedures, along with a few others I've found useful.
 

Attachments

  • FileHandlingLibrary.txt
    12 KB · Views: 95

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:14
Joined
May 7, 2009
Messages
19,169
i dont know if it occurs to all, but Filedialog just breaks when selecting file/folder on a network folder. i am using x64 bit ms access.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:14
Joined
Oct 17, 2012
Messages
3,276
Huh. I use it on a network folder every day, both with Access 2003 and Access 2013. In fact, I just tested them in the repository database that I pulled them from and they worked fine.

You're placing the value they return into a text box, a string variable, or a variant variable, right? Debug.Print works too. All those two functions do is return a string - it's up to you to handle it from there.

So what error message are you getting? Did you follow the notes at top and enable the Office libraries?
 

Users who are viewing this thread

Top Bottom