+ Reply to Thread
Results 1 to 4 of 4

Saving and searching Macro

  1. #1
    Forum Contributor
    Join Date
    07-06-2006
    Posts
    108

    Saving and searching Macro


  2. #2
    Tom Ogilvy
    Guest

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


  3. #3
    Forum Contributor
    Join Date
    07-06-2006
    Posts
    108
    Thx a lot. The saving part is already working. The searching part is for tomorrow. If that works too I owe you big time
    Last edited by TomBP; 07-06-2006 at 09:46 AM.

  4. #4
    Tom Ogilvy
    Guest

    Re: Saving and searching Macro

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1