Referencing a public variable in excel from access

[nxqviet]

Hi

This is a referencing question. I have an Access file that will open an
excel file, then run a macro in that excel. I need to assign a value
(varVersion ) to a public variable in the excel file name PubText.

==============
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "Template.xls"
strMacro = "TextString"
Set xls = CreateObject("Excel.Application")

xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)

Dim varVersion As Variant
varVersion = Forms!Test!Version.Value

xls.Run strFile & "!" & strMacro
.....Set PubText = varVersion ????? <------
======

Thanks,

V_

 

[strive4peace]

make a Sub in the Excel file to pass the value of the public variable
and assign it

Sub AssignPubText( pStrVar as string)
PubText = pStrVar
End Sub



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

V -

.... but I must ask ... what is is that you REALLY want to do? Perhaps
this is not the best way to go about it -- global variables are not too
stable...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*