Getting a constant/variable from another workbook?

  • Thread starter Thread starter Gustaf
  • Start date Start date
G

Gustaf

I need to get a value (a string constant) from another open workbook when Auto_Open() is run. Let's call them workbook 1 and 2. When workbook 1 opens, and Auto_Open() 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
 
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
 
why not just

ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = _
Workbooks("Workbook2Name.xls").Worksheets("Sheet1").Range("A1").Value
 
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
 
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
 
funny thing the English language init?


Bernie Deitrick said:
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
Click to expand...
 
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
 
Back
Top