+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Save Excel file (/Thread-Save-Excel-file)
Save Excel file - mv8167 - 07-13-2011
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 FunctionRE: Save Excel file - rajpes - 07-13-2011
objExcel.ActiveWorkbooks.Save
can we directly save it like this?i guess no
try to create object of workbook and then
workbook_object.save
RE: Save Excel file - mv8167 - 07-13-2011
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 FunctionMy 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.
RE: Save Excel file - rajpes - 07-13-2011
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=Nothingc u 2moro.
RE: Save Excel file - mv8167 - 07-14-2011
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?