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

Status
Not open for further replies.

bmarquart

New Member
Joined
Jun 14, 2008
Messages
3
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

 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
This works for files - if the file exists, the folder exists!

Code:
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
 
Upvote 0
try
Code:
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
 
Upvote 0
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).

Code:
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
 
Upvote 0
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...
 
Upvote 0
Instead of resurrecting ancient threads, start your own - and refrain from unnecessary 'bumping'. Closed.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,224,590
Messages
6,179,763
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top