Open Excel Sheet By button / text box data.

S

Sydious

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?
 
J

Jeanette Cunningham

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
 
S

Sydious

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.

Jeanette Cunningham said:
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

Sydious said:
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?
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

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

Jeanette Cunningham


Sydious said:
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.

Jeanette Cunningham said:
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

Sydious said:
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?
Click to expand...
Click to expand...
Click to expand...
 
R

Ron2006

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"
Click to expand...
Its not taking the value of txtFileDate and adding it to the the
\\broan-nutone.com\broan\Logistics\shiftmetrics\Performance\Test_Sheet_
file
name.
Click to expand...
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
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?- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

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

Ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data from sheet, through userform to text box on the sheet. 4
Issue passing data from sheet into userform 1
Problem in automating using a button created by recording a macro 0

Top