+ Reply to Thread
Results 1 to 128 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
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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

  16. #16
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    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

    Hi Steve,

    There's a redundant ":" colon in the code above.

    Please also note the comment in my last post that since I was unaware that the workbook comprised two sheets, the macro needed altering to cater for the name of the sheet that contains the Job number in E8, and the path to the job folder, now in E8.

    Delete what you have at the moment in Module1 and replace it with:

    Please Login or Register  to view this content.
    I can't see why this won't work assuming you've entered the correct path in A3, and have a Job folder exactly as describe by cell E8.

    Rgds

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

    thanks for coming back to me
    i dont want to give up i want to get it working and its obvious that you do too so i thank you for your effort

    here is what im getting

    http://www.savefile.com/files/1499731

  18. #18
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    maybe i have the wrong destination in my cell e43

    i have

    C:\documents and settings\my name\desktop\job folder

    my name is obviously my name

  19. #19
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    if i change it to C:\desktop\job folder

    i get a 400 error

  20. #20
    Registered User
    Join Date
    05-02-2006
    Posts
    80

    Don't give up!

    Hi Steve . Richard,

    Are you guys still working on this?
    I've gone through the thread and as Richard says the code should work.

    What I'm wondering is if we fully appreciate where exactly you want this document saved:

    At the moment the code is trying to save it here (I'm using J1234 as a test job number):

    "..DESKTOP\J1234\Docs\GLASS_FORM.xls"

    However from your first post I'm wondering if you actually want it to go here:
    "..DESKTOP\Job Folder\J1234\Docs\GLASS_FORM.xls"

    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.
    Another thing I wonder is the filepath for your desktop as this will be different from user to user. If you're just using "C:\Desktop" then that's probably not going to work.

    To resolve this can I suggest Steve that you go into My Computer and navigate to one of these files that you've previously saved and copy the address on a bit of paper (or cut and paste if you prefer) - then post it on here.

    We can then tailor the macro so it's aimed exactly where you want it to go.

    Don't give it up - the satisfaction you'll get when this is worked out and you're using it again and again without problems is a lovely one.

  21. #21
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi richard

    thanks for coming back to me
    i dont want to give up i want to get it working and its obvious that you do too so i thank you for your effort

    here is what im getting

    http://www.savefile.com/files/1499731

    Steve,

    I see that the stFileName line contains a reference to the cell E43. Is this where you are holding the name of the path to the Job folder? Last time I think you said that the text of the path was held in cell A3

    The error message is telling you that the folder doesn't exist, either because there is something wrong with the path, or the Job Folder name does not exist in the folder immediately below the Desktop level, or there is not a folder called 'Docs' in the Job Folder. Can you confirm that you have a complete path :

    C:\Desktop\J2663 - Parry\Docs

    If you have, run the macro and when it halts it should stop with the code which is causing the error highlighted in yellow. Presumably this is the line that begins
    stFileName = ....

    If you move the cursor and hover over the text 'stFileName' a little message box should pop up. This will tell you the path and name Excel is trying to use but which is wrong. Note what it says and double check that all components exist. i.e. the Job Name shown is in A3/E43 and there is a folder called 'Docs'. Correct any that are wrong.

    If this fails to track it down let's take it step by step. Temporarily create a folder in the C:\ root directory called say "Job123" no spaces. Now in this folder create a folder called 'Docs'

    You should therefore have a path

    C:\Desktop\Job123\Docs

    Now put the text "Job123" in cell A3, or E43 if that's what you're using and run the macro.

    If it still fails, send the whole workbook again and I'll dig out an old PC I have somewhere, and test the file out in a Windows Excel Environment.

    Rgds

  22. #22
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi guys
    thanks for your help

    i am at work now so its possible to tell you exactly how i get to where i want it to go
    heres how i get to it manually

    click save as then click desktop then i click job_file on server ( which when i hover over the folder it says this \\server\job_file ) i then need it to locate the specific job file by reading cell d8 on the glass sheet form and then referring it to a the same folder in job_file server

    ie j2345 would then locate j2345 and then input it in another sub folder within j2345 under a sub folder by the name of DOCS.

    I must point out to you that the folders on the job_file server all start with a j then followed by a 4 number reference

    the folders then have a space hyphen space then customers name which can be found in cell i9 if nexcessary

    any questions feel free to ask and i am so thankfull for all your help

  23. #23
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    e43 is where im storing the destination

  24. #24
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    THIS IS THE DESTINATION I THINK YOU WERE AFTER TRISTAN/RICHARD

    \\server\job_file

  25. #25
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    THIS IS THE DESTINATION I THINK YOU WERE AFTER TRISTAN/RICHARD

    \\server\job_file
    Hi Steve. Tristan

    If I've kept up with this, you now have,

    Please Login or Register  to view this content.
    And the prodecure still contains

    Please Login or Register  to view this content.
    So assuming the workbook you're trying to save is called Glass_Form.xls, the stFileName variable should result in the following.

    \\server\job_file\J2345\Docs\Glass_File.xls

    and the workbook should be saved with that path.

    Rgds

  26. #26
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    im finding this so frustrating
    im back home now and trying it with dummy folders and i just cant get it to work all i keep getting is 400 error

  27. #27
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard , tristan

    success at last found out what i was doing wrong

    everything works as it should if the job folder is exactly the same as cell e8 but the job folders are always with the customer name and that is where the errors occur.

    i have had it working with just the j2345 and the job folder being just j2345 but if as it is at work the job folders are named like this

    j2345 - (surname)

    or as an example j 2345 - smith

    so it cannot find the folder as it is at the moment because of the customer name

    but surely this can be altered as this is actually in cell j9

    so then maybe it will look at both?

    also while im at it is there anyway there could be a keyboard shortcut to run this macro as i find tools>macro>macros>selecting macro and run very tedious

    cheers guys you are stars

  28. #28
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    TRIED TO MAKE THE CODE THIS BUT IT DOESNT WORK

    Sub SaveJobFile()
    Dim stFileName As String
    stFileName = Sheet1.Range("e43") & "\" & Sheet1.Range("E8") & " - " & ("J9") & Sheet1.Range("E8") & "\Docs\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=stFileName
    End Sub

  29. #29
    Registered User
    Join Date
    05-02-2006
    Posts
    80
    Hi again,

    Please Login or Register  to view this content.
    By my reckoning that will produce something like:

    \\server\jobfile\J2345 - SmithJ2345\Docs\GlassSht.xls

    You've got Sheet1.Range("E8") repeated in the above code. Try replacing it with this:

    Please Login or Register  to view this content.
    Good luck

    T

  30. #30
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    TRIED TO MAKE THE CODE THIS BUT IT DOESNT WORK

    Sub SaveJobFile()
    Dim stFileName As String
    stFileName = Sheet1.Range("e43") & "\" & Sheet1.Range("E8") & " - " & ("J9") & Sheet1.Range("E8") & "\Docs\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=stFileName
    End Sub

    Steve,

    I see you've added another cell J9 with the cutomer name. No problems with that, but you've only put

    & ("J9")

    the correct syntax should be

    & Sheet1.Range("J9")

    Having added J9 why have you got two reference to 'Range("E8")'? Are there really two folders with the name of the Job in the full path, or is this just a typo?

    As to you last question, yes of course you can have a keyboard short cut, or better still a 'click' button. What I generally do is pick a nice icon that means something for you, a logo or somesuch, but any sort of object will do, even just a drawing rectangle. When you've got an object on the sheet, just right click on it, choose the 'Assign Macro' and pick the 'SaveJobName' macro. It's here that you can also assign a shortcut key.

    If you're still having probs. attach the file here so that we can take a look, although it sounds from the previous post that you had it working before you added the J9 bit. Don't forget to check that double use of the E8 cell.

    Rgds

  31. #31
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi tristan
    that code is returning the 400 error again

  32. #32
    Registered User
    Join Date
    05-02-2006
    Posts
    80
    Check Rich's last post - I didn't spot the "J9" error.

  33. #33
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    OK well i have to be honest and think that we ( sorry you guys ) have almost cracked it
    this is where i am right now

    Sub SaveJobFile()
    Dim stFileName As String
    stFileName = Sheet1.Range("e43") & "\" & Sheet1.Range("E8") & "-" & Sheet1.Range
    ("J9") & "\Docs\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=stFileName
    End Sub

    and its returning a microsfot visual basic error

    which says compile error and argument not optional i then click ok and the top line (Sub SaveJobFile()) is highlighted in yellow


    thank you so so much for your patience and understanding but i know we are so so close

  34. #34
    Registered User
    Join Date
    05-02-2006
    Posts
    80
    ....Don't forget to wrap code tags around your code. Use the # button in the posting dialogue box.....

    Where you've written....

    Please Login or Register  to view this content.
    Is that actually how it looks in the code or have you split it over several lines for easy viewing in the post.

    If that's how it looks in the code then you need to add " _" at the end of the line. This tells the compiler that you intend the split lines to be read as one.

    Try replacing with the code below:

    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi guys

    here is my spreadsheet
    http://www.savefile.com/files/1505464

    cell d43 is where i shall be storing the destination

    cell d8 is the job number

    and cell I9 is the customer name

    if i hover over the job folder on the desktop it says \\server\job_file

    or if i do manually i click file>save as>desktop>job_file on server

    all these folders in this job file folder have a job number which is j followed by 4 digits a space hyphen space and then customer name

    to which inside this folder is the docs folder

    i know we are so close its just so frustrating

  36. #36
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi guys

    here is my spreadsheet
    http://www.savefile.com/files/1505464

    cell d43 is where i shall be storing the destination

    cell d8 is the job number

    and cell I9 is the customer name

    if i hover over the job folder on the desktop it says \\server\job_file

    or if i do manually i click file>save as>desktop>job_file on server

    all these folders in this job file folder have a job number which is j followed by 4 digits a space hyphen space and then customer name

    to which inside this folder is the docs folder

    i know we are so close its just so frustrating
    Steve,

    What's the question, and what error is the macro failing with?

    On the file you refer to in your last post, cells D43, D8 and I9 have nothing in them. You also appear to have changed your mind on which cells to use since originally we were discussing E43, D8 and J9. There's no reason why you shouldn't change the cells, but if you do change, you do need to change the macro too. The macro in the file is still using E43

    I also not that in the file you refer to you haven't incorporated Tristan's reference to the client in I9 (or J9 as was).

    I feel if you rewind a step, to Tristan's last example and ensure the macro reflects this, once you enter the values in the three cells the whole thing will work. I seem to remember you saying a few posts ago that you had a simplified version working, which proves the code and principle is correct. You just need to ensure that the values in the three cells exist, and where they produce a path and folder name, that the path and folder actually exist on your system. Be extremely careful with spaces, and if you have a space somewhere in the server path, make sure that the D43/D8 cells in the workbook also incorporate a space.

    Rgds

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

    yes i apologise for the slight cell changes
    it appears my version at home is very slightly different than the one i use at work

    maybe somethings not quite right because we are on a server at work???

  38. #38
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    im sorry guys but i have to admit to giving up on this now even though so close
    i have to be hionest and say im so frustrated as whatever i try comes back with some sort of errro

    ive just tried something else and it comes back with cant execute code in break mode

    i really cant thank you both enough for your time and effort but im at my wits end and think i will stick to the long and laborious way of saving

    good luck gents and thanks for your patience

    steve

  39. #39
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi richard

    yes i apologise for the slight cell changes
    it appears my version at home is very slightly different than the one i use at work

    maybe somethings not quite right because we are on a server at work???
    OK Steve

    I've written applications in the past where people do use different paths & locations. It just means that you have to keep a reference to both in cells in the worksheet, and ensure the macro has IF..Then..tests to identify either the user or location so that the appropriate file name / path can be built.

    ....but all that's for another time when you've got this bit working...


    Rgds

  40. #40
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    im sorry guys but i have to admit to giving up on this now even though so close
    i have to be hionest and say im so frustrated as whatever i try comes back with some sort of errro

    ive just tried something else and it comes back with cant execute code in break mode

    i really cant thank you both enough for your time and effort but im at my wits end and think i will stick to the long and laborious way of saving

    good luck gents and thanks for your patience

    steve
    OK Steve, that's a shame, you're so close.

    Click the little black square on the Visual Basic Menu to clear a VB break, or Ctrl-Shift-F9 in case you've accidentally toggled a break point - which is what you do when you want to step through a macro to check line by line.

    Rgds

  41. #41
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    i just wonder if its because im on a server

  42. #42
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard how can i be certain ive got the right path?

  43. #43
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    i just wonder if its because im on a server

    No, I doubt it. As long as the complete path is in the text string the file should save. I've written many VBA procedures which save files across all sorts of servers/paths and not had any problems.

    Just as a matter of interest, where are you based - at work that is? If you were anywhere reasonably local to me, (and I travel around a fair bit), I'd be happy to pop in and try to sort it.

    Rgds

  44. #44
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    other side of the country from you mate unfortunately im in norfolk but thanks for the gesture most kind

    im sure its nothing to do with the path as the path is \\sever\job_file

    if i put that in excel it comes up as a hyperlink and if i click it , it takes me directly to the file

  45. #45
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    richard how can i be certain ive got the right path?
    Hi Steve

    Immediately before the ActiveWorkbook.SaveAs Filename:=stFileName add the following line of code temporarily.

    Please Login or Register  to view this content.
    This will halt the macro and show the path & filename that the macro is using in a Message Box. Check this character by character to ensure it is complete, and also check that the various paths and folders as per the Message Box do actually exist.

    Rgds

  46. #46
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    would you be kind enough to tell me the exact code to put in please richard

  47. #47
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard sorted it

    its returning this

    \-\Docs\GLASS REJECT PURCHASE ORDER AND ERROR REPORT FORM trial.xls

  48. #48
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    richard sorted it

    its returning this

    \-\Docs\GLASS REJECT PURCHASE ORDER AND ERROR REPORT FORM trial.xls
    Steve,

    Indeed it is. That will be correct until you fill in the three cells D43, D8 and I9 that contain:
    a) the Path
    b) the Job number
    c) the client.

    All three are empty at the moment so the bit of code that says

    Please Login or Register  to view this content.
    is finding nothing in D43, adding a "\" , finding nothing in D8, adding a "-", finding nothing in I9, adding "\Docs\", and finally adding the name of your workbook, viz. "GLASS REJECT PURCHASE ORDER AND ERROR REPORT FORM trial.xls"

    so all that results in the

    \-\Docs\GLASS REJECT PURCHASE ORDER AND ERROR REPORT FORM trial.xls

    string that you are seeing.

    Fill in the cells with the server path and the job name, AND this is important,make sure you actually have the path and folder(s) on your system that you see in the Message Box.

    Rgds

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

    still returning a 400 error even though i know the path is there

  50. #50
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    is it possible to do this without worrying about the name?

    so it just looks for the job number ( folder ) ie j2345????

  51. #51
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    is it possible to do this without worrying about the name?

    so it just looks for the job number ( folder ) ie j2345????
    What name are you referring to?

    Simplifying the path/name structure was a suggestion I made a few postings ago.

    I've just looked at the file you posted this morning and the macro seems to be missing the reference to the client name which I'm sure was there in earlier versions. Again it's not a problem provided you ensure that whatever is contained in the macro actually converts to a recognisable path/folder structure that exists.

    Let's go back to basics and avoid any reference to Job Number, or Client Name and see if we can get this working by saving just the workbook name to the C:\ Root directory of your local PC - not the server.

    So please replace the current macro temporarily with the the following:

    Please Login or Register  to view this content.
    Now in E43 put "C:\" - without the double quotes and run the macro.
    This should save the "Glass_Reject...........xls" file in the C:\ root directory.

    Confirm this is working then we can move to the next stage of introducing the Job number.

    Rgds

  52. #52
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi richard sorry for the delay

    getting home from work

    yes i can indeed confirm that works mate

  53. #53
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi richard sorry for the delay

    getting home from work

    yes i can indeed confirm that works mate
    Excellent,

    Let's move to stage 2. Still with C:\ in E43, create a new folder "J2345" underneath the C:\ path on your PC so that you have:

    C:\J2345

    Add the text "J2345" (without the quotes) in cell D8 of the worksheet.

    Now replace the previous macro with this one, and try running the macro again. You should find the file appears in the J2345 folder.

    Assuming this works we'll then move on to adding the client name.

    Please Login or Register  to view this content.
    Rgds

  54. #54
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    still with you richard and yes it works fine

    one question to ask at this stage

    if the job folder has a capital J and cell d8 had j in would it still function ok or is it case dependant?

    onwards and upwards

    thanks so much

  55. #55
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    still with you richard and yes it works fine

    one question to ask at this stage

    if the job folder has a capital J and cell d8 had j in would it still function ok or is it case dependant?

    onwards and upwards

    thanks so much
    Hello Steve,

    Excellent again!

    No the character case is not significant.

    Let's go for broke

    With the same basics as last time, path in E43, Job in D8, now put the client name in I8. Also on your PC in the J2345 job folder, create a sub folder called "Docs".

    Now replace the macro code with the following.

    Please Login or Register  to view this content.
    In my test system I entered the customer Name 'Parry' in I8. If you run the macro now you should end up with the file in the following path.


    Please Login or Register  to view this content.
    Hopefully you can see what we've been doing with this series of steps, and how to check it. The important thing is to read the relevant line in the code, in this case the stuff after the "stFileName =", and make sure this matches how your actual path and folder structure are ordered

    With a bit of luck you should now also be able to modify the various elements and have the file saved on your work server.

    Let us know how you get on.

    Rgds

  56. #56
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    this is weird richard

    i get to work and do exactly the same steps but using the path \\server\job_file

    run the macro and it says

    \\server\job_fileGLASS REJECT PURCHASE ORDER AND ERROR REPORT FORM trial.xls

    and then it comes up with the 400 error box and i go look in the job_file and its no where to be seen

    bizarre

  57. #57
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    you about richard?

  58. #58
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    this is weird richard

    i get to work and do exactly the same steps but using the path \\server\job_file

    run the macro and it says

    \\server\job_fileGLASS REJECT PURCHASE ORDER AND ERROR REPORT FORM trial.xls

    and then it comes up with the 400 error box and i go look in the job_file and its no where to be seen

    bizarre
    Hello Steve,

    I've been away all day today.
    Why are there double slashes at the start. Is that because you've entered "\\server" in e43, and is \\server a valid path or is it simply \server.

    What is the "jobfile" reference. Is this shorthand for the Job number, J2345 in our previous examples, or do you literally mean that's the text you're seeing in the Macro Message Box.

    Then there seems to be a missing "\" after the job number and no mention of the "docs" folder. Why have they disappeared. Can you confirm that you've not altered the macro in any way, and what are the values you have in your three worksheet cells E43, D8 and I8?

    When you say you did exactly the same steps at work, do you mean you used the server copy of the workbook and made all the same changes to that, that you had made to your PC home version, or do you mean that you have used the copy that you succesfully used at home, on the work server presumably changing just the path in D43?. If not would you try this please. if it works at home on the C:\ root folder system, then provided your server system has the same sort of structure, then there's no reason it shouldn;t work.

    One thing I find odd is that there is no mention of the servers drive letter. Why is that? Don't your server files exist in a path that begins with a letter, e.g. E:\, F:\ etc.?

    Rgds

  59. #59
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi richard
    sorry was out all last last night

    when i hover over the folder it says \\server\job_file

    i will be at work from 8am and take another look then

    the reason there is no job reference number was because i was starting from basics again at work

    the time before was at home

    steve

  60. #60
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard you about?

  61. #61
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    richard you about?
    Steve,

    I am at the moment.

    Cheers!

    R.

  62. #62
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi m8 yeah i checked at work and when i hovver over the file it definetly says \\server\job_file

  63. #63
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi m8 yeah i checked at work and when i hovver over the file it definetly says \\server\job_file
    When you say 'hover over the file' do you mean in the Macro code when it halts with an error, that's what it says when you position the cursor over the 'stFileName' variable?

    If so we need to fond out why that is. What have you entered in E43?

    Rgds

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

    no

    when i go to my desktop at work there is a folder on the desktop called job_file on server but when i hovver the cursor over the folder its destination says

    \\server\job_file

    i have tried this address and its no different c:\desktop\job_file on server
    which is what the folder is called

    does it make any difference at the way the folders inside the job folder are saved?

    inside the job folder they look like this but without the quotes

    "j5337 - camel"

    sometimes the j is a capital other times not
    and the customer name isnt always lower case either

    thanks richard

  65. #65
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi richard

    no

    when i go to my desktop at work there is a folder on the desktop called job_file on server but when i hovver the cursor over the folder its destination says

    \\server\job_file

    i have tried this address and its no different c:\desktop\job_file on server
    which is what the folder is called

    does it make any difference at the way the folders inside the job folder are saved?

    inside the job folder they look like this but without the quotes

    "j5337 - camel"

    sometimes the j is a capital other times not
    and the customer name isnt always lower case either

    thanks richard
    Steve

    Do you have any IT support in the office that could help you over this last bit of the problem? I'm puzzled that the path you mention '\\server' doesn't have a drive letter which I thought was a fairly standard way of organising folders/files whether on a network server or locally on the PC.

    Would you confirm what text you have in the three cells E43, D8 & I8 in the workbook you're using in the office, and would you also copy and paste the whole of the macro procedure into this message forum so that I can eliminate any problem with that.

    Rgds

  66. #66
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    no richard

    our i.t bloke is away for a month

    the code is

    #Sub SaveJobFile()
    Dim stFileName As String
    stFileName = Sheet1.Range("d43") & "\" & Sheet1.Range("d8") & "-" & Sheet1.Range _
    ("i9") & "\Docs\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=stFileName
    End Sub#

    is there any other way i can find it out???
    yes we are on a network

  67. #67
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    the hashes arent part of the code i thought i had to use them to wrap them on here?

  68. #68
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    no richard

    our i.t bloke is away for a month

    the code is

    #Sub SaveJobFile()
    Dim stFileName As String
    stFileName = Sheet1.Range("d43") & "\" & Sheet1.Range("d8") & "-" & Sheet1.Range _
    ("i9") & "\Docs\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=stFileName
    End Sub#

    is there any other way i can find it out???
    yes we are on a network
    Thanks Steve,

    and what are the values in the three cells?

    Incidentally you wrap code on here by clicking on the # icon where you see all the formatting stuff just underneath the Message: description. Clicking this brings up the html tags for code, /code

    Rgds

  69. #69
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    d43 is \\server\job_file

    d8 is j5331

    i9 is davies

    the actual foler is j5331 - davies
    Last edited by stevesunfold; 04-18-2008 at 06:58 AM.

  70. #70
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    trial
    Please Login or Register  to view this content.

  71. #71
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,081
    Excuse the intrusion, but in your previous posts you say that the file names are of the form: j5337 - camel with space,dash,space. However, in your code you are using "-" vs. " - " to concatenate the name...just a thought.
    Ben Van Johnson

  72. #72
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by protonLeah
    Excuse the intrusion, but in your previous posts you say that the file names are of the form: j5337 - camel with space,dash,space. However, in your code you are using "-" vs. " - " to concatenate the name...just a thought.
    Well spotted protonLeah. Thanks for that. Steve had got it working a couple of days ago on his home PC, so this additional space must have crept in on his office version.

    Steve... over to you. Will you check this out and let us know.

    Rgds,

    And thanks again ProtonLeah

  73. #73
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi guys

    im at home now and the worst thing is im off to italy tomorrow so wont access my work computer for about 10 days or so

    i can try it at home but im not entirely sure what you mean

    thanks guys for your help

    should i be trying a different code gents?

  74. #74
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi guys

    im at home now and the worst thing is im off to italy tomorrow so wont access my work computer for about 10 days or so

    i can try it at home but im not entirely sure what you mean

    thanks guys for your help

    should i be trying a different code gents?

    Hello Steve,

    You have a choice. Either change the name of the folder from
    'j5337 - camel' to 'j5337-camel' without the quotes, or leave the foldert name alone and change the code line from:

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Rgds

  75. #75
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    yeah i will have to leave it as all folders are set as j2345 - smith
    space hyphen space and theres almost 6000 folders and i dont want to change them

    now going to try it

    hope you are well richard

    appreciate your effort
    back soon

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

    worked a treat first time at home obviously what the problem was im guessing

    richard

    sometimes in cell d8 it will say j2345a

    the a afterwards or b or c or d and so on

    not very often does it go past a c but i only want it to read the job number

    ie j2345

    will this affect it?

  77. #77
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    guys guys guys

    worked a treat first time at home obviously what the problem was im guessing

    richard

    sometimes in cell d8 it will say j2345a

    the a afterwards or b or c or d and so on

    not very often does it go past a c but i only want it to read the job number

    ie j2345

    will this affect it?
    Hello Steve,

    That's good, but I think you've had it working OK at home before. The problem seems to be with the path/folder on your network.

    If the job number you require is always 5 characters or less then the revised line of code below will pick up just the first 5 characters:

    Please Login or Register  to view this content.
    Rgds

  78. #78
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard yes the job number is always 5 characters until we reach 10000 i guess but that can wait for another day

    i tried your new code and im getting compile error
    syntax error with that last code

    i want it to read the cell for the job number first 5 characters

    if j2345a

    it needs to read j2345 but save the file as j2345a

  79. #79
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard followed it to the t and it works perfectly

    except if i have a j2345a

    and then i have a j2345b

    it tries to save it but as a replacement and i dont want that

    but everything is superb now apart from that one thing

    thank you so much

    can i get them to save as per cell d8??

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

    everything is perfect including assigning the macros to a logo

    just want it to save as j2345a or j2345b what ever is in cell d8 please mate

    thank you so much

  81. #81
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    RICHARD

    everything is perfect including assigning the macros to a logo

    just want it to save as j2345a or j2345b what ever is in cell d8 please mate

    thank you so much
    Hello Steve,

    I'm slightly puzzled. This morning you said:
    sometimes in cell d8 it will say j2345a the a afterwards or b or c or d and so on not very often does it go past a c but i only want it to read the job number

    ie j2345
    Hence the revised code limited the string to the first 5 characters, i.e. excluding the a in j2345a.

    If you want to save exactly what's in d8 then just revert to the original code. i.e.

    Please Login or Register  to view this content.

  82. #82
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    richard what i mean is this

    in cell d8 is the job number

    but sometimes i have to do a revised order for a piece of glass that would be a remake

    ie j2345a

    as there is a job folder with the name j2345 i need this sheet j2345a to be saved in folder j2345

    basicly the folder j2345 is to hold all the documents with that job number and any letter that may follow it in to that folder but i want it to be saved as cell d8

    so basically it will be saved as glass sheet j2345a in job folder j2345

    therefore if i have to do another one like j2345b it will also go in the job folder j2345 and not over write the first one

    is that clear mate?

    thanks

  83. #83
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    richard what i mean is this

    in cell d8 is the job number

    but sometimes i have to do a revised order for a piece of glass that would be a remake

    ie j2345a

    as there is a job folder with the name j2345 i need this sheet j2345a to be saved in folder j2345

    basicly the folder j2345 is to hold all the documents with that job number and any letter that may follow it in to that folder but i want it to be saved as cell d8

    so basically it will be saved as glass sheet j2345a in job folder j2345

    therefore if i have to do another one like j2345b it will also go in the job folder j2345 and not over write the first one

    is that clear mate?

    thanks

    OK Steve

    The D8 cell is the name of the folder not the name of the file. The file name that's saved comes from the bit of the code that refers to Activeworkbook.Name.

    It sounds as though you are opening J2345, making some changes, changing D8 to refer to what you think is the file name, but which is in fact the folder name, and then wanting to save it immediately as J2345a plus client name etc.

    As the macro is written at the moment it requires that J2345a has been opened, and is in fact the name of the file in which you're making the changes. i.e. it implies that you must first have saved the file manually with the j2345a name.

    If you want to be able to create a subsidiary a,b,c etc. name to be included in the file name, then we're going to have to build up the name rather than simply accept the name of the workbook that you happen to have opened. i.e. we'll have to incorporate another cell where you will add a suffix like a,b,c etc, or indeed a blank, and include this additional cell in the construction of the file name.

    If this is the way you want to go, just post back and I'll work out an amended macro. If we do this you'll have to let me know whether there is any other text, like Glass Sheet, to be included in the file name. Doing it this way would mean that the filename is fixed in format. At the moment the filename could be anything you want it since the D8 cell is not used for naming the file only the folder in which it should go.

    Rgds

  84. #84
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    Richard The Sheet Needs To Be Saved As Whats In Cell D8

    So The Whole Thing Should Look Like This ( Or Something Very Similar )

    Glass Reject Order And Error Report Form ( Then What Ever Is In Cell D8 )

    Ie

    Glass Reject Order And Error Report Form J2345a

    Could Be B,c,d Whatever After The J2345

    But It Needs To Be Saved In The Job Folder ( Where It Is Saving It At Present ) J2345

    The A,b,c,d Or Whatever Dont Come Into Play With The Job Folder

    So This Would End Up Like This:-

    Glass Reject Order And Error Report Form J2345a

    Would Be Saved In Job Folder J2345

    Or

    Glass Reject Order And Error Report Form J2345k

    Would Still Be Saved In Job Folder J2345

    Thanks Mate And After This Thats That

    Phew Thank God I Hear You Say

  85. #85
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    Basically Richard It Needs To Do Exactly What Its Doing Ow But Saving It As What Ever Is In Cell D8

    And Saving It In What The First 5 Characters Are In Cell D8
    Folder

    The Letters After The Job Number Are Only A Way Of Saying How Many Times A Piece Of Glass Has Been Reordered

  86. #86
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    Basically Richard It Needs To Do Exactly What Its Doing Ow But Saving It As What Ever Is In Cell D8

    And Saving It In What The First 5 Characters Are In Cell D8
    Folder

    The Letters After The Job Number Are Only A Way Of Saying How Many Times A Piece Of Glass Has Been Reordered
    Steve,

    Try this. I've tested it on my Mac but not on a PC. Let me know if it's not working as you expect.

    Please Login or Register  to view this content.
    Regards,

    Richard

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

    yes mate

    thats absolutely perfect

    i cannot thank you enough

    if there is anyway i can thank you please say so

    perhaps a small gift i could send you

    after all you have spent a lot of time on this project and its only me that will benefit from it

    pm me your address and a gift will follow

    im now off to naples for a week but will be back on the 28th or 29th

    thanks so so much

    this forum is the tops

  88. #88
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi richard

    yes mate

    thats absolutely perfect

    i cannot thank you enough

    if there is anyway i can thank you please say so

    perhaps a small gift i could send you

    after all you have spent a lot of time on this project and its only me that will benefit from it

    pm me your address and a gift will follow

    im now off to naples for a week but will be back on the 28th or 29th

    thanks so so much

    this forum is the tops
    Steve,

    Many thanks for that extremely kind offer, it is very thoughtful and much appreciated. I was only too glad to help and that's reward in itself.

    Have a good time in Italy and maybe I can help again on some other occasion.

    Kind regards,

    Richard

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

    had a great time in naples and if youre ever off there send me a pm and i will tell ya where and where not to stay

    thanks for your help

    im back to work tomorrow so hopefully it will work directly to the server folder

    heres hoping

    thanks mate.........speak soon

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

    bingo!!!!

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

    once again thanks for everything

  91. #91
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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

  92. #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

  93. #93
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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

  94. #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

  95. #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?

  96. #96
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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

  97. #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

  98. #98
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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.

  99. #99
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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]

  100. #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

  101. #101
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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

  102. #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

  103. #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

  104. #104
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    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

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

  106. #106
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    in cell b69 and b70

    i plan to have 2 destinations

    b69 will be \\server\job_file
    b70 will be \\server\Master Documents

    if there is a j number ( ie j1234 ) in cell g23 then it will need to be stored in the destination in b69 as per the glass order you did for me

    and it should read something like

    \\server\job_file\cell g23\-\cell I23

    and like it to be saved as

    cell b16,purchase order,cell k12

  107. #107
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    if cell g23 contains stock

    then it will need to go to destination b70

  108. #108
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Steve

    I'm attaching your template with the macro within it - also shown below.

    Please Login or Register  to view this content.
    Rgds
    Attached Files Attached Files

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

    sorry for the delay
    am at home now
    and ive reattached a sheet for you to see that will be the same but one i can use at home

    http://www.savefile.com/files/1535584

  110. #110
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    so, as you can see i have changed the destinations

    i want to use the click here to save button but i need to assign it a macro to do so

    ok so lets talk through the sheet

    this sheet is a purchase order sheet

    if cell g23 has a j reference followed by 4 numbers ( there will be no letters after the numbers ) ( ie j4567 ) then it needs to go to destination in cell b69

    C:\Documents and Settings\name\Desktop\job folder

    it will then go the appropriate folder

    C:\Documents and Settings\name\Desktop\job folder\j4567

    it will then add the hyphen and cell I23

    C:\Documents and Settings\name\Desktop\job folder\j4567 - smith

    it will then add the docs

    C:\Documents and Settings\name\Desktop\job folder\j4567 - smith\docs

    and hopefully it will save itself as

    cell b16 purchase order cell k12.xls

  111. #111
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    if cell g23 has a reference that says stock then it needs to go to destination in cell b70

    C:\Documents and Settings\name\Desktop\master documents

    it will then go the appropriate folder

    C:\Documents and Settings\name\Desktop\master documents\stock purchase orders

    and hopefully it will save itself as

    cell b16 purchase order cell k12.xls
    Last edited by stevesunfold; 05-02-2008 at 03:06 PM.

  112. #112
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Steve,

    A small addition to the attached file. The previous only tested for an upper case 'J' at the start of the Job number. I've changed it so that it will recognise both an upper and lower case 'j' as per the example workbook.

    Rgds
    Attached Files Attached Files

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

  114. #114
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    im getting 400 error richard
    On which line?

    The paths and names that the code generates from the template you supplied is as below. The first one is the job, and the other for the Purchase Order

    Can you confirm that all the folders before the final bit which contains the .xls filename actually exist?

    If not play around with cells G23, I23, B69, B70, B16 and K12, which contain all the relevant text values.

    What's that reference to 'name' in B69 & B70? Is this really a folder? If not change it as appropriate. The code will work provided the folders etc. exist.

    Please Login or Register  to view this content.
    Rgds

  115. #115
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    basically name is my name and yes its a folder

    there are 2 destinations

    one for if cell g23 is a j number

    and the other destination is for if cell g23 fsays stock

    if its a j number it needs to be saved in the relevant job folder followed by space hyphen space then the customer name in cell I23

    if its stock it should go in the second destination

    the should all be saved as purchase order ( glass error report as youve got at mo )

  116. #116
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    [QUOTE=stevesunfold]basically name is my name and yes its a folder

    there are 2 destinations
    ...

    Steve,

    When you refer to 'name' and say it's my name, do you mean you've got a folder called 'Steve' or is there in fact a folder called 'name'?

    If the former you'll need to change cell B69 & B70 accordingly so that they contain the characters 'Steve'

    Have you examined the paths/folders in my last post and confirmed you really do have them on your system?

    Rgds

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

    yes does as you requested but am confused as to why Glass Reject Order And Error Report Form j4567.xls is part of the code

    as its a purchase order form and not glass reject order and error report form

  118. #118
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    would you be kind enough to put the whole code here please

    amybe i can change the glass part??

  119. #119
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    hi richard

    yes does as you requested but am confused as to why Glass Reject Order And Error Report Form j4567.xls is part of the code

    as its a purchase order form and not glass reject order and error report form
    Only because that's what was in the original specification.
    Those words are hard coded in the macro. If you don't want them, just remove them, and the 'Range("G23") which is the job number, just leaving the ".xls" bit.

    Rgds

  120. #120
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    would you mind posting th code please mate
    cos i cant seem to see it
    i have tried the right click view code but cant see anything


    thanks mate

  121. #121
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    would you be kind enough to put the whole code here please

    amybe i can change the glass part??

    Hi Steve,

    The complete code was in one of my previous messages viz.

    http://www.excelforum.com/showpost.p...&postcount=108

    The last 'glass bit' as you call it is what you want the name of the file to be called. You can change the text in the code, but you'll need at least some descriptive text - the file has to have a name!

    If you don't want the job number included in the file name then delete the '& Range("G23")' bit as well.

    Rgds

  122. #122
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    the thing is it does absolutely everything perfectly when i input stock into cell g23 and saves the workbook as TESCO Purchase Order w0342 into the correct folder

    but when i try the j4567 reference it saves it as glass order ........

  123. #123
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    cant understand either why i cant see the code when i right click the tab and select view code

    why is this

  124. #124
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    HI RICHARD

    THIS IS THE CODE IVE GOT

    Please Login or Register  to view this content.
    IT ONLY SAVES IN THE JOB FOLDER IF THE J NUMBER IS A CAPITAL J AND I WAS HOPING IT WOULD BE UPPER AND LOWER CASE

    AND THE CODE IS TELING IT TO SAVE AS GLASS REJECT ORDER AND ERROR REPORT FORM WHERE AS I WANT IT TO SAVE AS IT DOES IF I WERE TO PUT STOCK

    IE
    IE CELL B16,PURCHASE ORDER,CELL K12

  125. #125
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi guys

    anyone out there who can help me out of this hole please

    gratefully appreciated

  126. #126
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,935
    Quote Originally Posted by stevesunfold
    HI RICHARD

    THIS IS THE CODE IVE GOT

    Please Login or Register  to view this content.
    IT ONLY SAVES IN THE JOB FOLDER IF THE J NUMBER IS A CAPITAL J AND I WAS HOPING IT WOULD BE UPPER AND LOWER CASE

    AND THE CODE IS TELING IT TO SAVE AS GLASS REJECT ORDER AND ERROR REPORT FORM WHERE AS I WANT IT TO SAVE AS IT DOES IF I WERE TO PUT STOCK

    IE
    IE CELL B16,PURCHASE ORDER,CELL K12

    Hi Steve,

    Please see post 112 where you'll note I modified the code to allow for upper and lower case 'J'

    http://www.excelforum.com/showpost.p...&postcount=112

    The 'Glass Reject......' stuff was your original specification. If you want this to be the same as the name used by the Stock option, then just change the text to read "Purchase Order" - or anything you want.

    You might want to consider holding the names you wish to use for the two choices, in two more variable cells on the sheet, and then use those cell references in the macro just like the other cell variables.

    Rgds

  127. #127
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    i cant see how to get the code though richard from that post

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

    thanks to you and your patience ive got it

    thanks so much mate ive been tinkering and managed to get there

    phew


    cant thank you enough for all your effort , cheers

    here is my final code and it works

    Please Login or Register  to view this content.

+ 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