Save Excell file as

[John]

I have code that opens up an excel file, refreshes the data and then saves
it. I also need to save the file as another name in another location after
it saves. I added this appExcel.Application.SaveAs
Filename:="R:\Service.xls" but it doesn't seem to be working. Below is all
of the code that I am trying to run. Thank you for any and all help I can
get.

Function refreshClosed()

Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")

Dim workBook As Object
Dim workSheet As Object

Dim strFile As String
Dim strFullPath As String

strFile = "ServiceSummary_" & Format(Now() - 1, "mmddyyyy")
strFullPath = "D:\UPSDATA\SF_Report\" & strFile

appExcel.Application.Visible = True

appExcel.Workbooks.Open strFullPath

Set workSheet = appExcel.Sheets("Report")


appExcel.Run ("refall")

appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.SaveAs Filename:="R:\Service.xls"
appExcel.Application.DisplayAlerts = True
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing


End Function

 

[Jack Leach]

It may not be ideal, but outside of the Excel sheet you can use the FileCopy
function in vba to copy it to a new location.

FileCopy Source Destination

Destination needs to be a complete filepath (including .xls)

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[KC-Mass]

Try replacing
appExcel.Application.SaveAs Filename:="R:\Service.xls"
WITH
ActiveWorkbook.SaveAs Filename:="R:\Service.xls"

 

[John]

That worked...thanks!!!!

Try replacing
appExcel.Application.SaveAs Filename:="R:\Service.xls"
WITH
ActiveWorkbook.SaveAs Filename:="R:\Service.xls"

 

[Ken Snell MVP]

No, do NOT use ActiveWorkbook. That will create another instance of EXCEL.

Use workBook.SaveAs Filename:="R:\Service.xls"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/