Creating a folder and saving file within Folder (Naming convention within file)

[alex900_6]

Hi all,

Not sure if this is possible but seems anything is possible in excel!!

I have a Macro; below, that saves a file named to a cell within the file but will not allow to overwrite if the file already exists. I need to take this a step further and create a 'new' folder first (folder name the same as the file) and then save the file within that folder. Again, if the folder already exists, you will not be allowed to continue.

Please Login or Register  to view this content.

Not sure if this can be done but any help would be appreciated.

Thanks

[omahaNative_1023]

if dir already exist, then stop the routine. use DIR to check if folder or file exist. it is in the argument. then give message to user if happens. or do what you want. here is the page that tells arguments

https://docs.microsoft.com/en-us/off...p/dir-function

[alex900_6]

Thanks for the response but my knowledge is very limited on VBA (Someone created the code for me)!!

I need if possible for the code to be adapted to create a new folder and save the file within the newly created folder.

I'm stumped!!

[omahaNative_1023]

use this and then put in your own code

Please Login or Register  to view this content.

https://docs.microsoft.com/en-us/off...p/dir-function

[alex900_6]

Makes no difference......just proceeds with saving the file in the selected location.

I want the VBA to create a folder (Using cell Q2 as the folder name)......and then save the file within the newly created folder (Using Q2 as the file name)......if the folder already exists an error message pops up.

[omahaNative_1023]

Seems to me that you might have run code in correctly. I tested that on my end and worked fine. But you could also use file system object to check if directory exists or not. You can find file system object reference on Microsoft website like the link I gave you in my last post.

[alex900_6]

Can you send me the full code as you've ran it please?.....like i say, this is alien to me (to a point). It runs fine but just save the file as previous....it's not creating the 'new folder'

Thanks

[omahaNative_1023]

see what you can do with this. change the dir obviously to what you need.

Please Login or Register  to view this content.

see attached book. it works fine.

[alex900_6]

Thanks for your patience but still no joy......keep getting the message "dir already exists. PO file can't be created." Though folder doesn't exist!!

[omahaNative_1023]

can't help you. it works for me. you need to research beginner vba code.

[AC PORTA VIA]

TRY THIS
Sub CREATE_FOLDER_SAVE_FILE_PDF()
Dim pdfname As String
Dim fileSaveName As String


pdfname = ActiveSheet.Range("a1")
vDir = "C:\test\test\" & pdfname
fileSaveName = vDir

If Dir(vDir, vbDirectory) = "" Then
MkDir (vDir)
Else
MsgBox "dir already exists. PO file can't be created.", vbCritical
Exit Sub
End If

ChDir "C:\test\test\" & pdfname

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
pdfname _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End Sub

[alex900_6]

Thanks for coming back to me.......nearly there!!

You're code creates the folder, great and also stops with a message if the folder already exists, again, great. However, the pdf is not saving within the created folder!! Can't see why in your code other than maybe vDIR maybe?......my location is "\\Reports\Internal PO Log\PO pdf's\"......how do i point it to the new folder and save the file?

If i change 'OpenAfterPublish' to "True" it opens the file as a pdf but has no location for it.

I would also like a message at the end to say "Your PO has been saved"

As i say, nearly there, your help is much appreciated.

Thankyou

[AC PORTA VIA]

it does save when i try it
you may try to check your folder path to make sure it is correct
"\\Reports\Internal PO Log\PO pdf's\" - where is this located -desktop - C: DRIVE?

[alex900_6]

No it saves to the business external drives which shouldn't cause any problems.....just can't work out why it wouldn't save. The file path is correct and it's working (as in creating the file) it just isn't saving to the new folder!!

Heads hurting now, been trying different options for over an hour!!

[AC PORTA VIA]

not sure really
here is the new code you can try with your path and message at the end to say "Your PO has been saved"

Please Login or Register  to view this content.

[alex900_6]

Thanks for you help (and patience).....

It's just not working! Absolutely no idea why?....i'm not even getting the message at the end. It's just creating the folder, that's it. No debug error messages. I'm stumped!

Thanks for your help anyway, understand if you give up on me!

[alex900_6]

So, I've amended the file path to my Desktop and it's worked which proves your code to be correct!! :-) I have no idea what the issue would be saving to an external drive??

[AC PORTA VIA]

try this to make sure code is correct
create folder name test on your desktop and then run this code
check to see if file is saved in this folder

Please Login or Register  to view this content.

[alex900_6]

Thanks, i've just done that as above and it's worked.....need to work out why it's not when using an external drive?

[AC PORTA VIA]

your path to external drive is not correct
right click on the folder name PO pdf's on the external hard drive and you will get correct path

[alex900_6]

Right then, i'll give you the full path;

\\cdhnas\Reports\Internal PO Log\PO pdf's

It's just not happening......i'm copying this path directly from 'File Explorer'!

So frustrating now i've seen it work correctly using my desktop :-(

[AC PORTA VIA]

Not sure really

[alex900_6]

:-(

OK, thanks for your help anyway, you solved the code which was great thanks.....I just need to try and work out the problem 👍

Thanks again

[AC PORTA VIA]

You are welcome