Save Excel file

[mv8167]

My code below works except for the Save code. The SaveAs is good and works fine.

Can someone tell me what I need to change? thx

My code:

Code:

Public Function SaveOrSaveAsExcelSheet (FilePath)
Set objFso = CreateObject("Scripting.FileSystemObject")

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

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

objExcel.Visible = False
objExcel.DisplayAlerts = False
objExcel.Quit
Set objExcel=Nothing
Set objFso=Nothing

End Function

[rajpes]

objExcel.ActiveWorkbooks.Save
can we directly save it like this?i guess no
try to create object of workbook and then
workbook_object.save

[mv8167]

Rajpes,

Thx for sticking this out with me.

I thought that objExcel.ActiveWorkbooks.Save would work because it seemned that this objExcel.ActiveWorkBook.SaveAs (FilePath) worked. Now Im not sure.

I tried using the code that you had supplied last week on another thread but it also did not work. But the Excel file I have is already open. So, I dont think I need to open/crete (unless there is a reason to do so). In another Function I check if the Excel ss has been created or not. Here I just want to check, If my open Excel ss is not exist, used SaveAs. If it exsists, then Save.

I know I am missunderstanding something. Do I even need to use Save or SaveAs? All I want to do is save my open Excel file. Do I need to even check if it Exists or not?

So far I have tried all of the ways I can figure out, each in turn getting errors.

My Open Function works fine:

Code:

Public Function OpenClearOrCreateExcelSheet (FilePath, objExcel)

Set objFso = CreateObject("Scripting.FileSystemObject")

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

If bFileExist Then              ' If the File exists open it
        objExcel.Workbooks.Open (sSourceFile)
        objExcel.Visible = True
        'Clear DataSheet
        objExcel.ActiveWorkbook.Sheets("sheet1").UsedRange.Rows.ClearContents
Else                                         ' If the File does not exist, then Create a new file
        objExcel.Workbooks.Add
End If

objExcel.Visible = True
Set oSheet = objExcel.Activesheet
'Set oSheet = objExcel.ActiveWorkBook.WorkSheets(Sheet1)
Set Cells = oSheet.Cells
objExcel.DisplayAlerts = False

End Function

My Save/SafeAs Function does not work so well. (This code comes from an online source)
Code:

Error:
Object doesn't support this property or method: 'objExcel.saveas'

Function file: O:\QTP Tests\LibraryImageAccessFunctions-2.qfl
Line (1041): " objExcel.saveas FilePath 'Use the SaveAs method if the file has never been saved before".

Where can I go within QTP to read what methods are allowed for objects, etc?

Thx for helping me better understan this.

[rajpes]

I think you are making it unnecessarily complicated.
When you do some work on a workbook you could save it then and there itself, no need to call a separate function to do just this job.Moreover you are calling it from another function!

I copied some code from some site, see if it helps you

Code:

Dim objExcel, FilePath
FilePath="C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\gcr.xls"
Set objExcel=CreateObject("Excel.Application")
set objFso=CreateObject("Scripting.FileSystemObject")
objExcel.Visible=True

If Not objFso.FileExists(FilePath)  Then
objExcel.Workbooks.Add
objExcel.Cells(1,1).value="QTP"
objExcel.ActiveWorkbook.SaveAs (FilePath)

Else
set myFile= objExcel.Workbooks.Open (FilePath)
Set mySheet=myFile.Worksheets("Sheet1")
mySheet.cells(1,1).value="QTP"
objExcel.ActiveWorkbook.Save
End If
objExcel.Quit
Set objExcel=Nothing

-----
c u 2moro.

[mv8167]

I got it, finally. Dont know why, but I changed objExcel.close to objExcel.quit and everything works.

Well almost everything.

Only the obExcel.ac tiveworkbook.save does not save the file with the latest time stamp, thus it must not be saving as I expect it to. The code does the step, bbut the time stamp does not update to the newest time.

Do I need to do something to re save the xls file with the new time stamp.

or

Can I add a count, or change the save name to add the time or env?