TransferSpreadsheet to write to desktop

[mondo3]

This command worked for me in access 2003, but doesnt work in 2007 :

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryHistory", "C:\Documents and Settings\All Users\Desktop\History.xls

How should it be changed to write to the desktop in 2007?

 

[vbaInet]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryHistory", "C:\Documents and Settings\All Users\Desktop\History.xls"

I don't know if it's a type but you're missing the closing quote.

If it was a typo, does it throw up any error? Have you tried exporting to a blank new spreadsheet?

 

[ghudson]

Isn't the user's desktop in a different location in Vista and Windows 7 than C:\Documents and Settings\All Users\Desktop\

I am still using XP at work so I cannot verify.

 

[SOS]

Isn't the user's desktop in a different location in Vista and Windows 7 than C:\Documents and Settings\All Users\Desktop\

I am still using XP at work so I cannot verify.

Yes, you are correct about that.

Vista is different from XP and Win7 is slightly different from Vista.

 

[SOS]

So because of that I would suggest using the API here to get the special folder of Desktop.

 

[ghudson]

Instead of cluttering up the user's desktop, why not let the user decide where they want to output the file? Browse [Find a directory or file]

 

[SOS]

Instead of cluttering up the user's desktop, why not let the user decide where they want to output the file? Browse [Find a directory or file]

Yeah, good idea! I like that even better. Hope the OP likes it.

 

[mondo3]

I've found that many of our users cant navigate through folders, so I use the desktop as that's pretty simple (and no complaints so far). Perhaps that api is the way to go, but i was hoping for a simpler answer. thx

 

[SOS]

I've found that many of our users cant navigate through folders, so I use the desktop as that's pretty simple (and no complaints so far). Perhaps that api is the way to go, but i was hoping for a simpler answer. thx

Simple is not the norm when it comes to making things "user friendly." The more user friendly you make something, the harder it is to code and the less code you use the less user friendly it usually is. Just a general rule of thumb.

 

[gemma-the-husky]

I see users with so much stuff on their desktops you wouldn't believe.

you can only take them so far, cant you!


============
You can take a horse to water, but a pencil must be lead

 

[SOS]

you can only take them so far, cant you!

And some of them I'd like to lead out of the building as they just don't seem to get it after going over and over and over again about things.

 

[vbaInet]

I see users with so much stuff on their desktops you wouldn't believe.

you can only take them so far, cant you!


============
You can take a horse to water, but a pencil must be lead

If you were overseeing the network you could hide most their documents and make them panic for a while, then maybe they would take a hint! haha -evil laugh-

 

[dkinley]

The best and simplest answer I ever used was the Environ function ...

Environ("USERPROFILE") & "\Desktop\FileName.xls"
or
Environ("USERPROFILE") & "\Desktop\" & sOutputName


I included the second one because I normally dynamically create a file name that normally includes the day's date in case their desktop is cluttered up.

-dK

EDIT: You don't need tgo substitute anything for "USERPROFILE" - this is a reference to the current user logged into the machine. I know this works for XP ... unsure if MS kept everything the same for later OS versions.

 

[vbaInet]

Simple is not the norm when it comes to making things "user friendly." The more user friendly you make something, the harder it is to code and the less code you use the less user friendly it usually is. Just a general rule of thumb.

You could create a folder on the user's desktop at least and send all files from your db there.