Automating Access / Excel with batch file
 

I'm trying to automate a process involving Access and a linked Excel
spreadsheet that is protected with a password. My approach is to build a
batch file that will open an Excel (2003) spreadsheet and execute a macro
that unprotects the sheet, saves and closes it thereby allowing the batch
file to run the Access code (which updates the spreadsheet file), and finally
run another Excel macro to protect and save the changed Excel file.

Can this be done with command line switches / options? Am I asking the
impossible? Is there a betterway? Thanks.
--
Art

Automating Access / Excel with batch file
 

Why not just open the Excel spreadsheet and drive the whole process from your
Workbook_Open procedure? You can automate Access from your Excel VBA code
(or vice-versa) and you would never need to close, save, and reopen Excel,
plus much greater flexibility than you could do with batch files and command
line switches. You can keep the whole process hidden (invisible to the user)
if desired.

If you have not used automation, the basic idea is to include a reference to
the Access object model in your Excel project, then you can create an object
variable to contain a whole Access session:
Dim AccessApp as Access.Application
You can then use all the VBA code you would normally use from Access if you
remember to put AccessApp.___ ahead of all your base objects. Too much to
teach in a quick response, but you can find lots of info by searching MSDN on
the topic of automation.

"Art" wrote:

I'm trying to automate a process involving Access and a linked Excel
spreadsheet that is protected with a password. My approach is to build a
batch file that will open an Excel (2003) spreadsheet and execute a macro
that unprotects the sheet, saves and closes it thereby allowing the batch
file to run the Access code (which updates the spreadsheet file), and finally
run another Excel macro to protect and save the changed Excel file.

Can this be done with command line switches / options? Am I asking the
impossible? Is there a betterway? Thanks.
--
Art

Automating Access / Excel with batch file
 

Well, that certainly puts a new twist on things. I'll try that next week.
Thanks.
--
Art


"K Dales" wrote:

Why not just open the Excel spreadsheet and drive the whole process from your
Workbook_Open procedure? You can automate Access from your Excel VBA code
(or vice-versa) and you would never need to close, save, and reopen Excel,
plus much greater flexibility than you could do with batch files and command
line switches. You can keep the whole process hidden (invisible to the user)
if desired.

If you have not used automation, the basic idea is to include a reference to
the Access object model in your Excel project, then you can create an object
variable to contain a whole Access session:
Dim AccessApp as Access.Application
You can then use all the VBA code you would normally use from Access if you
remember to put AccessApp.___ ahead of all your base objects. Too much to
teach in a quick response, but you can find lots of info by searching MSDN on
the topic of automation.

"Art" wrote:

I'm trying to automate a process involving Access and a linked Excel
spreadsheet that is protected with a password. My approach is to build a
batch file that will open an Excel (2003) spreadsheet and execute a macro
that unprotects the sheet, saves and closes it thereby allowing the batch
file to run the Access code (which updates the spreadsheet file), and finally
run another Excel macro to protect and save the changed Excel file.

Can this be done with command line switches / options? Am I asking the
impossible? Is there a betterway? Thanks.
--
Art