Repost - Default folder for Office Links

  • Thread starter Thread starter Brigitte P
  • Start date Start date
B

Brigitte P

I posted this in the past and didn't get a reply. Would someone please have
mercy on me :) Thanks.

When I dump a file into an Excel spreadsheet via the
toolbar button "x" (Office Links in Tools), the file goes
by default into the same folder that I have designated as
the default folder for Access files. My user then runs a
macro in Excel that formats the spreadsheet and saves into
another location.
However, until the user goes into the original Access
default folder on the network and delets the original
file, the file could be accessed by non-authorized users
This is a big problem, because we are a hospital and have
to follow confidentiality regs.
Question: Can I change the location where the office links
save to, and if so, where? I searched everyplace and can't
see it.
Thanks
Brigitte P
 
Brigitte P said:
I posted this in the past and didn't get a reply. Would someone
please have mercy on me :) Thanks.

When I dump a file into an Excel spreadsheet via the
toolbar button "x" (Office Links in Tools), the file goes
by default into the same folder that I have designated as
the default folder for Access files. My user then runs a
macro in Excel that formats the spreadsheet and saves into
another location.
However, until the user goes into the original Access
default folder on the network and delets the original
file, the file could be accessed by non-authorized users
This is a big problem, because we are a hospital and have
to follow confidentiality regs.
Question: Can I change the location where the office links
save to, and if so, where? I searched everyplace and can't
see it.
Thanks
Brigitte P
Click to expand...

I don't know for sure, but I doubt that you can change where the
built-in Office Link button puts the exported workbook. However, you
could create your own function to export the data to Excel, using
DoCmd.TransferSpreadsheet or DoCmd.OutputTo and putting the output file
where you like. You could then call that function from a command button
on a form or from your own custom menu item or toolbar button.
 
Two things you may wish to try: (air code)

Use CHDIR to set the current application directory before doing the export

or

Use application.setoption "Default Database Directory", "whatever"
to set the default database directory before doing the export
(you would need to reset the value before leaving Access)

(david)
 
In addition to the other suggestions you have received:

-You know where the file is going....
-You know what it's named (same name as the query or report you "dumped"
into Excel)....

so, another option might be a piece of code *in Access* that uses Automation
to open that file in Excel and save it with a password.
Or, other VBA code that would move the file to a more secure location.
 
Thanks to all of you. I have tried a macro with the transfer spreadsheet
option which used to work fine in A97. Now I get all kinds of error
messages, but mainly that the OLE server is not installed properly - I have
no idea what to do with this message. I noticed that some others have had
issues with this and posted questions on the newsgroup. I'll try to track
the responses to the OLE related questions, or if you know how to do this,
please share.

I also tried the code in one of the manuals, and got an error message that I
have a read only file, found in the Access help that the quotation marks
need to be removed, but that didn't help either. I'll try again the
solutions provided by you wonderful people.

When I use the office link button (x), it goes to the Access default
location and opens Excel. There I (or better my user) manipulates the format
with an Excel macro (my users customer wants the data in a certain Excel
format), and save as ....... I contemplated to expand the Excel macro to
delete the original file via that macro (need to figure out how to do this),
but my user doesn't have network rights to that folder, and I doubt that IT
will change this.

One thing I may need to do: change the default Access directory to a more
secure location, which will most likely only be troublesome for me. I just
need to make sure that my users don't switch the default location around.

Anyway, many thanks, and I'll try your solutions.
Brigitte
 
Thanks, got it. I changed the Access Dafault location, and did a macro.
Instead of saying Run Automatic I did a second line with RunApp (which
prevents the OLE error thing). Interestingly, when I typed the full path, it
didn't work. But when I created shortcuts and the copied the path it worked.
I didn't make a typing error the first time, I swear. Also, it does make a
difference where the quotation marks are -- path to Excel or Word needs the
quotation, path to filename does not. The later requires that there are no
spaces in the filenames. But I'm an old DOS person, so I rarely do spaces in
filenames or long names (DOS developed good habits in us :)
Thanks, you pointed me in the right direction.
Brigitte
 
Back
Top