VBA-check if folder/file exists, create them if not

[bmarquart]

Is there an easy way to check if a folder (c:\timecards) and file (timecards.xls) exists. if true continue with sub, if false then create them and continue with sub?

Private Sub btncontinue_Click()
Dim ShName As String
ShName = ActiveSheet.Name

'check if folder\file exists here, create if not and continue

Workbooks.Open filename:= _
"C:\TimeCards\timecards.xls"
Windows("BMtime.xls").Activate
ActiveWindow.WindowState = xlNormal
Sheets(ShName).Select
Sheets(ShName).Copy after:=Workbooks("timecards.xls").Sheets(1)
ActiveWorkbook.Save
ActiveWindow.Close
range("A4:P20,A24:L40,A46:P62,A66:L82").Select
range("A66").Activate
Selection.ClearContents
range("A4").Select

End Sub

 

[xenou]

This works for files - if the file exists, the folder exists!

Sub TestIt()
If myFileExists("C:\Temp\Test.xls") Then
msgbox "Exists!"
Else
msgbox "No Can Do."
End if
'-------------------------------------------------------
Function myFileExists(ByVal strPath As String) As Boolean
'Function returns true if file exists, false otherwise
    If Dir(strPath) > "" Then
        myFileExists = True
    Else
        myFileExists = False
    End If
End Function

 

[jindon]

try

Sub btncontinue_Click()
Dim myFile As String, myFolder As String
myFolder = "C:\TimeCards"
myFile = myFolder & "\timecards.xls"
If Not IsFolderExixts(myFolder) Then
    CreateObject("Scripting.FileSystemObject").CreateFolder myFolder
End If
If Not IsFileExists(myFile) Then
    MsgBox "No such file in the folder"
    Exit Sub
End If
Set wb = Workbooks.Open(myFile)
' Your code here
End Sub
 
Function IsFolderExists(txt As String) As Boolean
    IsFolderExists = _
        Createobject("Scripting.FileSystemObject").FolderExists(txt)
End Function
 
Function IsFileExists(txt As String) As Boolean
    IsFileExists = _
        CreateObject("Scripting.FilesystemObject").FileExists(txt)
End Function

 

[xenou]

Here's both a folder and file function using the FSO object... (you could import those methods right into your sub pretty easily of course).

Sub TestIt()
Dim s As String
Dim b As Boolean
    s = "C:\TEMP": b = CheckForFolder(s): Debug.Print s & "|" & b
    s = "C:\TEMP\": b = CheckForFolder(s): Debug.Print s & "|" & b
    s = "C:\TEEMP": b = CheckForFolder(s): Debug.Print s & "|" & b
    s = "C:\Temp\test.xls": b = CheckForFile(s): Debug.Print s & "|" & b
    s = "C:\TEMP\Test.xls": b = CheckForFile(s): Debug.Print s & "|" & b
End Sub
'------------------------------------------------------------------
Function CheckForFolder(ByVal strPathToFolder As String) As Boolean
'To use this fn you must set a reference for Scripting Runtime
'--------------------------------------------------
'1.  In the VBE window, Choose Tools | References
'2.   Check the box for Microsoft Scripting Runtime
'--------------------------------------------------
Dim fso As FileSystemObject
Dim blnFolderExists As Boolean

    'Create object
    Set fso = New FileSystemObject
    CheckForFolder = fso.FolderExists(strPathToFolder)
    If Not fso Is Nothing Then Set fso = Nothing

End Function
'------------------------------------------------------------------
Function CheckForFile(ByVal strPathToFile As String) As Boolean
'To use this fn you must set a reference for Scripting Runtime
'--------------------------------------------------
'1.  In the VBE window, Choose Tools | References
'2.   Check the box for Microsoft Scripting Runtime
'--------------------------------------------------
Dim fso As FileSystemObject
    
    Set fso = New FileSystemObject
    CheckForFile = fso.FileExists(strPathToFile)
    If Not fso Is Nothing Then Set fso = Nothing

End Function

 

[bmarquart]

Thanks Alexander! Thanks Jindon! You've both been a big help.

 

[VBABEGINER]

Hi,

On this I do have similar set of query..

I do have "Timesheet Tracker" workbook, which have user form and some fields to capture the daily activity data.

In current scenario, I do have 7 user who will access "Timesheet Tracker" workbook.

This "Timesheet Tracker" workbook I will kept in one path.
What I want to do is,
When user will open this "Timesheet Tracker" workbook, on specific path code will check, whether on that path application.user workbook is exist or not. If not, then create new workbook and named that workbook as application.user name

and save the respective data.

Can you please provide me the code for this...

 

[VBABEGINER]

hi please respond on this...

 

[Macropod]

Instead of resurrecting ancient threads, start your own - and refrain from unnecessary 'bumping'. Closed.