Saving and searching Macro

  • Thread starter Thread starter TomBP
  • Start date Start date
T

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
 
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 said:
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
Click to expand...
 
Tom said:
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
Click to expand...

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 :)
 
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 said:
Tom said:
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
Click to expand...

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 :)
Click to expand...
 
Back
Top