Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-28-2015, 10:11 AM   #1
SFRonin
Newly Registered User
 
Join Date: Dec 2015
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
SFRonin is on a distinguished road
Export Text, Delimited, VBA

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. 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 is offline   Reply With Quote
Old 12-28-2015, 10:46 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,194
Thanks: 82
Thanked 452 Times in 410 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Export Text, Delimited, VBA

You want to look into
Creating and saving an export specification
DoCmd.TransferText
String manipulation
and
FileDialogObjects

That should at least get you started.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
SFRonin (12-28-2015)
Old 12-29-2015, 12:00 AM   #3
SFRonin
Newly Registered User
 
Join Date: Dec 2015
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
SFRonin is on a distinguished road
Re: Export Text, Delimited, VBA

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 Command11_Click()
DoCmd.TransferText acExportDelim, , "MyQuery", CurrentProject.Path & "\Folder\Folder\" & Format(Date, "mm-dd-yyyy") & ".txt", True

Dim Foldername As String

Foldername = CurrentProject.Path & "\Folder\Folder"

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 by SFRonin; 12-29-2015 at 02:52 AM.
SFRonin is offline   Reply With Quote
Old 12-29-2015, 05:38 AM   #4
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,194
Thanks: 82
Thanked 452 Times in 410 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Export Text, Delimited, VBA

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.
Attached Files
File Type: txt FileHandlingLibrary.txt (12.0 KB, 43 views)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-29-2015, 06:08 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,155
Thanks: 57
Thanked 2,302 Times in 2,208 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Export Text, Delimited, VBA

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 12-29-2015, 06:46 AM   #6
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,194
Thanks: 82
Thanked 452 Times in 410 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Export Text, Delimited, VBA

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?

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Reply

Tags
delimited , export , text , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] export Space delimited Text in AC2010 ? smig General 0 01-18-2015 05:21 AM
How-Export a report as a text with Delimited miguel vasquez Reports 3 08-14-2006 08:06 AM
Export text (space delimited) doran_doran Queries 2 01-06-2004 07:21 PM
export table as delimited text captnk Modules & VBA 2 06-06-2003 03:25 PM
Export to Comma Delimited Text Cindy_B General 7 12-10-2002 07:24 AM




All times are GMT -8. The time now is 08:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World