Getting a constant/variable from another workbook?

[Gustaf]

I need to get a value (a string constant) from another open workbook when Autpen() is run. Let's call them workbook 1 and 2. When workbook 1 opens, and Autpen() is run, it should search workbook 2 for a constant/variable and set one of its own variables to that value.

I've seen that this can be done in Word, using the Document.Variables collection, but I find nothing like that in the Workbook model. Is there a way it can be done?

I know I can also save these values in the registry, but if there's a way to get them directly, that would be preferred.

Gustaf

 

[Bernie Deitrick]

Gustaf,

You could save the variable value to a range in workbook2 in code:

ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = varVariable

Then in your workbook1

myVarVariable = Workbooks("Workbook2Name.xls").Worksheets("Sheet1").Range("A1").Value

HTH,
Bernie
MS Excel MVP

 

[Patrick Molloy]

why not just

ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = _
Workbooks("Workbook2Name.xls").Worksheets("Sheet1").Range("A1").Value

 

[Bernie Deitrick]

Patrick,

I read 'variable' as meaning a variable as declared in VBA, with the OP wanting to assign it to
another declared variable in another project.

Bernie
MS Excel MVP

 

[Dave Peterson]

If workbook2 is open, you could create a function inside workbook2 that returns
the value of that variable.

Then workbook1 can call that function--either using Application.run or setting a
reference to that workbook2's project.

In a general module workbook2's project:

Option Explicit
Const myVariableNameHere as string = "Hi there"
Function ReturnMyVar() as String 'whatever
returnmyvar = myVariablenamehere
end function

Then in workbook1's project:

Option Explicit
Sub testme()
Dim Wkbk2Var As String
Dim wkbk1 As Workbook
Set wkbk1 = Workbooks("book1.xls")
Wkbk2Var = Application.Run("'" & wkbk1.Name & "'!ReturnMyVar")
MsgBox Wkbk2Var
End Sub

 

[Patrick Molloy]

funny thing the English language init?

Patrick,

I read 'variable' as meaning a variable as declared in VBA, with the OP
wanting to assign it to another declared variable in another project.

Bernie
MS Excel MVP

 

[jaf]

Hi Gustaf,
If you place the declares at the top of a module they should be visible to any module/form. (public)

Option Explicit
Dim str1 as string, str2 as string, myPI as double...
str1="hello"
str2="Monday"
myPI=3.14285714285714

Sub firstsub()
....
end sub

John