Save Excell file as

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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)
 
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)
 
Try replacing
appExcel.Application.SaveAs Filename:="R:\Service.xls"
WITH
ActiveWorkbook.SaveAs Filename:="R:\Service.xls"
 
Try replacing
appExcel.Application.SaveAs Filename:="R:\Service.xls"
WITH
ActiveWorkbook.SaveAs Filename:="R:\Service.xls"
 
Back
Top