save as to specific directory (different paths)

[daniels012]

I need to hit a button. It then saves the file with the name I choose to a directory that could be different on each different computer, but the last part of the path is always the same "\dropbox\clients\".

Is this possible to find the wildcard of a path using code?

Something like:

If Dir(DBPath, vbDirectory) <> "" Then
                ActiveWorkbook.SaveAs DBPath & myFileName

DBPath = ?? Whatever wildcard code we can create
myFileName = Worksheets("Estimating").Range("A10").Value & ".xls"

Even if It could bring up the dialog box to save the file, that would be great. Then they can HIT ok to make sure it is going to the right place.

Thank You,
Michael D

 

[Joe4]

Even if It could bring up the dialog box to save the file, that would be great. Then they can HIT ok to make sure it is going to the right place.

Perhaps this post will help with that?
http://www.mrexcel.com/forum/showthread.php?t=303724

 

[daniels012]

I think I have the part of SaveAs figured out, it is the special directory I am looking for that I need help.
Unless, this code opens the SaveAs dialog and puts the proper name in? If it does that, I will try it.

Michael D

 

[Joe4]

That is a bit tricky, not exactly sure how to do that.

I think it may be possible to search a computer and find where the "\dropbox\clients\" directory exists on a computer. Then we could capture that and incorporate it into your file naming code. I am just not sure exactly how to do that.

I will poke around and see what I can find out, and if I cannot figure it out, may try to enlist some expert help from some other friends.

 

[Joe4]

I found an interesting discussion on the topic where a very knowledgeable Excel expert weighs in (Ivan Moala). It probably isn't the answer you wanted to hear!
http://www.ozgrid.com/forum/showthread.php?t=16098

Question, though. Why are the paths different?

Is it simply because different members have different drive mappings for the same network drive (i.e. Person A has network drive ABC mapped to drive "F" and Person B has network drive ABC mapped to "G")? If so, can you use UNC file path naming convention instead, which will remove differences due to the same network drive being mapped to different drive letters on different computers?

 

[shg]

Assuming the file is to be saved on the user's computer, it's easy enough to find whether there is a directory C:*\dropbox\clients. Is that sufficient? Might there be more than one?

 

[daniels012]

The reason there may different paths, is because when installed on an XP machine, or a Windows 7 machine or a Vista machine, directories are different.

shg,
Could it be that simple?
Dropbox is on each person's computer. It sync's and updates periodically. So how would I write this in code to get that path?
maybe:

DBPath = C:*\dropbox\clients\

Michael D

 

[Joe4]

Based on what shg said and your latest response, I think that makes a big difference, if they are in different folders, but all under the computer hard-drive ("C"). Based on your original question, I thought they could be on different network drives (which is why I asked that last question).

If it is always on the computer's C drive somewhere, that might make it do-able. Try messing around with the code shg gave you and see if you can make it work.

 

[shg]

Time out! That was not code, it was a question!

The question was, could one user have two directories that match the pattern C:*\x\y, like C:\bob\joe\x\y and C:\jim\ann\x\y?

 

[Joe4]

Time out! That was not code, it was a question!

Whoops, I was actually looking at the code in Jeff's last post.
Sorry about the confusion.