Search and open feature

[TomBP]

The pictures speak for themselves:

\1

\1

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

Please Login or Register  to view this content.

Anyone with an idea what might be wrong?

[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" <[email protected]> wrote in
message news:[email protected]...
>
> The pictures speak for themselves:
>
> [image: http://i54.photobucket.com/albums/g1...BP/Pic1-4.jpg]
>
> [image: http://i54.photobucket.com/albums/g1...BP/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
> --------------------
>
>
> Anyone with an idea what might be wrong?
>
>
> --
> TomBP
> ------------------------------------------------------------------------
> TomBP's Profile:
http://www.excelforum.com/member.php...o&userid=36112
> View this thread: http://www.excelforum.com/showthread...hreadid=559237
>

[TomBP]

Nick

It gives the following error

\1

The C3 range was wrong. It should be J5. I adjusted this but that 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 that
> doesn't seem to do the trick.
>
>
> --
> TomBP
> ------------------------------------------------------------------------
> TomBP's Profile:
http://www.excelforum.com/member.php...o&userid=36112
> View this thread: http://www.excelforum.com/showthread...hreadid=559237
>

[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

Please Login or Register  to view this content.

This is the macro which isn't working again.

Please Login or Register  to view this content.

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> --------------------
>
>
> --
> TomBP
> ------------------------------------------------------------------------
> TomBP's Profile:
http://www.excelforum.com/member.php...o&userid=36112
> View this thread: http://www.excelforum.com/showthread...hreadid=559237
>

[TomBP]

Thanks a lot for your help. It works like a charm now.