subfolder and file creation
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 |
subfolder and file creation
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" skrev i meddelelsen ... 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 |
subfolder and file creation
To get a monthname using a number for the month use the following code Dec = MonthName(12, abbreviate:=False) Dec = MonthName(12, abbreviate:=True) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165124 Microsoft Office Help |
subfolder and file creation
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" wrote in message ... 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 |
subfolder and file creation
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 "Robert Crandal" skrev i meddelelsen ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 12:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com