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").Valu e = varVariable

Then in your workbook1

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

HTH,
Bernie
MS Excel MVP


"Gustaf" wrote in message ...
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

why not just

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Gustaf,

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

ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = varVariable

Then in your workbook1

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

HTH,
Bernie
MS Excel MVP


"Gustaf" wrote in message
...
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

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


"Patrick Molloy" wrote in message
...
why not just

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Gustaf,

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

ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = varVariable

Then in your workbook1

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

HTH,
Bernie
MS Excel MVP


"Gustaf" wrote in message ...
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

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




Gustaf wrote:

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

--

Dave Peterson

funny thing the English language init?


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Patrick Molloy" wrote in message
...
why not just

ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = _

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Gustaf,

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

ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = varVariable

Then in your workbook1

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

HTH,
Bernie
MS Excel MVP


"Gustaf" wrote in message
...
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

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



"Gustaf" wrote in message ...
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