word Excel vbscript for QTP 1

[porabai]

Hello All,

I am working in QTP. As a part of process, while testing for functionality of application during testing, i need to open word/excel/pdf file from a folder(Say my documents) and then add some data to word/excel files and save it back in the same folder (if possible with different name) and able to close. See the code below. I am able to launch word and excel but its prompting me to select the default files. please advise as how i can go ahead.

thank you for your help in advance
Sean

Dim wshShell
SystemUtil.Run "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE","","",""
CreateObject("WScript.Shell").SendKeys "%fo"

SystemUtil.Run "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE","","",""
CreateObject("WScript.Shell").SendKeys "%fo

 

[jges]

Try this:

  'Create Excel Object
  set objXL = WScript.CreateObject("Excel.Application")
  'Show it to the user
  objXL.Visible = true
  'Add a workbook
  objXL.WorkBooks.Add()

You can do similar for Word.

 

[porabai]

Thank you jges for the tip
I am getting error at the 2nd line.

i tried this way

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3
CreateObject("WScript.Shell").SendKeys "%fx"

after the last line the excel application prompts to save the book1. how can i force save and exit out sucessfully.

thank you
sean

 

[jges]

What are you trying to do with the SendKeys command?

Try this:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3
'CreateObject("WScript.Shell").SendKeys "%fx"
objExcel.ActiveWorkbook.SaveAs("C:\TEMP\testfile.xls")
objExcel.quit
set objExcel = nothing

 

[jges]

Be sure to change the SaveAs path to something reasonable for you before you run it.

 

[porabai]

Thank you once again jges.

Apart from functionality testing of applications, i need to open user daily used applications like word, excel, outlook, adobe reader and try to open along with the functionality application while testing. need to add/remove text in word/excel and send email while testing the application and close all of those opened applications in order to test for non breakage of code in them.during uninstallation of the applications some of the daily used applications are affected and we are analyzing the impact as part of performance issue.

 

[porabai]

thanks jges.
need help again from your side. how about opening an existing file from a temp folder and inserting the above test at the end and save it as different file everytime there is modification.

thanks a ton for your help and appreciate it.

sean

 

[mrmovie]

porabai (programmer),
how about objExcel.Open ? or .OpenWorkBook, or whatever the Excel's object models method is for opening an existing workbook?


I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[porabai]

Hello all
here is the script i am working on in QTP. It executes the vbs file and stores the image in folder. i am trying to show this image in the results as snapshot in QTP. please advise.
the file gets created in the folder but get an error at this line:
*******
The "Browser" object was not found in the Object Repository.
Check the Object Repository to confirm that the object exists or to find the correct name for the object.

Line (18): "Browser("Browser").CaptureBitmap ocsupport_file,False ' Use CaptureBitmap method to capture the screenshot, True will overwrite an existing error.bmp file".
Tip: If the objects in your application have changed, the Maintenance Run Mode can
help you identify and update your steps and/or the objects in your repository.


*******
The code is as follows:

Sub Run(ByVal sFile)
Dim shell
Set shell = CreateObject("WScript.Shell")
shell.Run Chr(34) & sFile & Chr(34), 1, false
Set shell = Nothing
End Sub
' Execute the file to get the screen capture

Run "C:\Support\OCSupport\OC_WHOAMI.vbs"
Wait (15)

Desktop.CaptureBitmap "C:\Sean\OCSupport.bmp"

Dim ocsupport_file ' Declare a variable which contains path of the error image
ocsupport_file = "C:\Sean\OCSupport.bmp" ' Place the image in folder
Browser("Browser").CaptureBitmap ocsupport_file,False ' Use CaptureBitmap method to capture the screenshot, True will overwrite an existing OCsupport.bmp file
Reporter.ReportEvent micFail,object_snap,"&lt;<img src='" & ocsupport_file &"'>&gt;"