Creating a folder using macro...

[ChrisMattock]

I have a macro that brings up an Input Box which takes a filename (fnames$) and saves an excel sheet as that filename, I need this to create a folder first then save itself in the folder. The folder name would be the same as strProjectNumber...

bk.SaveAs FileName:="C:\LOA\" & strProjectNumber & "\" & fnames$

OK, well obviously the above doesn't work but I am trying to get it to save as

C:\LOA\ => Then a new folder based on the strProjectName => Then the filename fnames$ (previously entered from an Input Box (No help needed with this))

Anyone help?

[colofnature]

Use

mkdir "C:\LOA\" & strProjectNumber

before you save the file.

Col

[ChrisMattock]

Thanks! But if the directory I am trying to make already exists will that be a problem? Also how would I then enter the save command to reflect the directory based on strProjectNumber.

[colofnature]

Yup, 'fraid so. It'll throw up a run-time error (75, if you're interested).

You could either trap this error, or check for the existence of the directory before you attempt to create it.

[ChrisMattock]

Hmm ok, I am an idiot and not very VB talented the idea of checking first seems sensible, how wuold I do that though?

[colofnature]

Something like

if dir("C:\LOA\" & strProjectNumber,vbdirectory)<>"" then
msgbox "Folder already exists!", vbcritical
exit sub
' or whatever...
endif

[ChrisMattock]

Thanks, but if the directory exists I just want the files saved in it rather than it quitting out, and I still don't know how to modify the path when saving the file to reflect the directory name. I did warn you I was an idiot. :P

[colofnature]

No worries! Your code needs to be

if dir("C:\LOA\" & strProjectNumber,vbdirectory) = "" then mkdir "C:\LOA\" & strProjectNumber
bk.SaveAs FileName:="C:\LOA\" & strProjectNumber & "\" & fnames$


Sorted!

[ChrisMattock]

Sorted indeed! Thanks!

[ChrisMattock]

Sorted indeed! Thanks!

[Ivan Raiminius]

Hi Chris,

on error resume next
mkdir "C:\LOA\" & strProjectNumber 'if already exists you will get
error
err = 0 'you don't check for this error (either the folder was created
or already exists. Third possibility is write protected disk - not
worth to check, because also saving will fail)
bk.SaveAs FileName:="C:\LOA\" & strProjectNumber & "\" & fnames$
if err<>0 then
msgbox "Error while saving"
else
msgbox "Saved"
end if
on error goto 0

Regards,
Ivan

[Bob Phillips]

On Error Resume Next
mkdir "C:\LOA\" & strProjectNumber
On Error Goto 0

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"colofnature" <[email protected]>
wrote in message
news:[email protected]...
>
> No worries! Your code needs to be
>
> if dir("C:\LOA\" & strProjectNumber,vbdirectory) = "" then mkdir
> "C:\LOA\" & strProjectNumber
> bk.SaveAs FileName:="C:\LOA\" & strProjectNumber & "\" & fnames$
>
>
> Sorted!
>
>
> --
> colofnature
> ------------------------------------------------------------------------
> colofnature's Profile:
http://www.excelforum.com/member.php...o&userid=34356
> View this thread: http://www.excelforum.com/showthread...hreadid=545527
>

[Tom Ogilvy]

s$ = "C:\LOA\" & strProjectNumber
On Error Resume Next
mkdir "C:\LOA\" & strProjectNumber
On Error goto 0
bk.SaveAs FileName:= s$ & "\" & fnames$

should do what you want.

--
Regards,
Tom Ogilvy



"ChrisMattock" wrote:

>
> Thanks, but if the directory exists I just want the files saved in it
> rather than it quitting out, and I still don't know how to modify the
> path when saving the file to reflect the directory name. I did warn you
> I was an idiot. :P
>
>
> --
> ChrisMattock
> ------------------------------------------------------------------------
> ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
> View this thread: http://www.excelforum.com/showthread...hreadid=545527
>
>