Running multiple instances of Excel/Word

[chriscboy]

Hi,

I have created a new menu option in our database that creates reports in excel and word, using automation. I use commands like this:

Public oContract
* Create instance of class
oContract=CreateObject("emsOfficeClass.Office")
* Run the report and show in Excel
oContract.ContractPortfolioReport()

This generates an instance of a report in Excel. However if the user runs the report again, the old report gets destroyed, because variable oContract is used again. What I want to know is that once the report is created, is there any way to remove the reference to Excel, without actually losing the document that has been created?

I know I could save the document, close it and use ShellExecute to open it again, but ideally I would like the user to make the choice of saving the document or not.

 

[Olaf Doschke]

What the hell is emsOfficeClass.Office?

You normally instanciate Excel as oExcel = createobject("Excel.Application"), then do your stuff in excel and simply set oExcel.visible = .T. in the end.

After that you may simply set oExcel = .null., which does only destroy that reference and does not quit Excel. When restarting that whole process createobject() will open another instance of Excel, not destroying whatever has been done in the first pass.

Bye, Olaf.

 

[chriscboy]

emsOfficeClass.Office is a com class I have written which is a wrapper around all the office servers.

I tried setting oContract = .NULL. but as soon as that command is processed Excel display's its "Do you want to save the changes you made to 'Template1'?" message, which indicates that it is closing excel. If I say No to the question excel closes.

I also tried typing the following in the command window (in case my class was doing something funny):

Public oX
oX = CreateObject("Excel.Application")
oX.Visible = .T.
oX = .NULL.

The above code also cause excel to quit.

 

[JRB-Bldr]

"What I want to know is that once the report is created, is there any way to remove the reference to Excel, without actually losing the document that has been created? "

With Excel Automation you can have your VFP application "tell" Excel do a SAVE (or SAVE AS..), thereby not losing the document.

But after having done the SAVE it is typical to:

tmpsheet = CREATEOBJECT('excel.Application')
oExcel = tmpsheet.APPLICATION

  <do whatever>

* --- Prepare For Upcoming SAVE ---
oExcel.CutCopyMode = .F. && Clear the clipboard
oExcel.DisplayAlerts = .F.

* --- Save Results ---
xlSheet.SAVEAS(mcExclFName)  && Fully Pathed Excel File Name

* --- Close the Worksheet ---
oExcel.workbooks.CLOSE

* --- Quit Excel ---
oExcel.QUIT
RELEASE oExcel

Is there a reason that you want to leave Excel Open after having saved the created document?

Good Luck,
JRB-Bldr

 

[TamarGranor]

Excel never opens more than one instance. You'll have to find another approach.

Tamar

 

[TamarGranor]

Oops, I'm wrong. That's PowerPoint that's limited to a single instance. Word has some weirdness around this issue, too.

My suggestion, regardless is to find an approach that doesn't rely on opening multiple instances.

Tamar

 

[Olaf Doschke]

Hi,

hmm, I can verify that behaviour. I wonder why, though, as I use Excel for some kind of report and leave it open without using a public var. I wonder how I did it, but it must be possible.

Bye, Olaf.

 

[wgcs]

Have you tried
tempReference = GETOBJECT('',"emsOfficeClass.Office")
or
tempReference = GETOBJECT('',"Excel.Application")

I think these would work if you created a single public variable using CREATEOBJECT (or getobject) then use a different reference with GetObject to get temp-references to the initial one:

PUBLIC goMainExcel, goTempExcel
goMainExcel = CREATEOBJECT("Excel.Application")
goMainExcel.visible = .t.
WAIT WINDOW "Excel is created"
goTempExcel = GETOBJECT('',"Excel.Application")
WAIT WINDOW "Excel is re-accessed"
RELEASE goTempExcel
WAIT WINDOW "Second reference is released"
RELEASE goMainExcel
WAIT WINDOW "First reference is released"

Alternatively, you could use "pseudo" variables:

  PUBLIC gvExcel  && Holds name of public var holding current excel reference
  
  do openExcel
  wait window "First reference open"
  do openExcel
  wait window "Second reference open"
  do openExcel
  wait window "Third reference open"
  RELEASE &gvExcel  && only released third reference
  * Second and First references are still existant
  * and can be seen using LIST MEMORY

procedure OpenExcel
  * Ready to open excel?
  gvExcel = sys(2015)
  PUBLIC &gvExcel
  &gvExcel = create("Excel.Application")
  &gvExcel..Visible = .t.
endproc

- Bill

Get the best answers to your questions -- See FAQ481-4875.