Open Excel Sheet By button / text box data.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sydious - 20 Feb 2008 08:59 GMT

I would like to set up a button that when pressed, would open the
coresponding Excel spreadsheet. The speadsheet file name would be that same
thing each time but would have the coresponding date added to it. Example
file names:

"Test_Sheet_02_01_2008"
"Test_Sheet_02_02_2008"
"Test_Sheet_02_02_2008"

This is the VBA Code that i have so far. I assume I am going wrong with
trying to reference the txt box value.

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stAppName As String

stAppName = "Excel.exe
\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet_" &
[Forms!].[Form1].[txtFileDate].[Value]
Call Shell(stAppName, 1)

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

The text box value would have "2-01-2008" entered into it.

The desired result would be it opening sheet "Test_Sheet_2-01-2008"

If this is possible, could this also be modified to import data from that
sheet also using a diffrent button?

Reply to this Message

Jeanette Cunningham - 20 Feb 2008 09:52 GMT

An easy way is to use Application.FollowHyperlink

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Dim stPath As String

stPath =
"\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet_" &
"Me.txtFileDate"
Application.FollowHyperlink stPath

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

note: stPath needs to be the full path including the name of the file to
open

Jeanette Cunningham

>I would like to set up a button that when pressed, would open the
> coresponding Excel spreadsheet. The speadsheet file name would be that
[quoted text clipped - 34 lines]
> If this is possible, could this also be modified to import data from that
> sheet also using a diffrent button?

Reply to this Message

Sydious - 20 Feb 2008 10:22 GMT

Using this code, it thinks the file name is:
"\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet_Me.txtFileDate"

Its not taking the value of txtFileDate and adding it to the the
\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet_ file
name.

> An easy way is to use Application.FollowHyperlink
>
[quoted text clipped - 59 lines]
> > If this is possible, could this also be modified to import data from that
> > sheet also using a diffrent button?

Reply to this Message

Jeanette Cunningham - 20 Feb 2008 20:01 GMT

Oops!
try it this way
"\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet" &
Me.txtFileDate

Jeanette Cunningham

> Using this code, it thinks the file name is:
> "\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet_Me.txtFileDate"
[quoted text clipped - 71 lines]
>> > that
>> > sheet also using a diffrent button?

Reply to this Message

Ron2006 - 25 Feb 2008 19:48 GMT

> Oops!
> try it this way
[quoted text clipped - 80 lines]
>
> - Show quoted text -

per the last example, you will need another _ after sheet"
before the "

Ron

Reply to this Message