macro to run a function with select case to open an Excel spreadsheet

[Bob]

Hi,

Apologies for posting this in 2 groups as not sure where it fits -
coding or macros.

I'm trying to open one of several Excel spreadsheets from a command
button on a form.

What I have at the moment is a macro with RunCode as its Action
The macro runs the function with the value of a text box on a form as
the variable value

=OpenKPI([Forms]![frmChooseAdviser]![Adviser])

The function is as follows

Public Function OpenKPI(Adviser As String)

Select Case Adviser

Case "Zach McArthur"
OpenKPI = "\\s-coyote\ifpstaff\Advisers_WIP\Zach Data
Capture.xls"

Case "Stuart McCarron"
OpenKPI = "\\s-coyote\ifpstaff\Advisers_WIP\Stuart Data
Capture.xls"

Case Else
OpenKPI = "\\s-coyote\ifpstaff\Advisers_WIP\Data Capture.xls"

End Select

End Function

When I run the macro I get an error stating "The expression you
entered contains invalid syntax" and "You may have entered a comma
without a preceding value or identifier" which doesnt make any sense
to me.

When I click OK to that, the Action Failed box pops up where it lists
the Argument as
\\s-coyote\ifpstaff\Advisers_WIP\Stuart Data Capture.xls

which is correct.

So, it identifies the correct file path based on the Select Case in
the function but it fails to open the file.

Any ideas what I'm doing wrong.

Thanks

Bob

 

[Paolo]

Hi Bob,
perhaps you have to declare the type that the function return so try in this
way
Public Function OpenKPI(Adviser As String)as string

HTH Paolo

 

[Klatuu]

Nowhere in you code do you actually open the file.
The RunCode action only executes the function you point to.
If you want to open the selected file, you will need to use either the Shell
Command or the FollowHyperlink in your code.

There is also another issue. How are you passing the arugment to the
function. In other words, how to you get the Adviser value into the function?

And the last comment I would make is it is not a good idea to hardcode
values into a function like that. What happens when Zach or Stuart are gone?
Now, you have to modify and redeploy your application.
Data belongs in tables, not in code.

 

[Jim Burke in Novi]

You may need to use the Eval function when passing a parameter that
references a form control value. Give that a shot. Try:

=OpenKPI(Eval("[Forms]![frmChooseAdviser]![Adviser]"))


I know you don't need to do that in a normal VBA function call, but
sometimes you need to use the Eval function to evaluate a form control value,
and it's possible this is one of those instances, since the call is being
done form a macro and not from VBA code. Try it and see what happens. I don't
see anything wrong with the function itself. You also might want to try just
referencing the form control value in the function instead of passing it as a
parameter. You SHOULD declare the function as returning a string, and make
sure the form control has a value in it before trying to execute your select.
(or before calling the function if you send it as a parameter)

 

[Bob]

Thanks everyone,

I've taken on board what Dave said about data belonging in tables and
created a table with records of the hyperlink type.
Then a query linking that table and this info [Forms]!
[frmChooseAdviser]![Adviser] displays the appropriate hyperlink for
the Adviser. Clicking the hyperlink opens the spreadsheet.
Its a bit clunky as I have to click a button first (to run the
query)and then click a hyperlink in the dataview results of the query.
I'd prefer to just click the button and the spreadsheet opens but at
least it works which is a lot better than it was.

Thanks again

Bob