Can I pass a text box variable into an Access Macro

Posted on 2010-01-07
Medium Priority
Last Modified: 2012-05-08
Can I take a varible created by a text box from a form and input it into the TransferSpreadsheet file location in a Macro.  Can I do that in VB is it cannot be done within the macro itself.

Example:  Form Input:.  User puts a file name in a text box. (this is the file he is nameing for a report).  The text box is call fname.
Macro Test: TransferSpreadsheet:
                    Excel 2003
                    Table name: RPT
                    File: C:\Reports\Forms!Input!fname  (Is this part possible)
Question by:swimtech
LVL 29

Accepted Solution

leonstryker earned 2000 total points
ID: 26202240
Sure, but you will need to write a VBA macro. This is the syntax for the actual call:

DoCmd.TransferSpreadsheet acImport, 3, "myTable","C:\Reports\Forms\Input\" & strFileName, True, "A1:G12"


Author Comment

ID: 26202338
Thanks -- Works perfect!   I put htis code under On_Click for my run button.  On the form I give the user a choice to Print to Printer and/or Print to Excel.  Pritn to print was no problem but I wanted the user to be able to name the file something they would remember.  This works great.  Thanks you

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question