How to reference open Excel from Word in different Subs? 1

[gtaborda]

Hi

I have used the code from Vroscioli in thread 840925 to open excel from word.
Works fine.

However i am now trying to continue adding data from another Routine (cmbbox_afterupdate) and there seems there is no reference (I get a "424 Needs an Object" error)

How can I give the Excel Objects a global reference so I can use it on any other routines within the module?

This is how I opened the Excel...

Public Sub OpenExcel()

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim sh as Excel.Worksheet

Set xl = CreateObject("Excel.Application")
xl.Visible = true
Set wb = xl.Workbooks.Open("filename")
Set sh = wb.Worksheets("worksheetname")

txtYourTextBox = sh.[a1]

'wb.Close
'xl.Quit
end sub

Thanks in advance

 

[PHV]

Define the objects outside the procedure body:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim sh as Excel.Worksheet

Public Sub OpenExcel()
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gtaborda]

aaaaaaaaaaaahhhhhhhhhhhh!

How stupid !

Thank you so much !!!!