Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
Hi,
I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ . However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
You could use an error trapper as mine that I use to backup from current
directory Sub Backup() 'kept in personal.xls & assigned to toolbar button On Error GoTo BackupFile MkDir CurDir & "\Backup" BackupFile: With ActiveWorkbook MyWB = .Path & "\BACKUP\" & .Name .SaveCopyAs MyWB .Save End With End Sub "Jeff Marshall" wrote in message ... Hi, I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ . However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
Jeff,
Two possibilities. 1. Use DIR to test, and FileSystemObject to create it Dim myDir, myFile myFile = "C:\Billing\Invoices\SepInv\" myDir = Dir(myFile) If myDir < "" Then MsgBox "Directory already exists" Else myDir = CreateObject("Scripting.FileSystemObject").createF older(myFile) End If 2. Use FileSystemObject for both Dim myDir, myFile myFile = "C:\Billing\Invoices\SepInv\" myDir = CreateObject("Scripting.FileSystemObject").FolderE xists(myFile) If myDir = True Then MsgBox "Directory already exists" Else myDir = CreateObject("Scripting.FileSystemObject").createF older(myFile) End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jeff Marshall" wrote in message ... Hi, I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ . However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
Here are three different approaches gathered from previous postings.
----------------------------------------------------------- Public Function IsDir(ByRef strPath As String) As Boolean '' Does the directory exist? If Not Dir(strPath, vbDirectory) = vbNullString Then IsDir = True End Function ----------------------------------------------------------- Sub MakeDirectory() Dim Dirname As String Dim fs As Object Set fs = CreateObject("Scripting.FileSystemObject") Dirname = "C:\Data" If Not fs.FolderExists(Dirname) Then fs.CreateFolder Dirname ' your code Else ' your code End If End Sub Courtesy of a posting by Ron De Bruin ----------------------------------------------------------- An easy approach is to just attempt to create the folder. If it exists, the attempt fails, so you ignore the error - otherwise it is created: On Error Resume Next MkDir ThisWorkbook.Path & "\" & "Temp" On Error goto 0 Tom Ogilvy ----------------------------------------------------------- HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- On Sun, 28 Sep 2003 10:52:16 -0400, "Jeff Marshall" wrote: Hi, I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ . However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
Jeff,
I went to Google. You should find this post by Stephen Bullen to be very helpful. http://groups.google.com/groups?q=ex...%40mars&rnum=1 I will save you the trouble and simply paste it here. Best regards, Kevin Hi Rajko, I have a problem if I want to write a file to a folder which not exist. How can I check if folder exists, then create it and write a file. There are actually four outcomes to consider: 1. The folder exists - we're OK 2. The folder doesn't exist and there is no file of the same name - we can create the folder and be OK 3. The folder doesn't exist and there IS a file of the same name - we can't create the folder 4. A disk error ocurred during 1-3. I might be tempted <g to write a routine for it like: Sub Test() MsgBox CheckFolder("C:\Test") End Sub Function CheckFolder(sPath As String) As Boolean On Error GoTo ERR_DISKERROR 'Dir checks files as well as directories If Dir(sPath, vbDirectory) < "" Then 'Is it a directorythat we found, or a file? If GetAttr(sPath) And vbDirectory Then 'It's a directory, so we're OK CheckFolder = True Else 'What to do if you can't create the directory End If Else 'The directory doesn't exist, nor does a file of the same name MkDir sPath CheckFolder = True End If Exit Function ERR_DISKERROR: 'Handle the error how you like, and terminate the function 'examples a ' Drive doesn't exist/not accessible (e.g. A:/Test without a disk in) ' Invalid path string (e.g. contains invalid characters) ' Disk full when creating folder ' Disk not writable ' Path too long ' etc. End Function Regards Stephen Bullen Microsoft MVP - Excel http://www.BMSLtd.co.uk "Jeff Marshall" wrote in message ... Hi, I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ . However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
You macro will raise an error if it tries to make the folder and it already
exists. However, it is easiest to just trap the error and move on. On Error Resume Next mkdir "C:\Billing\Invoices\SeptInv" On Error goto 0 ' at this point you know it exists. this assumes "C:\Billing\Invoices" exists. If you can't assume that On Error Resume Next mkdir "C:\Billing" mkdir "C:\Billing\Invoices" mkdir "C:\Billing\Invoices\SeptInv" On Error goto 0 -- Regards, Tom Ogilvy Jeff Marshall wrote in message ... Hi, I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ . However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a folder exists
Hi All
Can I add to this by asking once you have established if a folder exists, can you do the same for a file that may or may not be in that folder? For example if "MyFile" was found to be in the folder already, could the new file be saved as "MyFile_02" and then the next time the procedure is run "MyFile" would be saved as "MyFile_03" and so on. Thanks Mick "Tom Ogilvy" wrote in message ... You macro will raise an error if it tries to make the folder and it already exists. However, it is easiest to just trap the error and move on. On Error Resume Next mkdir "C:\Billing\Invoices\SeptInv" On Error goto 0 ' at this point you know it exists. this assumes "C:\Billing\Invoices" exists. If you can't assume that On Error Resume Next mkdir "C:\Billing" mkdir "C:\Billing\Invoices" mkdir "C:\Billing\Invoices\SeptInv" On Error goto 0 -- Regards, Tom Ogilvy Jeff Marshall wrote in message ... Hi, I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ .. However before the folder is made I don't know how to; 1. Test if the SeptInv folder has already been made and 2. Once it has been made that my VBA macro doesn't try to make it again. Thanks in advance for any help. Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if file exists | Excel Discussion (Misc queries) | |||
How to check if a file exists in an ftp folder | Excel Discussion (Misc queries) | |||
Checking to see if Folder exists | Excel Programming | |||
Test to see if a worksheet exists thanks, Chip | Excel Programming | |||
easy way to test if a Named Range exists | Excel Programming |