auto save to a folder resurrection

**stevesunfold** · 05-20-2008, 08:08 AM

hi guys

some of you may remember this thread

http://www.excelforum.com/showthread.php?t=641622

i have a macro that saves specifically to a folder

that contains a job number and customer name

ie \\server\job_file\J5212 - Sumner\docs

i have encountered a problem where the name is spelt differently

the folder is actually called j5212 - sumner

but if i type j5212 - summer it wont obviously save and comes up with an error because of the spelling mistake

despite my folder being as it is

is there anyway i can get this to save to the folder with just the j5212??

**mudraker** · 05-20-2008, 08:24 AM

If there will be only one folder starting with j5212 then it can be done fairly easily. If there is more than 1 forlder then a means of asking the user which folder would also need to be included in the code

**stevesunfold** · 05-20-2008, 08:52 AM

hi mudraker

great to hear form you again , hope you are well

yes there is only one folder
it goes like this

one big job file folder
inside this is a folder for each job
these are labelled up like this j5212 - sumner
and inside each job folder are 4 folders for different headings

but i didnt want it to require the customer name due to spelling mistakes
etc etc

**stevesunfold** · 05-20-2008, 10:40 AM

HERE IS THE CODE I HAVE

Please Login or Register  to view this content.

CELL G23 IS THE JOB NUMBER AND ALWAYS STARTS WITH A J FOLLOWED BY 4 NUMBERS ALTHOUGH THIS WILL INCREASE WHEN WE GET TO 10,000 BUT UNTIL THEN ITS FINE

B16 IS THE SUPPLIERS NAME

K12 IS THE PURCHASE ORDER NUMBER

CELL I23 IS THE CUSTOMER NAME WHICH IM HOPING WE CAN AVOID

**stevesunfold** · 05-20-2008, 01:08 PM

bump for mudraker or richard buttrey if free

**stevesunfold** · 05-21-2008, 04:22 AM

bump bump bump

**mudraker** · 05-21-2008, 06:14 AM

If I understand what you are after correctly it is just a case of removing the reference to I23

replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

You can shorten 1 of you commads and make it more efficient by replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

**stevesunfold** · 05-21-2008, 06:28 AM

hi mud
thanks for the reply

it doesnt work

the trouble is i think are the folders
they are actually saved as ( exactly as below )
job number space hyphen space customer name

ie j5213 - smith

**mudraker** · 05-21-2008, 06:49 AM

I thought my answer was to easy

So you want it to save in the folder with a name that start with the j5213 -

Try this version

Please Login or Register  to view this content.

**stevesunfold** · 05-21-2008, 07:00 AM

Hi Mudraker Im Getting A 400 Error
On That But Looks Promising

**mudraker** · 05-21-2008, 07:07 AM

What line of code is highlighted with the error & what is the error message?

**stevesunfold** · 05-21-2008, 07:14 AM

no code is highlighted but i get this message

microsoft office excel cannot access the file 'server\jo_file\3.pdf\Docs
there are several possible reasons

the file name or path does not exist ( it does )
the file is being used by another program
the workbook you are trying to save has the same name as a currently opened workbook ( not the case

**stevesunfold** · 05-21-2008, 07:15 AM

where does the 3.pdf come into it???

**broro183** · 05-21-2008, 07:56 AM

hi all,

I'm off to bed so I can't answer any questions but I think the below should help...
(I've been messing around since the "triple bump" earlier tonight & have incorporated as much error testing as I think necessary.)

Mudraker, I have tested this & it seems to work but can you please help the OP if any issues arise?
btw, I'm pleased you suggested the use of ucase :-)
I had thought of it too & wasn't sure if it was a better approach.

Please Login or Register  to view this content.

hth
Rob

**stevesunfold** · 05-21-2008, 09:01 AM

hats off to ya broro

bit long winded but it works a treat

thanks very very much

**broro183** · 05-22-2008, 07:49 AM

Hey it's a pleasure, thanks for the feedback - I'm pleased I could help :-)

It may seem "longwinded" but it works & at post number 15 ;-) !

I recommend you step through the code using the [F8] key in the VBE window so that you can understand how it works & then you can use the principles in future coding ie:
- declare all variables
- define variables at start of code so that any changes only need to be made in one place.
- refer to the the defined variables in the "action code"
- put any duplicate coding into other functions/macros which are then called from the main macro
- use some sort of error handling

btw,
- the statement

Please Login or Register  to view this content.

in the error handling places the text string in the Immediate window of VBE & can be seen by prressing [ctrl + g] in the VBE & can then be copied/pasted for investigation/comparisons.

- Looking at my coding a second time I realise the below can be simplifed/made more flexible for future changes in Job numbers

Please Login or Register  to view this content.

could be changed to

Please Login or Register  to view this content.

The select case clauses should be ordered fom the most likely to the least likely (may speed things up slightly) with the "case else" as the last option.
- Also, I'm sure there is a better way of identifying the actual folder rather than checking for existence & then looping - but I don't know how...

Rob

**stevesunfold** · 05-22-2008, 08:08 AM

cracking stuff and a good read too

thanks so so much mate really appreciate it

auto save to a folder resurrection

LinkBack

Thread Tools

Rate This Thread

Display

auto save to a folder resurrection

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions