Run-time error '3044'

[MNM]

Greetings,
I have a BE database, that when opened, opens a form for saving the results of a query to a text file on the desktop.
It works fine, if the full path is entered.
The problem is, I want this saved on any users' desktop.
I did some digging and found the %userprofile% variable, which when used, gives me the error.
I understand this should work in both Windows XP and Windows 7, which are the environments the full DB will operate in.
So far the "EXPORT" button on the form has the following for the code:

Private Sub BTN_Export_Click()
 DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", "C:\Users\Mark N. McAllister\Desktop\PubComExp.txt"
End Sub

When I tried this:

Private Sub BTN_Export_Click()
 Dim strPath As String
 strPath ="%userprofile%\desktop\PubComExp.txt"
 DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", strPath
End Sub

the error occurs.

What's wrong?

TIA
MNM

 

[spikepl]

What is the error message of runtime error 3044? (We do not have the error texts engraved on the inside of our eyelids)?

 

[gemma-the-husky]

this path will be taken as a literal, and will not exist. you need to substitute the %userprofile% bit for a real folder

strPath ="%userprofile%\desktop\PubComExp.txt"

you need some way of replacing this

%userprofile% with "C:\Users\Mark N. McAllister"

 

[gemma-the-husky]

see if environ("homepath") helps

"C:\" & environ("homepath") & "\desktop\PubComExp.txt"
might be close

there are about 50 environ settings, but not an exact one for desktop.

you can use either environ(x) or environ("setting")

 

[MNM]

What is the error message of runtime error 3044? (We do not have the error texts engraved on the inside of our eyelids)?

Run-time error '3044':
'%userprofile%\desktop\' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Upon Debug, this line is highlighted:
DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", strPath

I want it to export the file to the Desktop of the Current User, regardless of who is logged on to the workstation.

Thanks,
MNM

 

[MNM]

see if environ("homepath") helps

"C:\" & environ("homepath") & "\desktop\PubComExp.txt"
might be close

there are about 50 environ settings, but not an exact one for desktop.

you can use either environ(x) or environ("setting")

Yes, the environ("homepath") worked! I need to test it on-site.

I read there's a difference between 2003 & 2010; the DB is saved as 2003, but built in 2010.
The main workstations will be XP w/2003.
If there's no problem, a big Thanks for Gemma-the-Husky

Mark

 

[MNM]

Sorry Gemma-the-Husky.
In XP w/ A2003, I get:

"Compile error
Project or library was not want found."

The "Environ" was highlighted.

Anyone know a Plan B?

I ran across this on another site:

'This function is used to allow the use of the Environ function in Access 2003
    
Public Function Environ(Expression)
On Error GoTo Err_Environ
        
    Environ = VBA.Environ(Expression)
    
Exit_Environ
    Exit Function
    
Err_Environ
    MsgBox Err.Description
    Resume Exit_Environ
    
End Function

I added this in A2010, but it erred out with something like 'Expecting value not function'.

Ed. I added the code as a module in A2003, but got the same message about '...not found.'

MNM

 

[MNM]

Update:
I noticed ":" missing in the code (2 places).
Now I get the 3044- invalid path error.
Clearly not a solution

 

[MNM]

Greetings,
While scrounging around the web, I came across something quite interesting.
On http://windowssecrets.com/ the user HansV posted some modules and a form in the thread here;
http://windowssecrets.com/forums/showthread.php/111528-GetSaveAsFileName-in-Access-(2003)
This allows the user to open the SaveAs dialogue box.
I've decided to try this route, as it seems the path needs to be hard-coded into the transfer method.

 

[gemma-the-husky]

I found this problem with environ()

environ works in vba, but won't work in queries any more. (after A2003, I think). Must be to do with the SQL engine.

You can use a function instead.

in the query put the column definition as

=getDesktop()

and have a function to set the string value.

---

 Public Function getDesktop() as string       
    getDeskTop = [B]"C:\" & environ("homepath") & "\desktop\PubComExp.txt"[/B] 

 (or maybe)
     getDeskTop = [B]"C:\" & vba.environ("homepath") & "\desktop\PubComExp.txt"[/B] 

 End Function

if it still doesn't work, then there is something wrong with your references, but I am surprised.

 

[MNM]

Gemma-the-husky,
I got a good solution from a user on another forum.

Dim strPath As String
Dim sUserProfile As String
sUserProfile  = CreateObject("WScript.Shell").ExpandEnvironmentStrings("%UserProfile%")
strPath = sUserProfile & "\desktop\PubComExp.txt"
DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", strPath

This works. Although, I need to replace 'desktop' with the Japanese Katakana equivalent for it to work in the field (on Japanese OS machines).
Thank you, and everyone else for your assistance.
Mark