Pass a Value from Access to Excel 3

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TheAceMan1]

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.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[combo]

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

combo

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[sxschech]

Would either of these be of use?

https://stackoverflow.com/questions/24622348/calling-access-vba-function-from-excel

https://www.access-programmers.co.u...ll-a-ms-access-function-from-ms-excel.213166/

dim returnvalue as variant
returnvalue = acc.Run("YourRoutineName", YourParam1, YourParam2)

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[strongm]

Or you could use a worksheet's CustomProperties

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[strongm]

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

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

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[strongm]

>Their Custom property Stores are not [common]

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

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!

 

[strongm]

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

In Access:

[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:

[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:

[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):

[blue]Option Explicit

Public Function Doit2(FromAccess As Variant) As Variant
    MsgBox FromAccess
    Doit2 = "OK"
End Function[/blue]

 

[TheAceMan1]

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!
Also FAQ181-2886 Worthy Reading!