Search and open feature

[TomBP]

The pictures speak for themselves:

[image: http://i54.photobucket.com/albums/g115/TomBP/Pic1-4.jpg]

[image: http://i54.photobucket.com/albums/g115/TomBP/Pic2-3.jpg]

I already tried to fix this using the following macro but no success


Code:
--------------------
Sub OpenWorkbook()
Dim s1 as String, bk as Workbook, bk1 as Workbook
s1 = "C:\Documents and Settings\" & _
"fackt0\Desktop\Shipments\"

set bk = activeWorkbook
if dir(s1 & Range("C3").Text & ".xls") <> "" then
set bk1 = Workbooks.Open(s1 & Range("J5").Text & ".xls")
bk.Activate
else
msgbox "workbook not found"
End if
End sub

 

[NickHK]

Tom,
Well which error are you getting ?
Also what is the value of Range("C3").Text, as there is no obvious value
from your jpg.

NickHK

 

[TomBP]

Nick

It gives the following error

[image: http://i54.photobucket.com/albums/g115/TomBP/Error.jpg]

The C3 range was wrong. It should be J5. I adjusted this but tha
doesn't seem to do the trick

 

[NickHK]

Tom,
Look at the code:
Sub Macro1()
'Comments.....
Sub OpenWorkbook()

You have no "End Sub" for Macro1.

NickHK

 

[TomBP]

This is another macro I use for another sheet. This works fine and I see
no difference in standard lines with the macro which isn't working


Code:
--------------------
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/07/2006 by BPPassPort User
'
' Keyboard Shortcut: Ctrl+t
'
Dim s As String, s1 As String
s = ActiveSheet.Range("F10").Text
s1 = "C:\Documents and Settings\" & _
"fackt0\Desktop\Excel probleem\Shipments\"
ActiveWorkbook.SaveAs _
Filename:=s1 & s & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
--------------------



This is the macro which isn't working again.


Code:
--------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/07/2006 by BPPassPort User
'
' Keyboard Shortcut: Ctrl+t
'

Sub OpenWorkbook()
Dim s1 as String, bk as Workbook, bk1 as Workbook
s1 = "C:\Documents and Settings\" & _
"fackt0\Desktop\Shipments\"

set bk = activeWorkbook
if dir(s1 & Range("C3").Text & ".xls") <> "" then
set bk1 = Workbooks.Open(s1 & Range("J5").Text & ".xls")
bk.Activate
else
msgbox "workbook not found"
End if
End sub

 

[NickHK]

Tom,
Yes, look:

Sub Macro1() <<<<<<<<< 1 sub
'
' Macro1 Macro
' Macro recorded 4/07/2006 by BPPassPort User
'
' Keyboard Shortcut: Ctrl+t
'

Sub OpenWorkbook() <<<<<<<<< 2 sub

NickHK

 

[TomBP]

First of all thx for your help. I think you mean this will do the trick