subfolder and file creation

[Robert Crandal]

I am currently interested in code for searches for the
existence of either folders or files. If the current month
is January, I need to search a given path to check of
a sub-folder named "January" exist. If the "January"
folder is not found, I will need to create that subfolder.

My first question is how do test if a folders exists?
Second, if it doesnt exist, how do you create it???

Once it is determined that the desired folder exists
(or was just created), I will need to search that subfolder
for an arbitrary file. My last question is, how can I
determine if a file exists in the folder so I can open it??

Thank u all

 

[Per Jessen]

Hi

Look at this :

Sub test()
Dim wbA As Workbook
Dim wbB As Workbook

Set wbA = ThisWorkbook
Mnth = Format(Date, "MMMm")
fName = "FileToOpen.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists("C:\Temp\" & Mnth) Then
If fs.FileExists("C:\Temp\" & Mnth & "\" & fName) Then
Set wbB = Workbooks.Open("C:\Temp\" & Mnth & "\" & fName)
Else
'File does not exists
End If
Else
fs.createfolder ("C:\Temp\" & Mnth)
End If
If wbB Is Nothing Then
msg = MsgBox("File does not exists", vbExclamation + vbOKOnly, "Merry
Christmas")
End If
End Sub

Hopes this helps.
....
Per

 

[Robert Crandal]

Thank you very much. That code worked very nicely for me.

I have another question now. If the file doesnt exist. I plan to create
the file by copying a file and renaming it to the target filename. So,
what is the proper way to copy a file to another filename (and
possibly rename the file at the same time).

thank you!

 

[Per Jessen]

Hi

Thanks for your reply.

You can do it like this:

If wbB Is Nothing Then
Set wbB = Workbooks.Open("C:\Temp\Template.xls")
wbB.SaveAs "C:\Temp\" & Mnth & "\" & fName
End If

Regards,
Per