+ Reply to Thread
Page 7 of 9 FirstFirst ... 5 6 7 8 9 LastLast
Results 91 to 105 of 128

auto save to a file via macro or program?

  1. #91
    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,915
    Quote Originally Posted by stevesunfold
    richard

    bingo!!!!

    im back at work and everything is perfect and runs like a dream

    once again thanks for everything
    That's excellent. That is good news Steve. All the best. Richard

  2. #92
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi richard

    is there anyway you can explain to me exactly how this code works as im trying to use it for other spreadsheets that get saved in specific locations

    ive tried tinkering with it but i keep getting error codes whatever i do

    maybe if i understood it more i could use it

    im trying to get a sheet to be saved to a folder which is on my server

    destination is server\job_file

    then in that folder is a folder called glass returns

    and i want it to save as glass return number ( whatever is in cell d8 )

    kind regards

  3. #93
    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,915
    Quote Originally Posted by stevesunfold
    hi richard

    is there anyway you can explain to me exactly how this code works as im trying to use it for other spreadsheets that get saved in specific locations

    ive tried tinkering with it but i keep getting error codes whatever i do
    Hi Steve,

    Just to recap the code is I believe the following:

    Please Login or Register  to view this content.
    It's reasonably straightforward so here goes.

    The first line Dim stFileName.....declares a Visual Basic variable which we choose to call 'stFileName'. We also choose to tell VB that it's a variable that contains strings of characters, or if you like 'text' as opposed to numeric values, integers, decimals or other variable types.

    In the next line we tell VB what the value of the variable is. In this case the value is the result of adding together a series of strings. All the strings are in cells on sheet1, hence you see references to 'Sheet1' and 'Range'.

    So the first cell we see is D43, which is the cell that contains the text showing the path to your folder(s). In your home test environment it was probably "C:", at work I guess you've got "\Server" or something. (I don't really understand why your system didn't have a drive letter like E: or F: etc), but whatever you had seemed to work.

    So at the moment the variable has in it the value of D43, let's say "C:"
    The next bit of code on the line says add a backslash. i.e. &"\" , so the string value so far is "C:\"

    The next bit of the code refers to cell D8 which contains the Job number - which may include a suffix 'a', 'b' etc. Let's say it's J2345a. However the folder we're after is just the 5 character Job number itself, so we use the 'Left' instruction to slice the bit of cell D8 we want. The structure of the 'Left' instruction is LEFT(range value, number of characters). In this case we want the leftmost 5 characters so we have: Left(Sheet1.Range("d8"), 5). which results in the string, "J2345". We want to append this to what we've already built so we preface this with another slash, and so far we have:
    "C:\J2345"

    The next bit we add is the little "-' character, and since there are spaces either side of it we have &" - ". So now we have
    "C:\2345 - "

    Cell I9 contains the name of the client so we append that with
    & Sheet1.Range("i9"), and if I9 contained the word "Tesco" so far wed have
    "C:\2345 - Tesco"

    We now add "\Docs\" & "Glass Reject Order And Error Report Form ", which means we have in total so far:

    "C:\2345 - Tesco\Docs\Glass Reject Order And Error Report Form"

    and finally on this line since you want the full job number with any suffixes we append whatever's in D8, this time without slicing the first 5 characters, and the text ".xls" So now the complete value of the stFileName variable is

    "C:\2345 - Tesco\Docs\Glass Reject Order And Error Report Form J2345a.xls"

    The last line of code "ActiveWorkbook.SaveAs Filename:=stFileName" simply says take the Active Workbook, i.e. in this case the one you're working with, and instructs VBA to save it in the path and with the filename that is stored in the stFileName variable - see above.

    HTH

  4. #94
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard

    pure quality

    simple explanation is what i needed and got it to work first time

    i simply cannot thank you enough for your time and effort

    cheers mate

  5. #95
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    Hi Richard

    Just To Take This One Last Step Further I Want This To Do As Its Done But If I Put Stock Into The Job Number Box I Want It To Put It Straight Into A Folder Called Stock Purchase Orders

    Is This Possible Mate?

  6. #96
    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,915
    Morning Steve,

    Yes this is straightforward, but we'll need to know how the macro can recognise that the number you enter is a Stock number rather than a Job Number.

    For instance do all Job numbers begin with a 'J' and stock numbers an 'S'?
    Alternatively can we say if the Job number doesn't begin with a 'J' it is therefore a Stock number?

    Rgds

  7. #97
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    Yes Richard The Latter Is Fine

    If Not

    All Jobs Begin With A J Number And A Stock Is As It Says Stock

  8. #98
    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,915
    Quote Originally Posted by stevesunfold
    Yes Richard The Latter Is Fine

    If Not

    All Jobs Begin With A J Number And A Stock Is As It Says Stock
    Hi Steve,

    I should have asked if this second folder was in place of the original folder called 'Docs'. In the code below I have assumed that's the case.

    You didn't mention what the file should be called. I haven't changed the name "Glass Reject Order And Error Report Form" in the code, so that's what it will be called in the Stock Purchase Orders folder. You may want it called something different, if so post back.




    Please Login or Register  to view this content.

  9. #99
    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,915
    Sorry Steve, there was a rogue double quotes in the code I've just sent at the start of the Else "stfolder = "\Stock Purchase Orders\"

    It should be


    Please Login or Register  to view this content.
    [/QUOTE]

  10. #100
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    Hi Richard

    No Thats The Problem

    If Its A Stock Job Rather Than A J Number It Goes Somewhere Completely Different

    \\server\master Documents\warehouse Purchase Orders For Stock

  11. #101
    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,915
    Quote Originally Posted by stevesunfold
    Hi Richard

    No Thats The Problem

    If Its A Stock Job Rather Than A J Number It Goes Somewhere Completely Different

    \\server\master Documents\warehouse Purchase Orders For Stock
    Steve, just to confirm, is the text 'warehouse Purchase Orders For Stock' a folder name or the name of the file? If it's a folder name, what do you want to call the file?

    i.e. for a job we have
    \\server\jobnumber - clientname\Docs\name of file
    where name of file = "Glass Reject Order And Error Report Form jobnumber.xls

    what's the equivalent for a stock item? Where does the job number fit into the naming convention, it's not clear from the above? Can you spell this out in detail, and the name of the file you want to end up with so that I can modify the code.

    Rgds

  12. #102
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    ah sorry richard

    it will be saved as a purchase order form ( not the glass one )

    the company name is in b16

    i was hoping it would look something like

    b16 (company name) purchase order then the purchase order number

    so for example

    tesco purchase order w0123.xls

    this would then save in the job folder on the server if it is for a job ie j2345 which weve done before

    which is determined by cell g23

    g23 will tell it which job file to go to or if it says stock to go to \\server\master Documents\warehouse Purchase Orders For Stock

    thanks mate

  13. #103
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    ps in cell g23 will be either a job number or it will say stock

    in e23 is either the customer name or consumables if it says stock in g23

  14. #104
    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,915
    Steve,

    I'm attaching a small file which I want to use to define the paths and names for your two choices.

    Would you review this and where necessary change the stuff in the yellow boxes. Make sure that the green cells accurately reflect exactly what you want to see for both the paths/folders and file names. These will change automatically if you change any of the yellow cells.

    Then if you've made changes, upload it back to the forum.

    Rgds
    Attached Files Attached Files

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

+ Reply to Thread
Page 7 of 9 FirstFirst ... 5 6 7 8 9 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