Saving and searching Macro

[TomBP]

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/g115/TomBP/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/g115/TomBP/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/g115/TomBP/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/g115/TomBP/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

 

[Tom Ogilvy]

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

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/g115/TomBP/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/g115/TomBP/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/g115/TomBP/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/g115/TomBP/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]

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/g115/TomBP/excelreply1.jpg]

When I press Debug it shows this:

[image: http://i54.photobucket.com/albums/g115/TomBP/excelreply2.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

 

[Tom Ogilvy]

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

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/g115/TomBP/excelreply1.jpg]

When I press Debug it shows this:

[image: http://i54.photobucket.com/albums/g115/TomBP/excelreply2.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