Export a report to the current desktop in pdf format

[neelsfer]

I would like to always export a particular report as a pdf document to the current "desktop" of that specific PC, without hardcoding the "user" of that computer in the vba. My current vba is:

DoCmd.OutputTo acOutputReport, "Drugusage", acFormatPDF, , True

Any suggestions please

[Seth Schrock]

I don't know if this works in VBA, but in windows scripting the %username% takes the place of the user name in the file path. It might be something to look into.

Edit: If this is something that will have to work across Win XP & 7, just know that the file path is different for each OS. XP uses Documents and Settings while 7 uses Users. I'm not sure how to tell the database which path to use. I normally make a folder on the C:\ drive and then make a shortcut on the desktop to that folder if I need to. It simplifies the VBA end.

[neelsfer]

It will be for different windows 7 machines only.

[Seth Schrock]

Okay. I don't have a test database to work with, but you could try the following:

DoCmd.OutputTo acOutputReport, "Drugusage", acFormatPDF, "C:\Users\%username%\Desktop\Drugusage.pdf", True

If that doesn't work I have another idea, but lets start with that for now.

--**DOES NOT WORK**-- Moderators can remove post.

[Seth Schrock]

I remembered where I had used the DoCmd.OutputTo in one of my databases so I tested my theory on that and what I had in my previous post didn't work. So I tested my other idea and it did work. Here it is:

Dim strUserName As String, strPath As String
 
strUserName = Environ("username")
strPath = "C:\Users\" & strUserName & "\desktop\Drugusage.pdf"
 
DoCmd.OutputTo acOutputReport, "Drugusage", acFormatPDF, strPath, True

I had to manually put in your information so you might check that I have typed everything correctly.

[neelsfer]

Thx a Seth it works great !!! I have also tried it for a database of mine

[Seth Schrock]

Just curious, are you working with Neelsfer on this?

[neelsfer]

Seth you are a star. It is working as i expected it to work

[Seth Schrock]

Not a problem neelsfer. It was actually exciting for me because this is one of just a few times where I have been able to take pieces from other puzzles and put them together and make a whole new picture :) I have used different part of my code in separate databases, but never together so it was nice to see it all come together so nicely.

[zmbd]

YEA Seth!
It was only a matter of time!

As for the environment variable: "%USERPROFILE%\Desktop"
It has been this since windows97 and continues thru windows7. I have not had a hand's on with windows8. I typically use this to place a document in the users' document folder.

As for a longer term solution (and something I've considered moving to): http://msdn.microsoft.com/en-us/libr...ffice.10).aspx I believe that ADezii will have a better handle on that method.

[NeoPa]

FYI: CJFer was a temporary account Neels (neelsfer) used when his main one was temporarily unavailable.

[NeoPa]

It would be harsh to reset the Best Answer, but I would suggest Seth remember Z's solution for future use. It is preferable, and more reliable, as it doesn't presuppose the user's profile is under C:\Users (Which actually reflects a relatively recent change in fact - Windows7 and forward only).

[Seth Schrock]

I skimmed the website enough to know that I will have to go back when I have more time, so I have put it in my favorites to make it easier to find and also reference the next time I want to do something like this (which might be soon).