Save As issue

[mv8167]

What is wrong with

Code:

objExcel.ActiveWorkbook.SaveAs (FilePath)

My code:

Code:

Public Function SaveOrSaveAsAndCloseExcelSheet (FilePath, objExcel)

'USE to Save or Save As and then Close an Excel sheet

sSourceFile = FilePath

Set objFso = CreateObject("Scripting.FileSystemObject")

'Check if file already Exists
bFileExist = objFso.FileExists(sSourceFile)

If  bFileExist Then             'Save - Use the Save method as the file has never been saved
    objExcel.Workbooks.Save '(FilePath)
Else                                     'Save As - Use the SaveA s method as the file has been saved
    objExcel.Workbooks.SaveAs (FilePath)
End If

objExcel.Visible = False
objExcel.DisplayAlerts = False

objExcel.Quit
Set objExcel=Nothing
Set objFso=Nothing
objExcel.Workbooks.Close()

End Function

[rajpes]

'assuming your filepath argument has full path fallowed by \filename.xls in the end

Code:

Public Function SaveOrSaveAsAndCloseExcelSheet (original,new)
Set objFso = CreateObject("Scripting.FileSystemObject")
bFileExist = objFso.FileExists(original)

If Not bFileExist Then
set wb=objExcel.Workbooks.open(original)
wb.saveas(new)
else
wb.save
End If

wb.close
Set wb=nothing
Set objExcel=Nothing
Set objFso=Nothing

End Function

[mv8167]

thx Raj,

Yes, my filename is good, thx for asking

Im getting an error saying that my function is not good. But it looks ok. Ill look more tommorrow.

thx
Lor

[mv8167]

Raj,

My:
FilePath = "O:/QTP Tests/QTPOutputData/ViewZipFax.xls"

Well, I figured out why, but now during the SaveAs I get the RunError:

Microsoft Office Excel cannot access the file 'O://QTP Tests/QTPOutputData/ViewZipFax.xls'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

Function file: O:\QTP Tests\LibraryImageAccessFunctions-2.qfl
Line (982): " set wb=objExcel.Workbooks.open(FilePath)".

Any thoughts on why?

I need to keep the objExcel in my call as then my script fails. I do have it Set in my intial test script, but if I do not pass objExcel, my scrip fails in my Functions.

[rajpes]

FilePath = "O:/QTP Tests/QTPOutputData/ViewZipFax.xls"

shouldn't it be
"O:\QTP Tests\QTPOutputData\ViewZipFax.xls" ?

[mv8167]

Maybe that's my issue: lol.

I seem to be getting two open xls sheets (Book1 and DocCviewHref.xls).

After Book1 is created, my goal is to determine if it is already saved. If not Save, if iut is present SaveAs.

But, my following code opens the DocViewHref.xls while the Book1 is still there. I just need to save the Book1 (which is currently open) as DocViewHref.xls and only keep this xls file iopemn.

What is wrong with my code? What do I change:

set wb=objExcel.Workbooks.open(original)

to?

thxx

[mv8167]

Raj,

I changed my address to be: O:\QTP Tests\QTPOutputData\ViewZipFax.xls

Now I get the Run Error:

Microsoft Office Excel cannot access the file 'O:\QTP Tests\QTPOutputData\ViewZipFax.xls'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

Function file: O:\QTP Tests\LibraryImageAccessFunctions-2.qfl
Line (956): " set wb=objExcel.Workbooks.open(FilePath)".

By my intended code, if the test does not find the DocViewHref.xls file, then Save. If a file is found, SaveAs.

Currently, the Book1 is saved to DocViewHref.xls, but Book1 remains open and DocViewHref.xls is open too.

Thoughts?

thx for yoyr help. ;-)

[rajpes]

oops Lorena,
Go to task manager and kill all processes with name "EXCEL" and then run and make sure wherever you are opening an excel in the code, it is closed after usage!

[mv8167]

After each run, I first close all open PDFs and all Excell ss, I even reestarfted my maxchine. Still the same.

Im trying again.

thx Raj.

---

Raj,

In your code, durring the Else statement, do I need to Set wb...?

Code:

Public Function SaveOrSaveAsAndCloseExcelSheet (original,new)
Set objFso = CreateObject("Scripting.FileSystemObject")
bFileExist = objFso.FileExists(original)

If Not bFileExist Then
       set wb=objExcel.Workbooks.open(original)
       wb.saveas(new)
else
        wb.save
End If

wb.close
Set wb=nothing
Set objExcel=Nothing
Set objFso=Nothing

----------------

[rajpes]

Pu it outside.

Code:

set wb=objExcel.Workbooks.open(original)
If Not bFileExist Then
wb.saveas(new)
else
wb.save
End if