Get user's Documents path?

[EdNerd]

I'm fairly experienced with Excel VBA, but I'm just beginning the adventure with Access VBA.

In Access 2007, I'm writing code for a form button to export SQL results into Excel 2007 using DoCmd.OutputTo. It all works as I test it on my machine.

When the project is completed, I intend to split the db. That then begs the question of how to specify the OutputFile string into the user's My Documents folder.

The front end will be on a server. The user will open the forms using a Windows 7 computer. I can specify a file name -- but what can I use to grab the path to that user's My Documents folder to build a string to save this output file?

Ed

 

[Isskint]

Not tried this in A07 but try, Environ$("USERPROFILE") & "My Documents". This will return the full path to the MyDocuments folder

 

[EdNerd]

It works on my XP Pro machine with AC2007. I'll have to wait to try it on a Win7 machine.

Thank you!! I've used USERNAME before, but didn't know about USERPROFILE. Would've saved me some time!

Ed

 

[Isskint]

You could also try some Wscript. Have a look at this code.

Function GetSpecialFolderNames()
Dim objFolders As Object
Set objFolders = CreateObject("WScript.Shell").SpecialFolders
MsgBox objFolders("desktop")
MsgBox objFolders("startmenu")
MsgBox objFolders("favorites")
MsgBox objFolders("mydocuments")
End Function

 

[DavidAtWork]

Can you not let the user specify where the file should be output to, this can be achieved simply by leaving the Docmd.OutputTo output file argument blank and the user will be prompted where to save
David

 

[EdNerd]

Yes, I saw that when I first used this method. But that's what prompted me to ask about this.

And just so I'm sure I understand -- when the user opens this form from a networked server, the form is inside the user's operating system and will return the user's path? Not the server's path?

Ed

 

[DavidAtWork]

When you leave the output file argument blank, this opens a dialogue box asking the user to specify where to save and the user is free to save anywhere, either on the network or in his/her myDocs folder
David

 

[EdNerd]

When you leave the output file argument blank, this opens a dialogue box asking the user to specify where to save and the user is free to save anywhere, either on the network or in his/her myDocs folder
David

Understood. In this case, my users would save these files on their local machines, and most of them in the default location.

If I can grab that default location, I can (1) create a Reports folder inside that directory to store any reports, at least initially, and (2) set an Excel object to the new workbook to manipulate it (add rows, text, formatting, etc.).

Ed

 

[DavidAtWork]

This is doable by first checking for the existance of a folder, if not exists then create it and then export file.
David

 

[EdNerd]

This is doable by first checking for the existance of a folder, if not exists then create it and then export file.
David

Yes - I've done that before in my Excel projects using XP.
My biggest questions for this Access project were:
-- getting the default location in a Windows 7 environment?
-- does this work from a split db across the network?
Those are new variables for me here.

Ed

 

[DavidAtWork]

use this to check for existance of a folder
If Dir("C:/MyDocuments/MyExcelFolder", vbDirectory) = "" Then
MkDir "C:/MyDocuments/MyExcelFolder"
End If
Now you can use this path to export and then later open the files and modify
David

 

[EdNerd]

use this to check for existance of a folder
If Dir("C:/MyDocuments/MyExcelFolder", vbDirectory) = "" Then
MkDir "C:/MyDocuments/MyExcelFolder"
End If
Now you can use this path to export and then later open the files and modify
David

Yes, I've done that lots of times.

I'm not sure I understand everything you're trying to communicate to me, though. You seem very reluctant to use the Environ$ method to get the UserProfile or UserName. But I will only have permissions to the profile level, and nothing between C: and there is available to me. So I do need that string, yes?? Or there's something you're trying to tell me that I'm not getting??

Ed

 

[Isskint]

use this to check for existance of a folder
If Dir("C:/MyDocuments/MyExcelFolder", vbDirectory) = "" Then
MkDir "C:/MyDocuments/MyExcelFolder"
End If
Now you can use this path to export and then later open the files and modify
David

David, whilst that works fine at identifying the existance of (and creating) a folder, it would not help IF the user has moved their default location for MyDocuments. That is why Ed was asking for a way to identify the path to that folder.
I would be tempted to go with your suggestion of leaving the Output To argument blank, prompting Windows to produce the SaveAS dialog. The only decision to be made by Ed is whether it is acceptable to have files saved wherever the user 'feels' at the time OR if he - the developer - needs greater control over the storage (for retrieval purposes say).

Ed, I have just tried the Environ$ method in an A07 split DB and that works fine.

 

[EdNerd]

Thank you, Isskint! I appreciate the boost.
And thank you, DaveAtWork, for the info.

Ed