Excel - How to open two workbooks? 1

[bs6600]

I need to open two work books simultaneously (Input.xls and Rules.xls) from within VFP6.
I've tried this

loExcel=getobject(,"Excel.Application")	&& create Excel object
loExcel.Workbooks.Open("Input.xls")		&& Open Input workbook
loInputBook	=loExcel.activeworkbook		&& make a note of Input workbook

loExcel.Workbooks.Open("Rules.xls")		&& open Rules Workbook
loRulesBook	=loExcel.activeworkbook		&& make a note of Rules workbook

ix=loInputBook.ActiveSheet.range("a1").value	&& access cell in Input Sheet

iy=loRulesBook.ActiveSheet.range("a1").value	&& access cell in Rules Sheet

but it fails with "Member ACTIVESHEET does not evaluate to an object."
Please help!!

Bill Spence,
Dunfermline, Scotland

 

[alvechurchdata]

I've not tried this but is the second call to WorkBooks.Open overwriting the first book? Do you need to call WorkBooks.Add to create a second member of the WorkBooks collection before opening the second book?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[bs6600]

Thanks Geoff- I don't think so - by macro it goes

    Workbooks.Open Filename:="Input.xls"
    Workbooks.Open Filename:="Rules.xls"
*make Rules point at Input (Input has only one sheet)
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=Input.XLS!R1C1"
    Range("I2").Select
*Make input point at Rules  (Rules has multiple sheets)
    Windows("Input.XLS").Activate
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=[Rules.xls]Sheet1!R1C1"
    Range("F2").Select

Maybe I will have to rework the processing, avoiding any use of .activesheet

Bill Spence,
Dunfermline, Scotland

 

[JimWWinter]

Bill,

Not tested, but try this.

oX=CREATEOBJECT(,"Excel.Application")   
oWB1 = oX.Workbooks.Open("Input.xls")
oWB2 = oX.Workbooks.Open("Rules.xls")       
oS1 = oWB1.Sheets(1)
oS2 = oWB2.Sheets(1)

ix = oS1.Range("A1").Value
iy = oS2.Range("A1").Value

Regards,
Jim

 

[TamarGranor]

There can only be one ActiveWorkbook and one ActiveSheet at a time. As Jim suggests, talk to specific worksheet of each workbook.

Tamar

 

[jmcd0719]

You could open Excel twice and use the object for each to control.

loExcel1=getobject(,"Excel.Application") && create Excel object

loExcel2=getobject(,"Excel.Application") && create Excel object

loExcel1.Workbooks.Open("Input.xls") && Open Input workbook

loExcel2.Workbooks.Open("Rules.xls") && open Rules Workbook


loInputBook =loExcel1.activeworkbook && make a note of Input workbook


loRulesBook =loExcel2.activeworkbook && make a note of Rules workbook

ix=loInputBook.ActiveSheet.range("a1").value && access cell in Input Sheet

iy=loRulesBook.ActiveSheet.range("a1").value && access cell in Rules Sheet

 

[TamarGranor]

Actually, you can't really open Excel twice. You get the same instance each time. You can see this by trying it and then looking in the Task Manager. There's only one instance of Excel.EXE.

In addition, GETOBJECT() always grabs an open instance if there is one, so your code would use the same instance anyway.

Tamar

 

[JRB-Bldr]

Here is a suggestion....

These lines of code are cut out of working code, so they are not 100% complete, but they do the job and they might point you in the right direction.

mcExclFName = ADDBS(mcExcelDir) + "WorkBook1.xls"

* --- Open Blank Worksheet ---
tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION

* --- Set Excel to only have one worksheet ---
oExcel.SheetsInNewWorkbook = 1

* --- Delete the Default Workbook that has 3 worksheets ---
oExcel.Workbooks.CLOSE

* --- Now Add a new book with only 1 worksheet ---
oExcel.Workbooks.ADD
xlBook = oExcel.ActiveWorkbook.FULLNAME
xlSheet = oExcel.activesheet

<Do Whatever Within Workbook #1.....>

* --- Open 2nd Workbook ---
mcXLFile2 = ADDBS(mcExcelDir) + "Header Template.xls"
oExcel.Workbooks.OPEN(mcXLFile2)
oExcel.WINDOWS(JUSTFNAME(mcXLFile2)).ACTIVATE
oExcel.ROWS("1:1").SELECT
oExcel.SELECTION.COPY  && Copy Row 1 Into Clipboard

* --- Go Back to First Workbook ---
oExcel.WINDOWS(xlBook).ACTIVATE

* --- Paste Clipboard Row Over Workbook #1 Row #1 ---
oExcel.ROWS("1:1").SELECT
xlSheet.Paste  

* --- Close 2nd Workbook File ---
oExcel.WINDOWS(JUSTFNAME(mcXLFile2)).ACTIVATE
oExcel.ActiveWindow.CLOSE

* --- Save Excel Results ---
oExcel.CutCopyMode = .F. && Clear the clipboard
oExcel.DisplayAlerts = .F.

* --- Save Results ---
xlSheet.SAVEAS(mcExclFName)

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

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

Good Luck,
JRB-Bldr

 

[bs6600]

Thanks to all who replied. Especially Jim Winter - his suggestion worked for me.



Bill Spence,
Dunfermline, Scotland

 

[JimWWinter]

Bill,

Glad to help, and thanks for the star.

Regards,
Jim