passing a excel sheet object from one form to another

[woow]

Hi,

I have 2 forms lets assume its A and B. I want to pass a excel sheet object from form A to B so i can run a macro in the excel sheet from the form B.

How can i pass a object from one form to another.

thanks

[RhinoBull]

You may just declare your excel object as Public at the module level and use it throughout your app lifecycle.

[woow]

I am a newbie I am lost. Could u please give me an example

[woow]

Ok I kinda understand and did this in module level,

Public objXL As Excel.Application
Public objWB As Excel.Workbook
Public objWS As Excel.Worksheet
Public objMacro As Variant

and now i set the objects in form A,

Set objXL = New Excel.Application
Set objWB = objXL.Workbooks.Open(FileName:=path + "TimesExcel.xls")
Set objWS = objWB.Worksheets(2)

but now when i do
objMacro = objXL.Run("Macro2")

this in form B it says objects variable is not set??????

I don't know how to call the same object in form B

[RhinoBull]

You have to check if object is set first and rhen try to use it:

'Form1
If objXL Is Nothing Then
    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Open(FileName:=path + "TimesExcel.xls")
    Set objWS = objWB.Worksheets(2)
End If
 
'Form2
If objXL Is Nothing Then
    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Open(FileName:=path + "TimesExcel.xls")
    Set objWS = objWB.Worksheets(2)
Else
    objXL.Run "Macro2"
End If

Note: when application ends - don't forget to close excel object and also reset all public objects to Nothing.

[Dave Sell]

Originally posted by woow
Hi,

I have 2 forms lets assume its A and B. I want to pass a excel sheet object from form A to B so i can run a macro in the excel sheet from the form B.

How can i pass a object from one form to another.

thanks

A good practice is to make your Worksheet a Private data member on Form A and then allow a Public Get Property on Form A, which can be accessed from other Forms:

' frmFormA
Private m_wksFormASheet as Excel.Worksheet
'
Public Property Get Worksheet() As Excel.Worksheet
  Set Worksheet = wksFormASheet
End Property

Then from Form B:

frmFormA.Worksheet.Range("A1:C40").Select ' Etc...