How to open, refresh, save Excel workbook from Access?

[KitenutDave]

From an Access module, I need to open, refresh, and save a spreadsheet as
part of an overnight batch operation. A later process will query the
spreadsheet to get the updated results. Can anyone suggest some code to do
this (the opening, refreshing, and saving) that will work unattended?
Thx.

 

[Stefan Hoffmann]

hi Dave,

From an Access module, I need to open, refresh, and save a spreadsheet as
part of an overnight batch operation. A later process will query the
spreadsheet to get the updated results. Can anyone suggest some code to do
this (the opening, refreshing, and saving) that will work unattended?

http://www.mvps.org/access/modules/mdl0006.htm


mfG
--> stefan <--

 

[Klatuu]

The code you referenced is good, but it does not open an existing xls file,
it creates a new one here:

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

To open an existing xls file:

Set objActiveWkb = objXL.Workbooks.Open(strGetFileName, 0, True)
Set objXlSheet = objActiveWkb.Worksheets("SomeSheetName")

SomeSheetName can be a text value that is the name of a worksheet or in can
be an index reference to the sheet.

 

[Stefan Hoffmann]

hi Klatuu,

The code you referenced is good, but it does not open an existing xls file,
it creates a new one here:

Yup, i know. Sometimes i'd like to make the people think themself


mfG
--> stefan <--

 

[Klatuu]

Interesting concept <g>