Search and open feature
 

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

Search and open feature
 

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" wrote in
message ...

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

Search and open feature
 

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

--
TomB
-----------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...fo&userid=3611
View this thread: http://www.excelforum.com/showthread.php?threadid=55923

Search and open feature
 

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

You have no "End Sub" for Macro1.

NickHK

"TomBP" wrote in
message ...

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

Search and open feature
 

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

Search and open feature
 

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" wrote in
message ...

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

Search and open feature
 

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

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

*End sub*


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