export to excel and save

[Guest]

Hi,
How can I save changes to an excel spreadsheet from Access via VBA without
opening the file? (Access/Excel 2003)
I am trying to export a query to a specified Excel spreadsheet which
performs some calculations and then import the data back into an access
table.
The calculations are best suited to excel.
The problem is that Excel doesn't seem to calculate until you open the
workbook.
The export works fine, but if I then try to immediately import the data I am
getting import errors because the data isn't calculated (#DIV\0! etc). If I
do the export, open the spreadsheet, save it, and then import its fine.
Therefore I think I need to either do the export, save the changes to the
spreadsheet and then do the import using TransferSpreadsheet or calculate the
spreadsheet before import.
Any suggestions greatfully received

 

[Ken Snell \(MVP\)]

You can Automate EXCEL after you do the export, and use that to open the
workbook to do the calculations and then close the workbook; then you can do
the import.

As an initial impression, it seems to be a lot of extra work to use EXCEL as
a calculation tool, though. What prevents you from using ACCESS to do the
calculations?

 

[Guest]

Hi Ken,
The calculation includes projecting future values based on monthly variable
rates of return with re-invested yield. I started off in Access but having
reviewed some posts in this forum about using the right tool for the job I
decided to switch to excel.

Therefore the workbook holds a sheet with the calculations (sheet1), I
export the query to sheet2, and sheet3 summarises this ready for import back
to access.

I agree, if I could do this in access, it would make things far easier, but
I don't think I can. Any ideas on saving/calculating excel before the import
without having to open the file?

Thanks for your reply
Simon

 

[Ken Snell \(MVP\)]

Only way I know to have EXCEL do calculations is to open the workbook --
which you can do via Automation, and can do "invisibly" so that the user
doesn't see it happen.

You could insert this code between the two TransferSpreadsheet actions:

Dim xlApp As Object
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("PathAndNameOfWorkbookFile")
xlWB.Save
xlWB.Close
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

 

[Guest]

Ken,
Thanks very much that does the job.
Simon

Only way I know to have EXCEL do calculations is to open the workbook --
which you can do via Automation, and can do "invisibly" so that the user
doesn't see it happen.

You could insert this code between the two TransferSpreadsheet actions:

Dim xlApp As Object
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("PathAndNameOfWorkbookFile")
xlWB.Save
xlWB.Close
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing