opening excel file under if statement 2

[wshm]

so I have this continous form...
and field txtUnit.
I would like to create a command button
which will open an Excel files only if
txtunit = "condition1"

eventually i would like to create about 3 command boxes,
which will open different excel file depending on the
value of the txtUnit.

ie. lets say the form is listing 3 forms(cont form so of course some fields are different) and they all
have different value for txtUnit(not unique)
listing1 = No. of visitors (value of txtUnit)
listing2 = No. of members
listing3 = No. of products

so i would like to do
If Me.txtUnit = "No. of Visitors" Then
open "spreadsheet1"
If me.txtUnit = "No. of members" Then
open "spreadsheet2"

first problem is that only way i know how to open
excel data is to use the "hyperlink" in properties..

another problem is that when i tried doing

If Me.txtUnit = "No. of Visitors" Then

Me.txt = "0"

i noticed that it makes the if statement true as long
as one of the form has "No. of Visitors"

I know I am making very complicated statement but
can this be done?

//condition 1 -> open a.xls
//condition 2 -> open b.xls
etc

am i making sense?? T_T

 

[TheAceMan1]

How are ya wshm . . .

Example:

[blue]   Dim locFile As String, idx As Integer, Rtn
   
   If Me.txtUnit = "No. of Visitors" Then
       idx = 1 [green]' a.xls[/green]
   ElseIf Me.txtUnit = "No. of members" Then
       idx = 2 [green]' b.xls[/green]
   ElseIf Me.txtUnit = "No. of members" Then
       idx = 3 [green]' c.xls[/green]
   End If
   
   If idx <> 0 Then
      locFile = Choose(idx, "Drv:\Path\a.xls", _
                            "Drv:\Path\b.xls", _
                            "Drv:\path\c.xls")
      Rtn = Shell(locFile, vbNormalFocus)
   End If[/blue]

See Ya! . . . . . .

 

[wshm]

ty aceman for your quick response.
im currently getting 'invalid procedure call or arguement
on this line.

Rtn = Shell(locFile, vbNormalFocus)

so i had

Dim locFile As String, idx As Integer, Rtn

If Me.txtUnit = "No. of Visitors" Then
idx = 1 ' a.xls
ElseIf Me.txtUnit = "No. of members" Then
idx = 2 ' b.xls
ElseIf Me.txtUnit = "No. of Visitors" Then
idx = 3 ' c.xls
End If

If idx <> 0 Then
locFile = Choose(idx, "C:\Users\syi\Desktop\Adm FY07 0131.xls", _
"Drv:\Path\b.xls", _
"Drv:\path\c.xls")

Rtn = Shell(locFile, vbNormalFocus)

End If

i just entered one index to see if it works but..
it's not working. am i doing something wrong?

 

[PHV]

You may try to replace this:
Rtn = Shell(locFile, vbNormalFocus)
with this:
Application.FollowHyperlink locFile

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wshm]

ty aceman and phv.

that coding did it phv. =)