Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pass a Value from Access to Excel 3

Status
Not open for further replies.

TheAceMan1

Programmer
Sep 23, 2003
11,174
US
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 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
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 [blue]passing a single boolean value[/blue]. 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 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
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 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 

To be clear, I'm talking a public variable and function in an Access module and running that function from Excel.

Your Thoughts ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
What about SaveSetting/GetSetting/GetAllSettings/DeleteSetting registry functions as an alternative?

combo
 
Howdy Combo ...

You hit the nail on the head. Will check it out and ge back to you.

Your Thoughts ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
OK ...

@Skip: I wound up spending a little too much time getting the table to work, so I went with the registry commands suggested by combo.

@combo: Your suggestion was spot on. Simplicity in motion.

Take care guys ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
How are ya strongm,

There's no point in custom properties in the execution of passing a value from an external application. However, custom properties have been crucial in many of my designs.

Your Thoughts ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
Not quite sure why there'd be 'no point'.

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



 
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 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
>Their Custom property Stores are not [common]

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

I did say "as far as I know".

I would love to see an example.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
Ok, this illustrates (but isn't production code - e.g doesn't check if custom property already exists)

In Access:
Code:
[blue]Option Compare Database
Option Explicit

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

In a module in the Excel workbook from above:
Code:
[blue]Option Explicit

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

[COLOR=green]' 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")[/color]
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[/blue]

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:
[blue]Public Sub runexcel2()
    Dim myExcel As Excel.Application
    
    Set myExcel = New Excel.Application
  
    myExcel.Workbooks.Open "<pathforexcelworkbook>\fromaccess.xlsm" [COLOR=green]' example workbook with code shown below[/color]
    MsgBox myExcel.Run("DoIt2", 7)
    myExcel.ActiveWorkbook.Close False
End Sub[/blue]

Excel (note change from Sub to Function):
Code:
[blue]Option Explicit

Public Function Doit2(FromAccess As Variant) As Variant
    MsgBox FromAccess
    Doit2 = "OK"
End Function[/blue]
 
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 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top