Process needs refining,

[Lynn A.]

I have a process that is currently in 4 separate steps that are operated by
individual buttons on a form in Access.

In Access:
Button 1. Select the csv file to open (filename gets stored in text box)
Button 2. Open the csv file in Excel with Access still running in the
background
(in Excel several macros are run to modify csv file so that it only has the
usable data in it, save the file as an xls File, close Excel)
Button 3. Select the file name created above (filename gets stored in text
box)
Button 4. Import the data into Access

I would like to do this in three buttons

Question 1. when I save the csv file to an excel file is there a way to
"capture" the new xls file name and directory and store it into the access
textbox without having to browse for it??

If I can, I can have this all done in three steps instead of having to
select the filename again.

I greatly appreciate your input.
--
Lynn Arlington

[Pieter Linden]

ly***********@hotmail.com (Lynn A.) wrote in message news:<f0**************************@posting.google. com>...

I have a process that is currently in 4 separate steps that are operated by
individual buttons on a form in Access.

In Access:
Button 1. Select the csv file to open (filename gets stored in text box)
Button 2. Open the csv file in Excel with Access still running in the
background
(in Excel several macros are run to modify csv file so that it only has the
usable data in it, save the file as an xls File, close Excel)
Button 3. Select the file name created above (filename gets stored in text
box)
Button 4. Import the data into Access

I would like to do this in three buttons

Question 1. when I save the csv file to an excel file is there a way to
"capture" the new xls file name and directory and store it into the access
textbox without having to browse for it??

If I can, I can have this all done in three steps instead of having to
select the filename again.

I greatly appreciate your input.

If you stuff the name of the file into a variable, you can just pass
that variable to some other procedure, like fImportFile(strFileName)
or something. Personally, I like getting *all* the information I need
up front, so the user can answer a few questions, click a button and
walk away while the processing is being done. Especially handy when
you have processes that can take quite a while, like importing/parsing
complicated files.

To be a bit more specific - grab the filename using the FileOpen API
from www.mvps.org/access see the first article in the API section.

use fIsAppRunning to determine if Excel is running. If it is and
you're opening macros, you can run them with a command line switch, I
think.
Since you use the FileOpen API, you can just stuff the filename into a
variable and do whatever you want to with it later - use it for
another process, whatever. When you're done, quit Excel, if you want.
Terminate the automation session. (Do it all in code... you never
actually have to see the Excel stuff running at all if you don't
want.)

I think you should be able to do all this with one button... If you
really wanted to, if you want to process multiple Excel files in
succession, you could put them all into one folder, use the BrowseAPI
function from Access Web, and use the Dir() function to loop through
the contents of the folder, processing each one in sequence.

Not nearly as hard as it sounds - just do each thing in a separate
routine and then tie the routines together...

HTH,
Pieter

[Pieter Linden]

ly***********@hotmail.com (Lynn A.) wrote in message news:<f0**************************@posting.google. com>...

I have a process that is currently in 4 separate steps that are operated by
individual buttons on a form in Access.

In Access:
Button 1. Select the csv file to open (filename gets stored in text box)
Button 2. Open the csv file in Excel with Access still running in the
background
(in Excel several macros are run to modify csv file so that it only has the
usable data in it, save the file as an xls File, close Excel)
Button 3. Select the file name created above (filename gets stored in text
box)
Button 4. Import the data into Access

I would like to do this in three buttons

Question 1. when I save the csv file to an excel file is there a way to
"capture" the new xls file name and directory and store it into the access
textbox without having to browse for it??

If I can, I can have this all done in three steps instead of having to
select the filename again.

I greatly appreciate your input.

If you stuff the name of the file into a variable, you can just pass
that variable to some other procedure, like fImportFile(strFileName)
or something. Personally, I like getting *all* the information I need
up front, so the user can answer a few questions, click a button and
walk away while the processing is being done. Especially handy when
you have processes that can take quite a while, like importing/parsing
complicated files.

To be a bit more specific - grab the filename using the FileOpen API
from www.mvps.org/access see the first article in the API section.

use fIsAppRunning to determine if Excel is running. If it is and
you're opening macros, you can run them with a command line switch, I
think.
Since you use the FileOpen API, you can just stuff the filename into a
variable and do whatever you want to with it later - use it for
another process, whatever. When you're done, quit Excel, if you want.
Terminate the automation session. (Do it all in code... you never
actually have to see the Excel stuff running at all if you don't
want.)

I think you should be able to do all this with one button... If you
really wanted to, if you want to process multiple Excel files in
succession, you could put them all into one folder, use the BrowseAPI
function from Access Web, and use the Dir() function to loop through
the contents of the folder, processing each one in sequence.

Not nearly as hard as it sounds - just do each thing in a separate
routine and then tie the routines together...

HTH,
Pieter