Saving and searching Macro

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.

\1

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.

\1

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.

\1

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

Please Login or Register  to view this content.

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.

\1

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

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
>
>