Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
Hi All, Your assistance is required.
How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
Hi,
I think your saying that D:\Store Returns\Contacts already exists so this will create the directory you want MkDir "D:\Store Returns\Contacts\StoreNo" Create it and then do your save. Mike "leerem" wrote: Hi All, Your assistance is required. How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
Hi
I would always check for the existence of the folder first with a call to a function as below. ... ... foldername = "D:\Store Returns\Contacts\StoreNo\" If Not DirExist(FolderName) Then MkDir FolderName End If ... ... Function DirExist(FolderName) As Boolean On Error Resume Next ChDir (FolderName) If Err < 0 Then DirExist = False Else DirExist = True End If On Error GoTo 0 End Function -- Regards Roger Govier "leerem" wrote in message ... Hi All, Your assistance is required. How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
Hi Mike, that seems so simple MkDir..
How could I adjust this so that it only performs this task if the file doesn't exist or does it not matter. i wouldn't want it to keep creating several file with the same name. "Mike H" wrote: Hi, I think your saying that D:\Store Returns\Contacts already exists so this will create the directory you want MkDir "D:\Store Returns\Contacts\StoreNo" Create it and then do your save. Mike "leerem" wrote: Hi All, Your assistance is required. How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
Like this
If Len(Dir("C:\Store Returns\Contacts\StoreNo", vbDirectory)) 0 Then MsgBox "Exists" Else MsgBox "Not There" 'do things End If Mike "leerem" wrote: Hi Mike, that seems so simple MkDir.. How could I adjust this so that it only performs this task if the file doesn't exist or does it not matter. i wouldn't want it to keep creating several file with the same name. "Mike H" wrote: Hi, I think your saying that D:\Store Returns\Contacts already exists so this will create the directory you want MkDir "D:\Store Returns\Contacts\StoreNo" Create it and then do your save. Mike "leerem" wrote: Hi All, Your assistance is required. How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
many thanks Mike
Regards Lee "Roger Govier" wrote: Hi I would always check for the existence of the folder first with a call to a function as below. ... ... foldername = "D:\Store Returns\Contacts\StoreNo\" If Not DirExist(FolderName) Then MkDir FolderName End If ... ... Function DirExist(FolderName) As Boolean On Error Resume Next ChDir (FolderName) If Err < 0 Then DirExist = False Else DirExist = True End If On Error GoTo 0 End Function -- Regards Roger Govier "leerem" wrote in message ... Hi All, Your assistance is required. How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a folder on SaveAs
One way is to ignore any error when you create the folder:
on error resume next mkdir "D:\store returns" mkdir "D:\Store Returns\Contacts" mkdir "D:\Store Returns\Contacts\StoreNo" on error goto 0 Here's something that Jim Rech Posted: Option Explicit Declare Function MakePath Lib "imagehlp.dll" Alias _ "MakeSureDirectoryPathExists" (ByVal lpPath As String) As Long Sub Test() MakeDir "c:\aaa\bbb" End Sub Sub MakeDir(DirPath As String) If Right(DirPath, 1) < "\" Then DirPath = DirPath & "\" MakePath DirPath End Sub leerem wrote: Hi All, Your assistance is required. How would I progmatically save an excel file to a folder that doesn't yet exist. I'm writing an attachment to a code that saves a straightforward file, however when the user saves a new file (eg. a new store has opened) I need to identify that store by their Store No. with the related files stored within. So I need to save the file as for example: ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" & FileNameSave, FileFormat:=xlNormal With StoreNo being the New Folder that needs to be created and FileNameSave as the File Name. In short how do you create a folder with code. Any assistance would be much appreciated. Regards Lee -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating new folder for template charts | Charts and Charting in Excel | |||
Creating a folder | Excel Discussion (Misc queries) | |||
Creating a new folder that does not already exsit. | Excel Discussion (Misc queries) | |||
Creating a List of the files in a folder in excel | Excel Discussion (Misc queries) | |||
SaveAS to a specific folder, with operator input of file name | Excel Discussion (Misc queries) |