Solved: Creating new folder & saving copy as:

[johnske]

Hi All, hope you can help...

I think most will see what I'm trying to do here, but the main problem is I dont know how to do create a new folder via VBA:

[vba]Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'My default file path is the desktop, so need 2 change it here to C:
Application.DefaultFilePath = "C:\"

'now, if it doesn't already exist, I want to create a folder on the c drive
'labelled with the active workbooks name (but without the .xls)

'I want to save a COPY of the active book to this folder titled
'as: ActiveWorkbook.Name & Date + Time
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Name & Date + Time '< this gives an error

'now I want to restore my default file path
Application.DefaultFilePath = "C:\windows\desktop"

End Sub[/vba]

[xcelion]

Hi Jhon,

You can use the MKDir statement to make a new Directory.If you give


[vba]Sub MakeNewFolder()
MkDir "yourfoldername"
End sub[/vba]

This will create folder in current working folder .You can specfy full path you want.

Thanks
Xcelion

[Anne Troy]

Hi, Xcelion! Welcome to vbax. I've edited your code to use VBA tags. Check my signature for instructions.

[xcelion]

Thanks DreamBoat

[johnske]

Hi Xcelion,

Thanx very much for that (that's one step done) I now have it as:[vba]Sub TryIt()
MkDir ("C:\MyFolder")
End Sub[/vba]
which is fine if I have a fixed name for the folder, but it won't allow me to assign a variable name to it.

Regards,
John

[Anne Troy]

No clue if this helps or not, John:


[vba]
'Hard coded Path value

'Path = "\\Server\apps\"

'Path = "C:\"

'User inputs Path variable

Path = Range("B1").Value[/vba]

[Anne Troy]

Sorry. Of course you'll uncomment what you'll use...

[xcelion]

I tride out this and it's working

[VBA]
Sub test()
Dim str As String
str = "C:/YourFOlder"
MkDir str
End Sub [/VBA]

Are you facing any problem ?

[johnske]

OK thanx again Xcelion,

This is similar, and it's what I was trying before and it gave error messages, now, all of a sudden it's working? Obviously something wrong with my machine [vba]Sub test1()
Dim NameIt$
NameIt = "C:/" & ActiveWorkbook.Name
MkDir NameIt
End Sub[/vba]

@ Dreamboat PS: I found what was wrong before, OE had decided to set itself to "Disconnect after sending & receiving" - again >

[cleturno]

Now with those questions already answered, how do you get to the my documents of a specific user to create a folder within there if it does not exist.

[johnske]

Now with those questions already answered, how do you get to the my documents of a specific user to create a folder within there if it does not exist.

Wow, this is an old thread!

Just change address, e.g.[vba]MkDir "C:\My Documents\" & ActiveWorkbook.Name[/vba]

[cleturno]

Wow, this is an old thread!

Just change address, e.g.[vba]MkDir "C:\My Documents\" & ActiveWorkbook.Name[/vba]

This will not work. Sorry I should have been more specific when I asked the question. The platform Win XP Prof. Uses the Windows login name and creates a new folder in the documents and settings folder for each user. So this means that I need a method of finding out who the active windows user is?

[Bob Phillips]

This will not work. Sorry I should have been more specific when I asked the question. The platform Win XP Prof. Uses the Windows login name and creates a new folder in the documents and settings folder for each user. So this means that I need a method of finding out who the active windows user is?

Try this
[vba]

Dim oWSH As Object
Const wsh_SPECIAL_FOLDER_MY_DOCS As Long = 16


Set oWSH = CreateObject("WScript.Shell")


MsgBox oWSH.SpecialFolders(wsh_SPECIAL_FOLDER_MY_DOCS)

[/vba]

[cleturno]

Thank you, I think that will take care of it. Made a slight variation, but at least now it will find the correct user dir. Thanks a bunch

[XLGibbs]

[VBA]

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'My default file path is the desktop, so need 2 change it here to C:
Application.DefaultFilePath = "C:\"

'now, if it doesn't already exist, I want to create a folder on the c drive
'labelled with the active workbooks name (but without the .xls)

'I want to save a COPY of the active book to this folder titled
'as: ActiveWorkbook.Name & Date + Time
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Name & Date + Time '< this gives an error

'now I want to restore my default file path
Application.DefaultFilePath = "C:\windows\desktop"

End Sub
[/VBA]


[VBA] ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Name & Date + Time '< this gives an error[/VBA]

Not to jump in or anything, but wouldn't the default Date and Time formats cause a file name syntax issue causeing the error?

In addition to the directory issue..addressed above
alternate"
[VBA]
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Name & Format(Date,"mm_dd_yy") & format(Time,"hh_mm")
[/VBA]


Not sure if this error had been resolved. Hope that helps.

[cleturno]

The problem was getting to the user's mydocs on XP pro based on the user's windows login name. Thanks for the help on that though it is greatly appreciated.

Thanks,
Chris