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

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

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?

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

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

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

im getting error 400 return

sorry if i have annoyed you richard but im just not 100% on what you are saying

i appreciate your help

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

cheers richard but im still getting a 400 error

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

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

think im going to give up richard

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

probably doing something really basic wrong

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

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

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

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

if i change it to C:\desktop\job folder

i get a 400 error

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.

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

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

e43 is where im storing the destination

THIS IS THE DESTINATION I THINK YOU WERE AFTER TRISTAN/RICHARD

\\server\job_file

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

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

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

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

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

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

hi tristan
that code is returning the 400 error again

Check Rich's last post - I didn't spot the "J9" error.

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

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

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

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

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

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

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

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

i just wonder if its because im on a server

richard how can i be certain ive got the right path?

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

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

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

would you be kind enough to tell me the exact code to put in please richard

richard sorted it

its returning this

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

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

aaaaarrrrrrggghhhhhh

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

is it possible to do this without worrying about the name?

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

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

hi richard sorry for the delay

getting home from work

yes i can indeed confirm that works mate

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

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

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

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

you about richard?

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

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

richard you about?

Originally Posted by stevesunfold

richard you about?

Steve,

I am at the moment.

Cheers!

R.

hi m8 yeah i checked at work and when i hovver over the file it definetly says \\server\job_file

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

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

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

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

the hashes arent part of the code i thought i had to use them to wrap them on here?

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

trial

Please Login or Register  to view this content.

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

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?

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

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

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?

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

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

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

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

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.

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

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

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

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

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

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

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

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

richard

bingo!!!!

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

once again thanks for everything

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

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

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

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

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?

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

Yes Richard The Latter Is Fine

If Not

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

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.

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]

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

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

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

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

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

HI RICHARD

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

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

if cell g23 contains stock

then it will need to go to destination b70

Steve

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

Please Login or Register  to view this content.

Rgds

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

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

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

im getting 400 error richard

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

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 )

[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