Excel VBA Check if directory exists error

Question

I have a spreadsheet that upon clicking a button will duplicate itself by copying pasting everything to a new workbook and save the file with a name that is dependent upon some variable values  taken from cells on the spreadsheet   My current goal is to get it to save the sheet in different folders depending on the name of client name  cell value held in variable   while this works on the first run  I get an error after   The code checks if the directory exists and creates it if not  This works  but after it is created  running it a second time throws the error      Runtime Error 75 - path file access error    My code   Sub Pastefile    Dim client As String Dim site As String Dim screeningdate As Date screeningdate   Range  b7   Value Dim screeningdate text As String screeningdate text   Format  screeningdate   yyyy -mm -dd   client   Range  B3   Value site   Range  B23   Value  Dim SrceFile Dim DestFile  If Dir  C  2013 Recieved Schedules   amp       amp  client    Empty Then     MkDir  C  2013 Recieved Schedules   amp       amp  client End If  SrceFile    C  2013 Recieved Schedules schedule template xlsx  DestFile    C  2013 Recieved Schedules    amp  client  amp       amp  client  amp       amp  site  amp       amp  screeningdate text  amp    xlsx   FileCopy SrceFile  DestFile  Range  A1 I37   Select Selection Copy Workbooks Open Filename          C  2013 Recieved Schedules    amp  client  amp       amp  client  amp       amp  site  amp       amp  screeningdate text  amp    xlsx   UpdateLinks         0 Range  A1 I37   PasteSpecial Paste  xlPasteValues Range  C6   Select Application CutCopyMode   False ActiveWorkbook Save ActiveWindow Close  End Sub   You ll have to excuse my lack of knowledge in this area  I am still learning  I have a very strong feeling it has something to do with the directory checking logic  as when the error is thrown the MkDir line is highlighted

User · Answer

This is the cleanest way    BY FAR  Public Function IsDir s  As Boolean     IsDir   CreateObject  quot Scripting FileSystemObject quot   FolderExists s  End Function

User · Answer

Use the FolderExists method of the Scripting object   Public Function dirExists s directory As String  As Boolean     Dim oFSO As Object     Set oFSO   CreateObject  Scripting FileSystemObject       dirExists   oFSO FolderExists s directory  End Function

User · Answer

You can replace WB parentfolder with something like  C      For me WB parentfolder is grabbing the location of the current workbook  file des folder is the new folder i want   This goes through and creates as many folders as you need           folder1   Left file des folder  InStr Len WB parentfolder    1  file loc                Do While folder1  lt  gt  file des folder             folder1   Left file des folder  InStr Len folder1    1  file loc                    If Dir file des folder  vbDirectory       Then       create folder if there is not one                 MkDir folder1             End If         Loop

User · Answer

I ended up using   Function DirectoryExists Directory As String  As Boolean     DirectoryExists   False     If Len Dir Directory  vbDirectory    gt  0 Then         If  GetAttr Directory  And vbDirectory    vbDirectory Then             DirectoryExists   True         End If     End If End Function   which is a mix of  Brian and  ZygD answers  Where I think  Brian s answer is not enough and don t like the On Error Resume Next used in  ZygD s answer

User · Answer

If Len Dir ThisWorkbook Path  amp    YOUR DIRECTORY   vbDirectory     0 Then    MkDir ThisWorkbook Path  amp    YOUR DIRECTORY  End If

User · Answer

To check for the existence of a directory using Dir  you need to specify vbDirectory as the second argument  as in something like   If Dir  C  2013 Recieved Schedules   amp       amp  client  vbDirectory       Then   Note that  with vbDirectory  Dir will return a non-empty string if the specified path already exists as a directory or as a file  provided the file doesn t have any of the read-only  hidden  or system attributes    You could use GetAttr to be certain it s a directory and not a file

User · Answer

To be certain that a folder exists  and not a file  I use this function   Public Function FolderExists strFolderPath As String  As Boolean     On Error Resume Next     FolderExists     GetAttr strFolderPath  And vbDirectory    vbDirectory      On Error GoTo 0 End Function   It works both  with   at the end and without

[excel] Excel VBA Check if directory exists error

Examples related to excel

Examples related to vba

Examples related to file

Examples related to directory