Can I derive Import file name from form inputs ?

[Eric @ CMN, Evansville]

I have a form where the user selects a date range for which to run some
reports. Currently there is a separate menu option to process the monthly
imports to prep the data for reporting. The excel files that are imported
into Access are manually named each month with the month and year as part of
the file path and name (the rest of the file path and name are consistent.)
Can I take inputs from the form where the user defines the date range to run
the reports for to feed the import statements the proper path and file names,
rather than having to manually change those each month prior to running the
import process ?

Thanks !

 

[Ken Snell [MVP]]

Yes. Are you using TransferSpreadsheet? If yes, you can use "string
concatenation" expressions that incorporate the values from controls on a
form. Provide us with more details about your exact setup for the import --
including whether you're using VBA code or a macro, the form name, the
controls' names, the data type in each control, etc..

 

[Eric @ CMN, Evansville]

I'm still developing the form....as of now I have text boxes on the form that
break out the peices of the dates (converted to text with leading zeros added
if necessary) input by the user that I need for the path and file names. My
standard operating procedure is to use macros (at first....to develop a
prototye and test out logic) and then switch things to VBA code. Can I use
[form]![textboxname] logic in the file name parameter of a
transferspreadsheet action in a macro ? Or will I have to go straight to VBA
for this one ?

I seem to recall doing something similar at a previous employer, but like a
good employee, I left all my work from that employer at that
employer....."intelectual property" of the company, not mine...I was an
employee, not a contractor.

 

[Ken Snell [MVP]]

In the File Name argument of the TransferSpreadsheet action in the macro:,
for example

="C:\MyFolder\" & Forms!NameOfForm!NameOfTextBox & ".xls"

--

Ken Snell
<MS ACCESS MVP>

I'm still developing the form....as of now I have text boxes on the form
that
break out the peices of the dates (converted to text with leading zeros
added
if necessary) input by the user that I need for the path and file names.
My
standard operating procedure is to use macros (at first....to develop a
prototye and test out logic) and then switch things to VBA code. Can I use
[form]![textboxname] logic in the file name parameter of a
transferspreadsheet action in a macro ? Or will I have to go straight to
VBA
for this one ?

I seem to recall doing something similar at a previous employer, but like
a
good employee, I left all my work from that employer at that
employer....."intelectual property" of the company, not mine...I was an
employee, not a contractor.

Yes. Are you using TransferSpreadsheet? If yes, you can use "string
concatenation" expressions that incorporate the values from controls on a
form. Provide us with more details about your exact setup for the
import --
including whether you're using VBA code or a macro, the form name, the
controls' names, the data type in each control, etc..

--

Ken Snell
<MS ACCESS MVP>

"Eric @ CMN, Evansville" <[email protected]>
wrote
in message news[email protected]...

 

[Eric @ CMN, Evansville]

Thanks Ken !

In the File Name argument of the TransferSpreadsheet action in the macro:,
for example

="C:\MyFolder\" & Forms!NameOfForm!NameOfTextBox & ".xls"

--

Ken Snell
<MS ACCESS MVP>

I'm still developing the form....as of now I have text boxes on the form
that
break out the peices of the dates (converted to text with leading zeros
added
if necessary) input by the user that I need for the path and file names.
My
standard operating procedure is to use macros (at first....to develop a
prototye and test out logic) and then switch things to VBA code. Can I use
[form]![textboxname] logic in the file name parameter of a
transferspreadsheet action in a macro ? Or will I have to go straight to
VBA
for this one ?

I seem to recall doing something similar at a previous employer, but like
a
good employee, I left all my work from that employer at that
employer....."intelectual property" of the company, not mine...I was an
employee, not a contractor.

Yes. Are you using TransferSpreadsheet? If yes, you can use "string
concatenation" expressions that incorporate the values from controls on a
form. Provide us with more details about your exact setup for the
import --
including whether you're using VBA code or a macro, the form name, the
controls' names, the data type in each control, etc..

--

Ken Snell
<MS ACCESS MVP>

"Eric @ CMN, Evansville" <[email protected]>
wrote
in message I have a form where the user selects a date range for which to run some
reports. Currently there is a separate menu option to process the
monthly
imports to prep the data for reporting. The excel files that are
imported
into Access are manually named each month with the month and year as
part
of
the file path and name (the rest of the file path and name are
consistent.)
Can I take inputs from the form where the user defines the date range
to
run
the reports for to feed the import statements the proper path and file
names,
rather than having to manually change those each month prior to running
the
import process ?

Thanks !