Passing variables between excel files
 

I tried to figure this out and looked through this website but do not get it.

In the following code there is a Workbook set to Wkbk. After Wkbk opens the
next line is to run the Auto_Open macro in Wkbk. In that macro I want to say
- for example depending on certain conditions: varMyTestNumber = -1 and then
when the Wkbk Auto_Open macro completes and returns to this macro I want to
be able to say:

If varMyTestNumber = -1 Then
"Code to run"
Else
"Run this code"
Endif

1) I cannot get a variable to return back from the Wkbk file. How do I do
this?

Another question:
2) How would I call a different macro in Wkbk file. I tried using
Application.Run but it keeps telling me it cannot find the macro. The other
macro is called "Private Sub GetPassCodeNumber()

Thank you,

Steven

'-------------------------------------------------------------------------------------------

Sub Tester()
Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

Application.ScreenUpdating = False

myPath = "C:\" '<- include that backslash!
myFileName = "Book1.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen
Else
MsgBox "The file is already open!"
End If

'*** I am getting nothing here ***'
If varMyTestNumber = -1 Then
"Code to run"
Else
"Run this code"
Endif

End Sub

Passing variables between excel files
 

' In Book1
Function Book1Value() As Integer
Book1Value = 1000
End Function

' In Book2
Sub FromBook2GetBook1Value()
Dim i As Integer
i = Application.Run("Book1!Book1Value")
MsgBox i
End Sub

Another way is to set a reference to Book1's VBA project in the VBE. Then
you can reference the function as if it exists in the same workbook. This
might be OK if a project is not distributed to users. Otherwise, there's
additional coding required to release the references in a particular order,
and it can be a little messy. My preference is to run the first way.

As for calling macros in different workbooks, using Run can be tricky with
real-world workbook names. The way to guarantee that it always works, no
matter what the workbook name.

- Wrap the workbook name with single quotes
- Double up single quotes

Given a workbook name of "Book 1's.xls" as an example.

Dim WkbName As String
WkbName = "Book 1's.xls"
' replace single quote with two single quotes
WkbName = Replace(WkbName, "'", "''")
' wrap single quotes around the name
WkbName = "'" & WkbName & "'"
Dim i As Integer
i = Application.Run(WkbName & "!Book1Value")
MsgBox i

But this approach will work for all names, from simple names like "Book1" to
names with spaces and single quotes in them.

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility




"Steven" wrote in message
...
I tried to figure this out and looked through this website but do not get
it.

In the following code there is a Workbook set to Wkbk. After Wkbk opens
the
next line is to run the Auto_Open macro in Wkbk. In that macro I want to
say
- for example depending on certain conditions: varMyTestNumber = -1 and
then
when the Wkbk Auto_Open macro completes and returns to this macro I want
to
be able to say:

If varMyTestNumber = -1 Then
"Code to run"
Else
"Run this code"
Endif

1) I cannot get a variable to return back from the Wkbk file. How do I do
this?

Another question:
2) How would I call a different macro in Wkbk file. I tried using
Application.Run but it keeps telling me it cannot find the macro. The
other
macro is called "Private Sub GetPassCodeNumber()

Thank you,

Steven

'-------------------------------------------------------------------------------------------

Sub Tester()
Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

Application.ScreenUpdating = False

myPath = "C:\" '<- include that backslash!
myFileName = "Book1.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen
Else
MsgBox "The file is already open!"
End If

'*** I am getting nothing here ***'
If varMyTestNumber = -1 Then
"Code to run"
Else
"Run this code"
Endif

End Sub

Passing variables between excel files
 

Wow! Thank you so much. That just spins my head of how that opens up so
much to me on things I was doing unbelievable workarounds on.