Calculate/Save XL Worksheet using VBA (1 Viewer)

[Elana]

Hi -

I've been trying to figure this out for two days and have gotten close, but now I'm stuck:

I need to open several excel workbooks, then calculate, save and close them. I want to do this from Access and here is what I've developed so far (using only one workbook as an example):

Public Function SaveCalcExcel()
Dim objxls As Object

Set objxls = CreateObject("Excel.application")

objxls.Workbooks.Open FileName:="C:\skiapplicationdata\resortinfo1.xls"
objxls.Visible = True
objxls.calculate
objxls.Workbooks.Close

objxls.Quit

Set objxls = Nothing

End Function

This seems to run all right, but it's not calculating the workbook. I added a line "objxls.save", but that doesn't seem to work as it get a message saying "Resume.xlw already exists...Replace?".

Anyway, I know I'm close, but I don't know what I'm missing here. Please help if you can set me straight. Thanks much!
E

 

[Elana]

I believe I've figured it out.

Public Function ExcelTest()

Dim objXL As Object


Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open "C:\skiapplicationdata\resortinfo1.xls"
.Calculate
.ActiveWorkbook.Save
.ActiveWorkbook.Close

End With
objXL.Quit

Set objXL = Nothing

End Function

HTH others who have the same question in the future.

E