×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Pass a Value from Access to Excel
3

Pass a Value from Access to Excel

Pass a Value from Access to Excel

(OP)
Howdy All,

Office 2003
Need to pass a value from Access to Excel (VBA preferred). The value tells excel which files in 1 of 2 folders to process. I had the idea of a custom environment variable but would perfer a more up to date method.

In this scenario Access opens an Excel file that does most of the processing.

Your Thoughts? ...

See Ya . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

RE: Pass a Value from Access to Excel

Hi,

Haven't seen you for a while.

How about putting the value in a table that Excel would query first, to determine which folder to process?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Pass a Value from Access to Excel

(OP)
Hi Skip. Ahhh ... my post finds you well. I retired in 2015 and enjoying it.

A table seems to be a good idea however, I'm just passing a single boolean value. I'll give the table a shot tomorrow and let you know the results.

Your Thoughts ... and honors to be in the company of old friends. thumbsup2

See Ya . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

RE: Pass a Value from Access to Excel

Or make a new sheet in the Excel workbook with a single boolean field in A1.

You could run an update query to change the boolean value before opening the Excel file.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Pass a Value from Access to Excel

(OP)
Good mourning Skip.

I first tried my original idea with environment variables but quickly realized we're talking two separate environments (one for each process ... Access/Excel), so can't connect.

The above brought to mind Public Variables, which are set in the declaration section of a module. Then it hit me ... if I install a function in the same module to read that public variable, I should be able to run the function from Excel and return the value (hmmm I'm not as rusty as I thought).
Now I'm sure I have the needed method/code in my library (it pretty extensive) but have not come across it yet.
Do you remember this method Skip?

Your Thoughts ...

See Ya . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

RE: Pass a Value from Access to Excel

What about SaveSetting/GetSetting/GetAllSettings/DeleteSetting registry functions as an alternative?

combo

RE: Pass a Value from Access to Excel

Or you could use a worksheet's CustomProperties

RE: Pass a Value from Access to Excel

Not quite sure why there'd be 'no point'.

Not worth the effort (compared to using, say, the registry), possibly ...



RE: Pass a Value from Access to Excel

(OP)
strongm . . .

The registry is common between Access and Excel. Their Custom property Stores are not. At least as far as I know. In this case passing the variable thru the registry simply made it all easy ... and done quickly. I have yet to imagine how I would pass a variable using custom properties.

With the highest respect for your opinion ...

See Ya . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

RE: Pass a Value from Access to Excel

>Their Custom property Stores are not [common]

But they can write to and read from each other's customproperties

RE: Pass a Value from Access to Excel

Ok, this illustrates (but isn't production code - e.g doesn't check if custom property already exists)

In Access:

CODE

Option Compare Database
Option Explicit

Public Sub runexcel()
    Dim myExcel As Excel.Application
    
    Set myExcel = New Excel.Application
    
    myExcel.Workbooks.Open "<pathforexcelworkbook>\fromaccess.xlsm" ' example workbook with code shown below
    myExcel.ActiveSheet.CustomProperties.Add "Test", 7
    myExcel.Run "DoIt"
    myExcel.ActiveWorkbook.Close False 'close without saving. If we were to save, then workbook will retain custom property 
End Sub 

In a module in the Excel workbook from above:

CODE

Option Explicit

Public Sub Doit()
    MsgBox GetCustomByname("Test")
End Sub

' Necessary since Microsoft, despite some documentation to the contrary, do not allow us to reference CustomProperties by Name, only by Index, i.e we cannot do CustomProperties("Test")
Private Function GetCustomByname(strName As String) As Variant
Dim prop As CustomProperty
GetCustomByname = "Custom property not found: " & strName
For Each prop In ActiveSheet.CustomProperties
    If prop.Name = strName Then
        GetCustomByname = prop.Value
        Exit For
    End If
Next
End Function 

But if you only need a transient value, then in fact you can just pass parameters with the .Run function that we used, and get values back, as previously suggested by sxschech

Access:

CODE

Public Sub runexcel2()
    Dim myExcel As Excel.Application
    
    Set myExcel = New Excel.Application
  
    myExcel.Workbooks.Open "<pathforexcelworkbook>\fromaccess.xlsm" ' example workbook with code shown below
    MsgBox myExcel.Run("DoIt2", 7)
    myExcel.ActiveWorkbook.Close False
End Sub 

Excel (note change from Sub to Function):

CODE

Option Explicit

Public Function Doit2(FromAccess As Variant) As Variant
    MsgBox FromAccess
    Doit2 = "OK"
End Function 

RE: Pass a Value from Access to Excel

(OP)
strongm ...

Thanks for the critique. It was worthwhile and put another method I wasn't aware of at my disposal. All in all and especially for a single value, the registry method fits the problem directly and with minimum code. For a greater number of passed values, I'd probably use the .run function as you've mentioned by sxschech.

You guys take care ... Ya Hear ...

Your thoughts ...

See Ya . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close