Saving and searching Macro

assume the number is in F7 of the activesheet

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("F7").Text
s1 = "C:\Documents and Settings\" & _
"morgand\Desktop\Shipments\"
ActiveWorkbook.SaveAs _
Filename:=s1 & s & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub


for the "search", assume the number is in C3 of the Activesheet

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

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

--
Regards,
Tom Ogilvy

"TomBP" wrote:

>
> First of all. Hi everyone
>
> I'm fairly new to the world of excel and know little to nothing about
> using macro's. Now I have an idea which can save me a lot of work and I
> think this is possible by using a macro.
>
> Let me explain... I took some picture screens to make it easier to
> understand what I want. The IMG tags doesn't seem to work on this forum
> so you'll have to click.
>
> Here you see a standard file which I open from my desktop.
>
> [image: http://i54.photobucket.com/albums/g1...BP/Pic1-3.jpg]
>
> In the next picture you see words/numbers in italic. I typ these over
> from papers which people fax to me. The shipment number which I marked
> in yellow is unique.
>
> [image: http://i54.photobucket.com/albums/g1...BP/Pic2-2.jpg]
>
> Now what I want to achieve is the following. I want to have a shortcut
> key which saves the document in a map called Shipments as the unique
> shipment number used in the document. So the document name in this
> case is 118526.xls .
> It will look something like this.
>
> [image: http://i54.photobucket.com/albums/g1...BP/Pic3-1.jpg]
>
> I already tried to fix a macro myself but it always comes up with the
> same doc name in the map Shipments. So it always saves as 118526.xls.
>
> Here you can see the code
>
>
> Code:
> --------------------
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 4/07/2006 by BPPassPort User
> '
> ' Keyboard Shortcut: Ctrl+t
> '
> ChDir "C:\Documents and Settings\morgand\Desktop\Shipments"
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\morgand\Desktop\Shipments\1128785.xls", FileFormat _
> :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
> False, CreateBackup:=False
> End Sub
> --------------------
>
>
> I think I need to adjust the numbers in bold to a certain cell in which
> the shipment number is present.
>
> "C:\Documents and Settings\morgand\Desktop\Shipments\*1128785*.xls"
>
> Extra:
>
> I thought of an extra feature aswell. The reason I save these documents
> is because I need to adjust them later on the day. Now is it possible to
> have a macro search for a document number and then open it.
> This is what I think it should look like.
>
> [image: http://i54.photobucket.com/albums/g1...BP/Pic4-1.jpg]
>
> If anyone can help me with this it would be very appreciated. Keep in
> mind tho that my knowledge on visual basic isn't that great.
>
> Thx in advance
>
>
> --
> TomBP
> ------------------------------------------------------------------------
> TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
> View this thread: http://www.excelforum.com/showthread...hreadid=558858
>
>

running the macro on an exsiting normal folder worked fine for me:

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

I am not sure why you would want a subdirectory/folder on the desktop - but
perhaps that is the source of the problem.

--
Regards,
Tom Ogilvy


"TomBP" wrote:

>
> Tom Ogilvy Wrote:
> > assume the number is in F7 of the activesheet
> >
> > 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("F7").Text
> > s1 = "C:\Documents and Settings\" & _
> > "morgand\Desktop\Shipments\"
> > ActiveWorkbook.SaveAs _
> > Filename:=s1 & s & ".xls", _
> > FileFormat:=xlNormal, _
> > Password:="", _
> > WriteResPassword:="", _
> > ReadOnlyRecommended:=False, _
> > CreateBackup:=False
> > End Sub
> >
>
> I tried this for an other example and got the following error:
>
> [image: http://i54.photobucket.com/albums/g1...celreply1.jpg]
>
> When I press Debug it shows this:
>
> [image: http://i54.photobucket.com/albums/g1...celreply2.jpg]
>
> Am I doing something wrong or is the code not right? Keep in mind that
> my knowledge of macro's is not that good
>
>
> --
> TomBP
> ------------------------------------------------------------------------
> TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
> View this thread: http://www.excelforum.com/showthread...hreadid=558858
>
>