+ Reply to Thread
Page 1 of 9 1 2 3 ... LastLast
Results 1 to 15 of 128

auto save to a file via macro or program?

  1. #1
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690

    auto save to a file via macro or program?

    Hi Guys

    Is It Possible To Auto Save To A File Via A Macro?

    What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.
    The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number
    Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In

    Regards

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,930
    Quote Originally Posted by stevesunfold
    Hi Guys

    Is It Possible To Auto Save To A File Via A Macro?

    What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.
    The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number
    Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In

    Regards
    Hi, Something like the following should do it. Keep the text string with the path to the relevant folder in cell A1. e.g.

    C:\Desktop

    Please Login or Register  to view this content.
    HTH

  3. #3
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    thanks for the reply richard

    in cell d8 is a job number for example j4567 when i save it i want it to go autmatically into;

    and this is how i find the path : desktop , job file folder , then the value in cell d8 will locate that folder then open that folder then store it in a sub folder by the name of docs

    i know its a long shot but is it possible?

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,930
    Quote Originally Posted by stevesunfold
    thanks for the reply richard

    in cell d8 is a job number for example j4567 when i save it i want it to go autmatically into;

    and this is how i find the path : desktop , job file folder , then the value in cell d8 will locate that folder then open that folder then store it in a sub folder by the name of docs

    i know its a long shot but is it possible?
    Hi,

    Oops, sorry. I use an Apple Mac and the standard path separator is the ":" symbol rather than the "\". I forgot to edit the code I posted. Just substitute "\" for ":"

    A1, or another cell if you like, contains the path to the level above the Job File Folder.

    D8 contains the name of the Job File folder as you specified.

    The macro creates a path based on these two cells, then extends the file path by adding the folder \docs and then the name of the activeworkbook

    HTH and sorry for the confusion

  5. #5
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi richard appreciate your time on this but i have to admit to being a bit of a novice

    dont suppose you can explein in simpletons terms

    please

  6. #6
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    would you please tell me exactly what i need to put in a1

    then if youd be so kind as to tell me how to run the macro

  7. #7
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    im getting error 400 return

  8. #8
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    sorry if i have annoyed you richard but im just not 100% on what you are saying

    i appreciate your help

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,930
    Quote Originally Posted by stevesunfold
    would you please tell me exactly what i need to put in a1

    then if youd be so kind as to tell me how to run the macro
    Hi,

    You need to enter text which represents the path to the folder in which your job folder is contained.

    So for instance if the full path to your eventual workbook file name is:

    C:\Documents\J 12345\Docs\your workbook file name.xls

    Then in A1 enter the text

    C:\Documents

    You enter the following code in the Visual Basic Environment (VBE)

    1. Hold down the ALT key and press F11.
    2. On the left hand side you should see a window called Project Explorer, or perhaps just Projects. (I know the description is different to my Mac but can't remember precisely). Find the name of your workbook in this window and double click it.
    3. A code window should open on the right. Now copy and paste the code below into that window.
    4. Close the VBE with ALT - F11 again.

    Now in Excel go to Tools --> Macro --> Macros, find the 'SaveJobFile' macro and run it.

    You could also put the same code in the Worbook_Save event in that large code window you've just used. Click the left hand drop down box and choose the Workbook object, and in the right hand drop down box choose the 'Before_Save event, and paste the code inside that event procedure. Obviously you don't need the Sub..End Sub lines in the code below because the Before Save event has already created those similar lines.

    This means that whenever you save the workbook it will end up in the Docs folder underneath the Job folder.




    Please Login or Register  to view this content.
    HTH

  10. #10
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    cheers richard but im still getting a 400 error

  11. #11

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,930
    Quote Originally Posted by stevesunfold
    cheers richard but im still getting a 400 error
    Hi Steve,

    I've corrected the workbook and am attaching it here.

    A few things.

    1. The Job Number was not entered in D8, which you mentioned was the case in your original posting, so I've added it.

    2. I didn't realise there were two sheets. I assume you mean D8 on the sheet called 'Glass Sheet' is the one that contains the Job number. Hence I've modified the code since if this sheet wasn't the active sheet when you ran the macro, it would fall over. I've included the sheet name in the macro.

    3. You hadn't entered the path to the folder which contains the Job folder. To test this I've used my Mac path in cell A1, which is obviously different to yours. I've put what I presume is the path you use in A3. You'll need to correct that if necessary and copy and paste it to A1 overwriting my path.

    4. I've taken the code out of the Worksheet object and put it in Module1. Just my way of doing things generally. It would have run where it was. You can manually Run it from here as described before. Tools Macro Macros etc...

    5. I've also put the same code in the Workbook_Save event so that it will save automatically in the designated folder when you press the Save icon.
    If by any chance the macro falls over when you save the book because of some other problem, just remove the code from the Workbook_Save event - (see previous posting on how to navigate there), until you've got it to work by manually running the macro.

    HTH
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    think im going to give up richard

    not that i want to but it just doesnt work on my machine

  14. #14
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    probably doing something really basic wrong

  15. #15
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    this is the code i have

    Sub SaveJobFile()
    Dim stFileName As String
    stFileName = Range("A3") & "\" & Range("e8") & "\Docs:" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=stFileName
    End Sub

    a3 is where i have put the address to save

    and its actually e8 that has the j4567 number in

    is there something else i should have changed

+ Reply to Thread
Page 1 of 9 1 2 3 ... LastLast

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