VBA to find a directory

[daniels012]

Is there a way to use a wildcard to find a directory?

Here is my situation. I have 4 users. All have different Operating systems, XP, Vista, Windows 7. So each has a different spot for there folders and file names.

On each machine, I know the last part of everyone's directory will be: \dropbox\clients
I have a button now that saves to the desktop:
[VBA]myFileName = Worksheets("Estimating").Range("A10").Value & ".xls"
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & myFileName
MsgBox "Be sure to go to the desktop, move your saved file into to dropbox. "[/VBA]
I would love to have VBA that would send the file to each person's dropbox folder. Is this possible?

Thank You for any help with this!
Michael

[Kenneth Hobs]

[VBA]Sub test()
Dim DTAddress As String

DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

If Dir(DTAddress, vbDirectory) = Empty Then MsgBox DTAddress, vbCritical, "Does Not Exist"
DTAddress = DTAddress & "dropbox"

If Dir(DTAddress, vbDirectory) = Empty Then
MsgBox DTAddress, vbCritical, "Does Not Exist - Being Created"
MkDir DTAddress
End If

DTAddress = DTAddress & Application.PathSeparator & "clients"
If Dir(DTAddress, vbDirectory) = Empty Then
MsgBox DTAddress, vbCritical, "Does Not Exist - Being Created"
MkDir DTAddress
End If
End Sub
[/VBA]

[daniels012]

Kenneth,
Thank You so much for the quick response.
Let me explain a little further. I am almost positive each person will have dropbox on their computers. If for some reason they don't then I would like the vba to save to desktop.
So that being said can I use a section of your code to do this? Let me explain in plain text:

If you find any path that ends in "\dropbox\Clients\" name it "DTAddress" then
ActiveWorkbook.SaveAs DTAddress & myFileName
If not then,
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & myFileName

Maybe you have done this with the code you provided? I just did not understand it.
Please let me know,
Michael

[Kenneth Hobs]

Check for the dir with the Dir() command as shown in my example code.

You can create the dir(s) using the MkDir command as I demonstrated.

You can use the same variable name in any route you go with providing you make sure that the dir actually exists.

[daniels012]

ok,
So can I do this?
[VBA]DTAddress = CreateObject("WScript.Shell").SpecialFolders("*\dropbox\Clients\") & Application.PathSeparator
If Dir(DTAddress, vbDirectory) = Empty Then
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & myFileName [/VBA]

Can I use a wildcard like that?

Michael

[daniels012]

Because I did this:
?CurDir() in the immediate window
and the directory was not the same. That is why I want to find that specific directory.

Michael

[Kenneth Hobs]

No. When you check that, it won't work.

While you could search your whole computer for those folders, that could take a long time. It is best to assume a path that it might be in and then use Dir() to verify that it exists. That will be much faster.

CurDir() and Thisworkbook.Path may not provide a consistent solution. They are paths that you could check though.

[daniels012]

So you think I should try all 3 operating systems and where they put the file? As opposed to finding the path?

I know one of them is "c:\Documents and Settings\Owner\My Documents\Dropbox\Clients\"
I know another is "c:\Users\User\Dropbox\Clients\"

So you are saying put all these choices is better than searching for the last 2 sections of the path? Right?

Michael

[daniels012]

I also was trying to avoid using Users names in this. For 6 or 7 different users I have to put there name in on their laptops. I may want to do the long search if this is a possiblility?
Michael

[daniels012]

OK,
Tell me if this will work. We always retrieve this original file from a Dropbox folder called: "SurfaceSystemsFiles"

I can get this to come up in my immediate window:
Left(Application.ThisWorkbook.Path,len(Application.ThisWorkbook.Path)-19) & "Clients\"

And it gives me the correct path.

Will this work for what I want? If so how can I add this to my original code? Or even test it using some of your code?

MIchael

[Kenneth Hobs]

Try playing with this:
[VBA]Sub test()
Dim fPath As String
fPath = ThisWorkbook.Path
If Len(fPath) < 19 Then
MsgBox "Length of thisworkbook.path is: " & Len(fPath), vbInformation, fPath
Exit Sub
End If

fPath = Left(fPath, Len(fPath) - 19) & "Clients\"
If Dir(fPath, vbDirectory) = Empty Then
MsgBox fPath, vbCritical, "Does Not Exist:"
Else
MsgBox fPath, vbCritical, "Does Exist:"
End If
End Sub[/VBA]

[daniels012]

Does this look like it would work? I used some existing and some of what you gave me:
[VBA]Sub TestPath()
Dim DBPath As String, myFileName As String, DTAddress As String, ans As String
myFileName = Worksheets("Estimating").Range("A10").Value & ".xls"
DBPath = Left(Application.ThisWorkbook.Path, Len(Application.ThisWorkbook.Path) - 19) & "Clients\"
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
If Dir(DBPath, vbDirectory) <> "" Then
ActiveWorkbook.SaveAs DBPath & myFileName
Else
ActiveWorkbook.SaveAs DTAddress & myFileName
MsgBox "Be sure to go to the desktop, move your saved file into to dropbox. "
End If
ans = MsgBox("Do you want to do another Estimating sheet FOR THIS SAME CLIENT?", vbQuestion + vbYesNo, "???")

If ans = vbNo Then
ActiveWorkbook.Close
Else
EraseWorksheetLeaveAddress
End If
End Sub[/VBA]

Please let me know if I need to trim done my code or if I can shorten my code. Also let me know if you interpret this correctly.

Thank You,
Michael D

[daniels012]

Is there any reason this vba code would not work on a machine with Office 2000?

It works fine on my Office 2007 machine!

I even put the code in the immediate window and it recognized the directory as being there?

Michael