Export table to txt file with a variable filename

[macollins7]

Hello, I tried doing some searching on this but wasn't finding much. I am very comfortable with Excel and so-so at creating macros in Excel with VBA but when it comes to Access I am much more of a rookie.

My current setup - Access 2007-2010, win7 64bit
I have a macro that does the following
- runs saved import to import excel file to table ("Table_Import")
- runs a make table query ("Query 1") to make new table ("Table 1")
- runs a make table query ("Query 2") to make new table ("Table 2")
- runs saved export to export "Table 2" as text file ("Export File").

My dilemma: Query 2 gives the user a prompt [Enter date] where users will enter the date of the records they want to include (ex: 7/10 or 7/10/12) in the final output. What I would like to do is have the name of the text "Export File" include the variable the user entered (ex: Export File_07.10.12).

Thanks in advance for any help!
Michael

[JoeM]

A Macro typically won't give you the flexibility you need to make it dynamic. The cool thing is, you can convert your Macros to Visual Basic (which will give you most of the code you need), and then you can edit the code to make it do what you want.

So, I would recommend highlighting your Macro and using the "Convert Macros to Visual Basic" functionality to convert your Macros to Visual Basic.
Then, I would create an Unbound User Forum with a Text Box where the User can enter the date that they want. Then have a Command Button on the Form to run your VBA code. You can incorporate the date entry they made in the Text Box into the name of the file. You could also use the Text Box entry as Criteria for your query.

One other note, your Make Table steps may be unnecessary. You do not need to write the records to a Table to export them. You can export from a Query just as easily as you can from a Table.

Take a crack at it, and write back if you need help with any of the steps.

[macollins7]

Thanks JoeM! I'll google unbound forms and take a look! as for the extra steps to make a table and then export I did this because when I manually tried to export my query results as text (manually via menu) I just kept getting an error. I't might work better when done via VB or Macro but not dealing with too much data so I wasn't too concerned with the extra step at this point.

Thanks again for the tips and I'll report back.

[macollins7]

Update:
Well I've definitely made some progress but not quite there. At the end of all this, I need to have a simple user process (i.e. Macro Button on custom toolbar -know how to do this) as the users of this do not know Access at all and I don't want them messing anything up.

- I have created an unbound form ("frm_EnterDate")
Attachment 8396
- I have created a macro that opens the form (I did this so that I would have a macro that I could add a toolbar button for)
- The "create file" button in the form runs macro 2
- Macro 2 runs code below: (some of the names have been changed for obvious reasons)
Function Create_Export_File()
On Error GoTo Create_Export_File_Err

DoCmd.SetWarnings False
DoCmd.RunSavedImportExport "Saved_Import"
DoCmd.OpenQuery "Query 1", acViewNormal, acEdit
DoCmd.OpenQuery "Query 2, acViewNormal, acEdit
DoCmd.SetWarnings True
DoCmd.TransferText acExportDelim, "Saved_Export", "Export_File", _
"Z:\folder1\folder2\folder3\Export_File_" & [Forms]![frm_EnterDate]![DateSelect] & ".txt"

Create_Paychex_Import_Exit:
Exit Function

Create_Paychex_Import_Err:
MsgBox Error$
Resume Create_Paychex_Import_Exit

End Function

-Unfortunately I don't really know if this is working because I am getting and error "object does not contain the automation object 'Macro 1 name here'" which there doesn't seem to be an easy answer to.

[JoeM]

If you place the command button on your Form, go to the Properties of the button and go to the On Click Event and selec Event Procedure. It will give you the shell of the VBA event procedure. Place your code in there. Then when you click the button, it will run the code.

Also, note that you can designate a Form to open automatically upon opening the database (in Access 2007 go to Access Options -> Current Database -> Display Form and select your form. This is quite handy, as all the user has to do is double click on the database and it pops right open to their Form. I use this a lot with my non-technical users, and half of them don't even realize that they are using Access!

[macollins7]

Thanks again! I've implemented both of your suggestions and it is all working. Had to work through an Access 2010 known issue (http://support.microsoft.com/kb/2581495) and found this page to be helpful when creating and calling the SpecificationName correctly (http://www.btabdevelopment.com/ts/2010ExpSpec). I was also able to add a msgbox showing the user how many rows were exported using this page (http://www.access-programmers.co.uk/...ad.php?t=86421). Now it's just a matter of hearing back from the client on their exact file specs required so I can make some tweaks. Thanks again for all of your help!

[JoeM]

Your welcome! Glad you got it all working out!