How to identify the object reference to the existing(already opened) Excel sheet

How to identify the object reference to the existing(already opened) Excel sheet - Printable Version

+- 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: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: How to identify the object reference to the existing(already opened) Excel sheet (/Thread-How-to-identify-the-object-reference-to-the-existing-already-opened-Excel-sheet)

How to identify the object reference to the existing(already opened) Excel sheet - yogeesh - 09-12-2012

How to identify the object reference to the existing(already opened) Excel sheet in the Desktop and do"save as" of that file.I need to identify the existing(already opened) excel file in the desktop and do "save as" of that file in the designated folder. Please help me in getting VB Script code for this.

I have tried the below code, but its not working.

I am using Excel 2010.

Code:
Dim fso, FolderPath, objExcel, strPathExcel

      FolderPath = "c:\Excel_Reports"

      strPathExcel = FolderPath&"abcd.xls"

   ' Get instance of FileSystemObject.

   Set fso = CreateObject("Scripting.FileSystemObject")

    Set objExcel = GetObject("","Excel.Application")

      If not(fso.FolderExists(FolderPath)) then

       fso.CreateFolder (FolderPath)

          ' Create a new folder with the FileSystemObject object.

       End if

  objExcel.ActiveWorkbook.saveas strPathExcel

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

RE: How to identify the object reference to the existing(already opened) Excel sheet - krr - 09-26-2012

You can use below code,

Code:
Dim fso, FolderPath, objExcel, strPathExcel

FolderPath = "C:\Users\Desktop\"

strPathExcel = FolderPath&"abcd.xls"

' Get instance of FileSystemObject.

Set fso = CreateObject("Scripting.FileSystemObject")

Set objExcel = GetObject("C:\Users\Desktop\xyz.xls","")''you should provide the path of the already opened sheet. Else if you want to work with your code you need to create an object of the work sheet and then use.

If not(fso.FolderExists(FolderPath)) then

 fso.CreateFolder (FolderPath)

 ' Create a new folder with the FileSystemObject object.

 End if

objExcel.saveas strPathExcel

 objExcel.Close

 objExcel.Quit

You can use below code with minimal changes to your code. you can use in both ways.

Code:
Dim fso, FolderPath, objExcel, strPathExcel

FolderPath = "C:\Users\Desktop\"

strPathExcel = FolderPath&"abcd.xls"

' Get instance of FileSystemObject.

Set fso = CreateObject("Scripting.FileSystemObject")

Set objExcel = GetObject("","Excel.Application")

Set objWorkBook = objExcel.Workbooks.Open ("C:\Users\Desktop\xyz.xls")''Already opened excel path

If not(fso.FolderExists(FolderPath)) then

 fso.CreateFolder (FolderPath)

 ' Create a new folder with the FileSystemObject object.

 End if

objExcel.ActiveWorkbook.saveas strPathExcel

 objExcel.ActiveWorkbook.Close

 objExcel.quit

Hi, Yogesh you can use below code.

Code:
Dim fso, FolderPath, objExcel, strPathExcel

FolderPath = "C:\Users\Desktop\"

strPathExcel = FolderPath&"abcd.xls"

' Get instance of FileSystemObject.

Set fso = CreateObject("Scripting.FileSystemObject")

Set objExcel = GetObject("","Excel.Application")

Set objWorkBook = objExcel.Workbooks.Open ("C:\Users\Desktop\xyz.xls")''already opened file

If not(fso.FolderExists(FolderPath)) then

 fso.CreateFolder (FolderPath)

 ' Create a new folder with the FileSystemObject object.

 End if

objExcel.ActiveWorkbook.saveas strPathExcel

 objExcel.ActiveWorkbook.Close

 objExcel.quit