How to import 2 spreadsheet files with one file name entry

  • Thread starter Thread starter Steve
  • Start date Start date
S

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.
 
Which version of ACCESS are you using? Do you have a form open when the
macro runs?
 
Microsoft Access 2003 (11.8166.8221) SP3
There is no form used or open with this Macro
 
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?
 
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?
 
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/


Steve said:
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


Ken Snell MVP said:
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/
Click to expand...
Click to expand...
 
Back
Top