Dear Expert,
Say B4 cells contains a text C:\marco\main\help\income2009.xls
How is the VB script so that once I run the marco it will open the file in
accordance to the text in cell please ?
In future, I change the text in B4 to C:\marco\main\help\expense2009.xls
That marco can help me open that file too.
It is not a fixed file name and thus fixed VB script.
File name (to be opened) in cell B4 will be changed from time to time.
Thanks

Try something like

Dim S As String
S = Range("B4").Text
If S < vbNullString Then
If Dir(S) < vbNullString Then
Workbook.Open S
Else
Debug.Print "File '" & S & "' does not exist.
End If
Else
Debug.Print "B4 is blank"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 26 Apr 2009 06:13:01 -0700, Elton Law
wrote:

Dear Expert,
Say B4 cells contains a text C:\marco\main\help\income2009.xls
How is the VB script so that once I run the marco it will open the file in
accordance to the text in cell please ?
In future, I change the text in B4 to C:\marco\main\help\expense2009.xls
That marco can help me open that file too.
It is not a fixed file name and thus fixed VB script.
File name (to be opened) in cell B4 will be changed from time to time.
Thanks

Hi,

Try this

Workbooks.Open Filename:=Sheets("Sheet1").Range("B4").Value

Mike

"Elton Law" wrote:

Dear Expert,
Say B4 cells contains a text C:\marco\main\help\income2009.xls
How is the VB script so that once I run the marco it will open the file in
accordance to the text in cell please ?
In future, I change the text in B4 to C:\marco\main\help\expense2009.xls
That marco can help me open that file too.
It is not a fixed file name and thus fixed VB script.
File name (to be opened) in cell B4 will be changed from time to time.
Thanks

I have tested ....
Both of you are very strong .... Great ...
Thanks so much !!!

"Chip Pearson" wrote:


Try something like

Dim S As String
S = Range("B4").Text
If S < vbNullString Then
If Dir(S) < vbNullString Then
Workbook.Open S
Else
Debug.Print "File '" & S & "' does not exist.
End If
Else
Debug.Print "B4 is blank"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 26 Apr 2009 06:13:01 -0700, Elton Law
wrote:

Dear Expert,
Say B4 cells contains a text C:\marco\main\help\income2009.xls
How is the VB script so that once I run the marco it will open the file in
accordance to the text in cell please ?
In future, I change the text in B4 to C:\marco\main\help\expense2009.xls
That marco can help me open that file too.
It is not a fixed file name and thus fixed VB script.
File name (to be opened) in cell B4 will be changed from time to time.
Thanks