Passing variables between excel files

[Steven]

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

 

[Tim Zych]

' 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]

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.