First Time Trying a Macro in Access

[mcl]

I've done macros in word and excel. I really like those because I always
record my keystrokes first them edit it later. Gives you a starting draft.
Obviously there is no way to do that with access (right?).
This is at work where I'm doing this(Access 2000). Left access open on
Friday and went home contemplating where I need to go with this. Here at
home I have Access 2003.
What I need it to do is export the output from 13 queries I have in comma
delimited format with the first line being field names to already
established file names. The filenames would not change. Although it would be
nice to be able to tell it via a dialog box what directory to put them in.
That way I could give this to other people who could run it on their own
systems and tell it where to put the files on their own systems. The
database actually links to our oracle database and pulls the data out of
tables there. But I immedialy ran into a problem. On the available actions,
I saw no way to specify that I want comma delimited output with the first
line being field names. Can it be done?

 

[Steve Schapel]

mcl,

Sounds like you need a TransferText action in your macro for this. You
will notice this action has a 'Has Field Names' argument, so you would
apparently want this set to Yes. The trick with a TransferText macro is
to run through the process manually first, via the File|Export menu.
Then, on the last screen of the export wizard, click the Advanced
button, and save what you have done as an Export Specification (i.e.
defines delimiters etc), and then enter the name you saved it as into
the Specification Name argument of the TransferText.

 

[mcl]

Are Access 2003 and 200 different in what options you get. Here at home I
found the Transfer text and one of the options was comma delimited with the
option for header info. I tried it on one of my home databases and it worked
great. Can't wait till I get back to work tomorrow to try it on the access
2000. But I don't remember that option on that one. (Imagine, can't wait to
get to work so I can try something...OK, I'm a nerd).
OK, assuming I get this working to this level how do I add a dialog box at
the begining so someone can choose what directory to put the files in?

 

[mcl]

At work, tried it and it worked fine. When I first tried it on Friday I
didn't use the Transfertext. I noticed that at home.
Also, I named my output filename.csv and I never had to do anything after
that. The csv must have told it what to do. I did say I wanted field names
and the output is csv with field names.
Nothing else needs to be done .....................EXCEPT (;-).
The output now is to the same directory as the database. Can I add something
at the beginning which allows someone to pick the output directory for all
13 files?
Yes, I know I can add a path in the filename but I want people to be able to
choose their own output path.

 

[TJT]

Found this thread searching for similar advice -
I want to transfer text to excel workbook - already know how to set up worksheets
But would also like to know how to allow user to pick the directory
i.e. open a browse window to allow user to select directory.
thanks

 

[Steve Schapel]

mcl,

If you put an unblound textbox on a form, where the user can enter the
path for the output directory/file, then I think you will be able to
refer to this in the File Name argument of the TransferText macro, using
syntax such as...
=[Forms]![NameOfForm]![NameOfTextbox]
Of course, the form will need to be open at the time that the macro is
run. That's about as sophisticated as you can get with a macro, as far
as I know. For the user to select the folder, you would need to look at
something like this...
http://www.mvps.org/access/api/api0001.htm

 

[Steve Schapel]

TJT,

See my reply to mcl.

 

[TJT]

Steve: thanks - i did something similar to your suggestion in another DB
but wanted something a little fancier
will checkout your link
thanks

 

[TJT]

mcl - here's a link to code that opens a browse window for the user to select the location to save the file(s) - with instructions -quite simple to use, if you are ready to try some vba code.
This routine captures the user selected path into string szPath - you would have to name the file to be exported in your code.
As Steve already suggested save the value of szPath into a textbox on your form to use it with transfer text
I looked at the Open and Save API browsers but this seems more suited to my purpose and maybe yours as well.
http://www.mvps.org/access/api/api0002.htm