Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
Add .LOG to .txt file on creation | Excel Programming | |||
Add .LOG to .txt file on creation | Excel Programming | |||
PDF file creation | Excel Programming | |||
Copy excel file to subfolder | Excel Programming |