Using Environ("userprofile") but getting "File could not be found" error message

[wardw]

I'm fairly new to VBA, and I'm learning how to build a macro with a dialog box that prompts for a Word document to open, opens the document, makes some changes, and saves it. The dialog has a dropdown list of available documents.

When I choose my document in the dialog (in this case it's Latest Report.docx) and click OK, I get a message:

"This file could not be found. ("C:\Users\...\WWME\Latest Report.docx")".

The portion of the dialog box code that points to the document is:


Documents.Open FileName:=Environ("userprofile") & "Documents\WMME\" & cmbSourceDocument.Value

I've checked my Windows userprofile, and it's correct. A file with the name Latest Report.docx is in the correct directory. I tried just citing the literal path to the document like this:


Documents.Open FileName:="C:\Users\Ward\Documents\WMME\Source Document.dotx"

but the same error appears. How can I direct the macro to the correct document?

I'm using Visual Basic 6.5, Word 2007, on a Windows 10 PC.

[SamT]

Try

Dim Ufolder as String
Ufolder = Environ("UserProfile") & "\" & Environ("UserName") & 's Documents\"
'OR
Ufolder = Environ("UserProfile") & "\" & "My Documents\"

Documents.Open UFolder & "WMME\Source Document.dotx"
I can't really tell because I have customized my System Folder structure so much. On my computer, the "My Documents" folder isn't even on the same hard drive as the Windows operating system and the Program Files folder. so I don't even have a "My Documents" any where in the Documents and Settings folder. I do have a "SamT's Documents" there under my User Name.

BTW, the UserProfile argument will usually return something like "C:\Documents and Settings\Ward"

Put this in a blank Worksheet's code page and run it to see all the Environ variables on your computer

Sub ListAllEnvironStrings()
Dim envString As String
Dim i As Long

For i = 1 To 100
envString = Environ(i)
If envString <> "" Then Cells(i, "A").Resize(1, 2) = Split(envString, "=")
Next

End Sub
If you actually get 100 Environ Strings then double the max value of i. (this should never happen.) I only have 28 on my single user Windows XP machine.

[Bob Phillips]

Maybe


Documents.Open FileName:=Environ("userprofile") & Application.PathSeparator & "Documents\WMME\" & cmbSourceDocument.Value

[wardw]

Thanks for your reply, SamT. I tried running the Environ variables macro you suggested, but VBA didn't like the "Cells" word in it; the macro may be for Excel, not Word. But I did run another macro that worked, and it listed USERPROFILE=C:\Users\Ward

Trying the first line you suggested got a syntax error, and the second one produced the same "File not found" message (I don't have a My Documents directory; just Documents).

[wardw]

Thanks, SLD; your suggested correction does work. There seems to be a problem with just omitting the path separator, or trying to specify it as "\". Inserting

& Application.PathSeparator & satisfies VBA, though.

[Bob Phillips]

You would expect omitting it to be a problem, that would then be a (non-)path of

C:\Users\WardDocuments\WMME\Source Document.dotx