Export table to excel on Desktop 1

[thevillageinn]

Here're my issues:

1. I'm completely new to VBA programming, but I'm getting there...slowly.

2. I have a table that I want to export to an Excel spreadsheet, easy enough, but when the user clicks the "export" button, I want the default location to be on the desktop of the current user, I don't want to export it to "All Users" because that location is local to each system, and won't follow the users roaming profile. I keep thinking the %HOMEDRIVE%%HOMEPATH% sort of options are something I need to use?

(I'm using the Common Dialog Control, but if I can hard code the save as location to the desktop, and accomplish one other step prior to exporting, I am open to other options. I'm also using Access 97.)

TIA! -Dan

 

[Sawatzky]

dear Dan,

first of all you should not save any files to the desktop, when roaming profiles are used.

Every file saved there, will travel all the way from the server to the users maschine, when the user logs in and all the way back when the user logs out.

That means this would cause unnecessary traffic in your network environment and could make your network-administrator suffer.


When you are working with roaming profile in most cases the home directory is mapped to the same volume for every user, for example as h:. So you could use the path h:\data\ or things like that as default.

hth regards Astrid

 

[thevillageinn]

Astrid,
Thank you. That does help. I had not thought of that. I do understand what you mean with regard to network traffic, but this is a very small report and only 2 or two people will ever use the application, but it has to be "fool proof" so to speak.

I will give your suggestions a try.
Thanks again
-Dan

 

[KyleS]

If you decide you want to go ahead with that anyways, here is the code to do so...

FileCopy "DATABSAE PATH", "C:\Winnt\Profiles\" & Environ("username&quot & "\Desktop\METCard.mde"

Just adjust the second string to match your desktop location (I'm on NT 4.0) break it where their network name appears and insert Environ("UserName&quot and then continue with the path, having replaced the folder that shares their username with the Environ() function.

Although astrid does have a point, but there are other options as well, create a shortcut to the DB on the Network drive and copy the shortcut to the desktop and the DB to their "Personal" drive.

Hope this helps...
Kyle :

 

[KyleS]

Dan, sorry about that post, not exactly what you're looking for (OK, so I can program but I can't read ;-))

Let's try this again...

DoCmd.OutputTo acOutputReport, "REPORTNAME", acFormatXLS, "C:\Winnt\Profiles\" & Environ("username&quot & "\Desktop\REPORTNAME.xls", False


This code on the OnClick of your export button will give you what you want...

Sorry for the confusion..

Kyle

 

[thevillageinn]

Kyle,
That helps quite a bit. I figured there had to be a way to specify the user specific path in a dynamic sort of way.

-Dan

 

[thevillageinn]

Ok, I have worked on this and found that Astrid's idea "works" but it places the report on the network copy of a user's profile, meaning it wouldn't be visible to the user until they log off and back on.

I was able to use Kyle's suggestion to return the User Name and then save the report to C:\Documents and Settings\username\Desktop...it works pretty well.

Here's my usage:

CommonDialog1.FileName = "C:\Documents and Settings\" & Environ("username") & "\Desktop\UnmatchedTrx_" & FileDate & ".xls"

So, upon clicking the "Export" button, the user is prompted to fill in an input box to uniquely identify this report (FileDate) variable and then that unique identifier is added into the name of the report and the whole thing is saved by default on the users desktop. It works pretty well thus far.

Thanks to everyone who offered suggestions.
-Dan

 

[Sawatzky]

Dear Dan,

when I explained my approach, I should have benn more concrete, sorry for that.

In that network environment, you have the userprofile (with the desktop) stored in a special place most times the same place as the homedirectory, which itself is most times mapped to volume H:.
but if you create a directory on H: and not on h:\WIn nt ..... \userprofiles (have not got the exact name in mind) then you just get a 'normal' directory which you can use like any other normal directory.

let us call it H:\accessstuff
now we customize the code being provided by Kyle

DoCmd.OutputTo acOutputReport, "REPORTNAME", acFormatXLS,
"H:accessstuff\REPORTNAME.xls",
False

this creates the file in H:\ which is available immediately. (If not seen in the explorer-window, just try pressing F5.

regards Astrid

 

[thevillageinn]

Astrid,
I believe I understand what you are driving at, but on my system, on our network, traditional or not, correct or not, creating a file on the network copy of a person's profile (in our case Z:\) does not create a file on the local machine's desktop, until the user logs off and back on. The profile is only updated at log on and off. The profile that is actively used when a user is logged on is not dynamic in any way, and will not respond to items added to the network profile. This probably makes a much less secure network/multi-user environment, because a copy of a users profile is on any computer they log onto, but it's how ours was set up.

So, unfortunately, your method does not work for my application. Unless I am still not understanding what you mean. Thank you however for your contribution, and follow up.

Regards
-Dan

 

[Sawatzky]

Dear Dan,

sorry for being longtime off, I was ill.


Just for explanation:

what happens when a user logs in is hte following:

He is identified by the system, and then his profile travels all the way from the server to his local maschine. When a User logs off the profile is copied back to the server.
The desktop (and all the files directly on the desktop) is a part of the user's profile.

So if a file is created on the desktop, this file travels each time the user logs in or logs off.

So what I meant was: Do not create the file on the desktop but on a network-volume the user can access (p.e. his Home-directory).

in my firm its like this:

we have a network volume mapped to H:
so we find in H: at least 2 subdirectorys:
Windows NT4 Profile
and
Data
So every user can access H:\data\. Which is where we store his other data (if we have to)

all user have depending on their department and function further mappings, where data is stored all department's users have to have access to.


hope this clarifies a bit what i meant.


kind regards Astrid