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
Printable View
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
You may just declare your excel object as Public at the module level and use it throughout your app lifecycle.
I am a newbie I am lost. Could u please give me an example
Ok I kinda understand and did this in module level,
Code: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,
but now when i doCode:Set objXL = New Excel.Application
Set objWB = objXL.Workbooks.Open(FileName:=path + "TimesExcel.xls")
Set objWS = objWB.Worksheets(2)
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
You have to check if object is set first and rhen try to use it:
Note: when application ends - don't forget to close excel object and also reset all public objects to Nothing.VB Code:
'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
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:Quote:
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
VB Code:
' frmFormA Private m_wksFormASheet as Excel.Worksheet ' Public Property Get Worksheet() As Excel.Worksheet Set Worksheet = wksFormASheet End Property
Then from Form B:
VB Code:
frmFormA.Worksheet.Range("A1:C40").Select ' Etc...