I want my Auto_open file to check to see if there is a folder and if not
create it
Something like
user = Application.username
If "//server3/jobs/user/" exists then
do something
Else
create the user folder

One way to check to see if a folder exists:

Option Explicit
Sub Auto_Open()

Dim TestStr As String
Dim FolderName As String

FolderName = "\\server3\jobs\" & application.username

If Right(FolderName, 1) < "\" Then
FolderName = FolderName & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(FolderName & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox FolderName & " not found"
Else
MsgBox FolderName & " was found"
End If

End Sub

Another way is go use File System Object.

Option Explicit
Sub Auto_Open()
Dim FSO As Object
Dim FolderName As String

FolderName = "\\server3\jobs\" & Application.UserName

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(FolderName) = False Then
MsgBox FolderName & " not found"
Else
MsgBox FolderName & " was found"
End If
End Sub



Oldjay wrote:

I want my Auto_open file to check to see if there is a folder and if not
create it
Something like
user = Application.username
If "//server3/jobs/user/" exists then
do something
Else
create the user folder

--

Dave Peterson

One way to check to see if a folder exists:

Option Explicit
Sub Auto_Open()

Dim TestStr As String
Dim FolderName As String

FolderName = "\\server3\jobs\" & application.username

If Right(FolderName, 1) < "\" Then
FolderName = FolderName & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(FolderName & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox FolderName & " not found"
Else
MsgBox FolderName & " was found"
End If

End Sub

Another way is go use File System Object.

Option Explicit
Sub Auto_Open()
Dim FSO As Object
Dim FolderName As String

FolderName = "\\server3\jobs\" & Application.UserName

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(FolderName) = False Then
MsgBox FolderName & " not found"
Else
MsgBox FolderName & " was found"
End If
End Sub

And here is yet another way to see if a folder exists...

Private Function FolderExists(PathName As String) As Boolean
On Error Resume Next
If Len(PathName) 0 Then
FolderExists = ((GetAttr(PathName) And vbDirectory) 0)
Err.Clear
End If
End Function

Here is the companion file exists funciton...

Private Function FileExists(FileName As String) As Boolean
On Error Resume Next
If Len(FileName) 0 Then
FileExists = ((GetAttr(FileName) And vbDirectory) = 0)
Err.Clear
End If
End Function

Rick