find a folder extension macro

[stevesunfold]

hi guys
this query has now took a peculiar twist in the fact that my boss now wants to change the goalposts

here is a thread with the code in

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

thanks to broro183

Please Login or Register  to view this content.

my boss has now decided that all the old files will have a few different subfolders which will not include \docs

so what i need this code to do is look for \docs and if it doesnt exist ask it too look for the new sub folder of Ordering Docs

is this possible?
i hope so and so sorry to annoy you all again

regards

[Simon Lloyd]

Steve, Please click on the Edit button on your post click Go Advanced and change your title to something a little more descriptive its more likely to get you the response you need!

[stevesunfold]

sorry simon

[Simon Lloyd]

No worries, thanks for changing it!

[Richard Buttrey]

Steve,

This is untested since one of the Functions called uses the "File System" object syntax which isn't available on my Mac. However try it and if it works, or if broro steps in and amends his original code, then that would be good.

If not post back and I'll dig out an old PC over the weekend and try it there.

The lines I've added are in red.

Please Login or Register  to view this content.

[stevesunfold]

yeah hi richard thanks for the reply

it returns bad file name

run timer error 52

regards

[broro183]

hi,

Sorry, I'm being a bit lazy so this is untested too - hopefully it works ;-)
(see between the stars)

I have used the same type of approach as Richard but I used the other function "doesfilefolderexist" which returns a boolean response rather than the "getactualfoldername" which gives a folder name. btw, I don't know much about fso objects either - this was really a stolen & modified function.
Also, I've inserted a second check in case the "Ordering Docs" folder doesn't exist either.

Please Login or Register  to view this content.

hth
Rob

[stevesunfold]

hi broro

unfortunately it doesnt work and returns the following error

macro ending b/c either :
1) no folder with the following job number exists
c:Documents and settings \HP_Owner\My documents\j4580*

[Richard Buttrey]

Steve,

That error you're getting doesn't appear to be caused by the modification broro (or I) made. It's comes as a result of an earlier bit of code which was presumably working OK before the additional folder stuff was added.

Can you confirm that you do have a .....J4580\Docs folder because the error message is suggesting you don't? Have you perhaps deleted that one now you've set up an alternative? If so try re-instating the original ...\Docs folder and seeing if that sorts it.

HTH

[stevesunfold]

hi richard

the code at the top works perfectly except if the docs folder doesnt exist

ie the job folder contains a folder called ordering docs instead of docs

i then tried your code and it comes up with

compile error
sub or function not defined

and it highlights Sub SaveJobFile() and also this line DoesFileFolderExist

thats before i even try it without the docs folder and replace it with ordering docs

i then try broros code and it does exactly what i just put

i dont think we are very far away at all
in fact its probably somethng so small its unreal

thanks for replies

[broro183]

Steve,

Have you actually tried stepping through the code using the [F8] key in the VBE?
This will help you understand what each line of the code is doing and also show you the previous line before the code is instructed to stop by jumping or "going to" the error handling message box at "TheEnd".

The most likely reason that Richard's code has a compile error would be that you have replaced all of the original code with his modified version of "SaveJobFile" instead of just replacing the "savejobfile" macro (ie deleted the two functions at the base of the original code). The 2 functions are needed otherwise the macro can't complete because as VBE states "sub or function is not defined".

The below version is now tested & will check for the alternative file "Ordering docs", if it doesn't find one it will create it.

Please Login or Register  to view this content.

hth
Rob

[stevesunfold]

broro

thanks so so much i really appreciate it
lets hope my boss does too

im still very very new to this macro stuff and im learning as i go but its very slow going

does anybody have any advice or any good books

it works a treat

youre a star

[broro183]

hi Steve,

Thanks for the feedback - I'm pleased I could help :-)

My advice is to make the Help files your friend, try recording macros initially (not possible for all tasks eg the one that we've just solved) & then reading the Help files for the sections of recorded code that need modifying. Then search excel forums using keywords/phrases to find out/try & solve your questions before asking for help - you're likely to learn a lot more "by experience"... and helpers are often more willing to assist when some trial & error is shown.

Surfing the web regularly & just reading threads that look interesting has given me a variety of titbits that I can call on when needed. Also, I've read a few books in the past 3 years & the one that I have found most helpful (after tens/hundreds of hours surfing the web + my work gave me some Excel context) is John Walkenbach's Power Programming. Some people disagree but I found it easy to read & quite varied with suitable step by step examples. I now find it easier & easier to understand the context that some OP's are questioning about b/c I've been bitten by the bug & am addicted to learning what VBA can do to both help myself & others - if you're "lucky" the bug may get you too & then the learning is likely to become quicker ;-)

You may find the below link useful too:
http://www.excelforum.com/showthread.php?t=620254

have fun!
Rob

[Richard Buttrey]

Well done Rob.

I agree with all your comments, that's how I started too. Although recording macros is limited in so many ways, I still find it a great, and a quick and easy way of capturing syntax and parameters for stuff I'm not using all the time. It's also useful since it leads you into understanding the various processes and gives something to start with when you want to experiment and edit code.

I also found 'Writing Excel Macros' by Steven Roman and the 'Excel 2000 (since updated 2003) Programmers Reference Guide', by Paul Kimmel, Stephen Bullen and others, very useful.

Regards,

R.

[stevesunfold]

thanks guys for the help and advice it really is much appreciated

i know this is going to save me so much time and i can only thank you guys for that after all its only me thats really going to benefit from this

it makes a refreshing change in this world that we live in that somebody actually does care and dont need a cash transaction to do it

im quite happy to buy you both a beer as the time saved means i will have more time to perhaps buy a beer if ya ever in norfolk let me know and youll get ya beer

thanks guys

right im off now to think up some more time saving projects hee hee

quite strange really thinking how much time has been spent on this means that in reality i will save a few seconds here and there but it also means i wont forget to save something somewhere i should have

cheers and good luck

[stevesunfold]

hey richard

just for peoples information i googled what you said steve roman and it came up with this free to view book let

http://books.google.co.uk/books?hl=e...FwHA#PPA117,M1

[Richard Buttrey]

Wow,

Thanks for that reference Steve. I've not seen that level of detail before. It's almost the whole book. Interestingly the odd page is suppressed and a copyright note appears to tell you that. I wonder why some pages are copyright but not others. I must have a look in more detail.

It's probably still more user friendly to buy the book since reading these things on line is not easy, but interesting nevertheless.

If I'm ever in Norfolk I'll certainly hold you to that beer!

Thanks,

R.

[stevesunfold]

deffo m8 mesaage me b4