PDA

View Full Version : Open a workbook that refering to a cell



Shazam
02-08-2006, 10:01 PM
Hi everyone,

Well I'm trying to make this code to work. What I'm trying to do is when the user change the file name Test.xls in cell A2 it will open that workbook. Now when the user change the file name in cell A2 again say with the file name uptime.xls then the code will close test.xls and then open uptime.xls. Can it be modified to do that?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target.Text <> "" Then

With Application.FileSearch
.NewSearch
.LookIn = "C:\" & [A2] & ".XLS"

.Filename = ""
If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
Workbooks.OpenText .FoundFiles(1), xlWindows

End If
End With
End Sub

CCkfm2000
02-08-2006, 10:43 PM
hi shazam,

i'm not good with vba but try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Text <> "" Then

If Target.Address = "$A$2" Then
With Application.FileSearch
.NewSearch
.LookIn = "c:\"
.FileName = [a2] & ".XLS"

If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
Workbooks.OpenText .FoundFiles(1), xlWindows
End If
End With
End If
End If

End Sub


hope this works for you it did for me...

regards

cckfm2000 :think: