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?


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=545527

Use

mkdir "C:\LOA\" & strProjectNumber

before you save the file.

Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=54552

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.


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=545527

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.


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=545527

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


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=545527

Something like

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


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=545527

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

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

Sorted indeed! Thanks! :)


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=545527

Sorted indeed! Thanks! :)


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=545527

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

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"
wrote in message
...

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

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