How to import 2 spreadsheet files with one file name entry

[Steve]

I have a macro which imports data to 2 tables (a "Data" table and a
"Payments" table) using the TransferSpreadsheet function twice, with the
following InputBox statement for the File name. The user is required to
enter the date (eg. 06052009) twice - it is the same for each entry.

="Q:\Data_files\" & "Planning_" & InputBox("Enter file name") & "_data.xls"
="Q:\Data_files\" & "Planning_" & InputBox("Enter file name") & "_payment.xls"

Is there some way I can change the macro so that the user only has to enter
the file name (date) once, to work for both actions.

 

[Ken Snell MVP]

Which version of ACCESS are you using? Do you have a form open when the
macro runs?

 

[Steve]

Microsoft Access 2003 (11.8166.8221) SP3
There is no form used or open with this Macro

 

[Ken Snell MVP]

If you want to continue using a macro, then you'll have to store the desired
value for "Enter file name" somewhere in the database so that the macro's
action can read it.

Easiest thing to do is use a textbox on a form for entering the value, then
let the macro action read the value from that form's textbox. But, before I
give any more suggestions, tell us more about the circumstances for
how/when/why this macro runs. Perchance, is it an AutoExec macro? or else
how do you trigger it?

 

[Steve]

2 files of data are received each day with the same file name format and in
the same location. This data is appended to the 2 Access tables using a macro
which is run manually.

I've created a form to enter the date (07052009). Can I now incorporate -
[Forms]![Enter_Date].[TheDate] with "Q:\Data_files\" & "Planning_" &
InputBox("Enter file name") & "_data.xls"

If I can, what is the correct syntax please?

 

[Ken Snell MVP]

Yes. In the File Name argument, put this expression (including the leading =
character):

="Q:\Data_files\" & "Planning_" & [Forms]![Enter_Date].[TheDate] &
"_data.xls"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

2 files of data are received each day with the same file name format and in
the same location. This data is appended to the 2 Access tables using a
macro
which is run manually.

I've created a form to enter the date (07052009). Can I now incorporate -
[Forms]![Enter_Date].[TheDate] with "Q:\Data_files\" & "Planning_" &
InputBox("Enter file name") & "_data.xls"

If I can, what is the correct syntax please?

--
Steve

If you want to continue using a macro, then you'll have to store the
desired
value for "Enter file name" somewhere in the database so that the macro's
action can read it.

Easiest thing to do is use a textbox on a form for entering the value,
then
let the macro action read the value from that form's textbox. But, before
I
give any more suggestions, tell us more about the circumstances for
how/when/why this macro runs. Perchance, is it an AutoExec macro? or else
how do you trigger it?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/