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.

Co

 

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

 

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

 

[Guest]

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.