testing for folders, templates, and files

Martin Perera

Board Regular
Joined
Jan 17, 2005
Messages
147
Hiya experts

I am trying to come up with a macro to test whether a folder (Finance) in MyDocuments exists, whether a template (Finance.xlt) and whether a file with starting with 2005.....xls exists in specfic folders. If they do I wish to stop a onopen code from running.

one option would be to check the DefaultPath for a folder called "Finance" and or TemplatePath to check for Finance.xlt. but i would have to pass the path via a variable




Code:


Sub check()

Dim MyPath, MyFolder
MyPath = Application.DefaultPath ' Set the path.

MyFolder = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyFolder <> "Finance" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyFolder <> "." And MyFolder <> ".." Then
' Use bitwise comparison to make sure MyFolder is a directory.
If (GetAttr(MyPath & MyFolder) And vbDirectory) = vbDirectory Then
Debug.Print MyFolder ' Display entry only if it represents a directory.
If MyFolder = "Finance" 'should stop if find 'Finance'
End If
MyFolder = Dir ' Get next entry.
End If
End If
Loop

'test for .xlt in Templatepath

MyTemp = Dir("Application.templatePath& "\" & Finance.xlt")
If(MyFolder = "Finance" And MyTemp = Finance.xlt) Then
MsgBox "SetUp has already run please open Frontsheet to continue"

End Sub

or something like that to check for the folder, then check for the template. Never used Dir before but I know it can check for files and folders not handling the variable though.

Does any of this make sense to you. i think I am getting my IF's mixed up a bit.

this was off the top of my head lol

I have no idea how to attempt finding the first part of file name (pos some sort of string variable)

If any of this makes sense can anyone help.....
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,122,476
Messages
5,596,380
Members
414,063
Latest member
N_Bates

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
Top