By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

Forcing users to save exported reports to a specific folder

P: n/a
I have an Access application on a terminal server. Sometimes my users
need to export reports to pdf, rtf or xls files and save them to their
own client device hard drives.

They can do that right now the way I have this set up, but it's
confusing and slow. When they browse for a place to save the reports,
they see all of the drives on the terminal server as well as their own
client drives. So they're likely to want to choose "My
Documents" (which is on the TS) instead of choosing "C Drive on [User
Machine]".

Furthermore, even if they do follow the correct path to their client
My Documents folder, it takes forever to do so. To remedy this, I've
set it up so that everybody has a "Documents" folder in the root of
their C drives so they can just dump reports into that folder without
having to navigate through multiple folders.

What I'm wondering is if there's a way to force the user to save to
that folder, without even letting them see all of the directories
available to them on the terminal server.

Anybody dealt with this issue? Have any clever ideas?
Aug 28 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I have a similar situation where I use a front end db and all sorts of
different BE Dbs (Mainly different clubs I am associated with), The same FE
is on 4 different machines in the various clubs and only the relevant BE.

One solution is to have 2 tables, one table defining the Club ( in your case
Fred's computer or John's computer) and a second linked table that has the
path of all relevant files such as where the BE database is stored, where
the club logos are stored etc., and more relevant where Excel files and PDF
or Word files are stored.
These 2 tables are stored on the FE database, so on Fred's machine the Excel
path might be "C:\Documents and Settings\Fred\My Documents\Excel
Spreadsheets" and on Johns Machine "C:\My Documents\Excel"

Each Club has a Selected = True to define the active club. VBA routines
ensure there is only 1 club selected and therefore only 1 set of paths.
Then use a DLookup or equivalent in your export routine to find the correct
path with the criteria that Selected = true

HTH

Phil
"evenlater" <ev*******@gmail.comwrote in message
news:d7**********************************@k7g2000h sd.googlegroups.com...
>I have an Access application on a terminal server. Sometimes my users
need to export reports to pdf, rtf or xls files and save them to their
own client device hard drives.

They can do that right now the way I have this set up, but it's
confusing and slow. When they browse for a place to save the reports,
they see all of the drives on the terminal server as well as their own
client drives. So they're likely to want to choose "My
Documents" (which is on the TS) instead of choosing "C Drive on [User
Machine]".

Furthermore, even if they do follow the correct path to their client
My Documents folder, it takes forever to do so. To remedy this, I've
set it up so that everybody has a "Documents" folder in the root of
their C drives so they can just dump reports into that folder without
having to navigate through multiple folders.

What I'm wondering is if there's a way to force the user to save to
that folder, without even letting them see all of the directories
available to them on the terminal server.

Anybody dealt with this issue? Have any clever ideas?

Aug 28 '08 #2

P: n/a
Interesting. But how do you force the user to save exported reports to
the path specified in your tables? When they click the buttons to
export to Word or Excel or PDF, do they still get the browse dialog
box?

On Aug 28, 5:31*pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
I have a similar situation where I use a front end db and all sorts of
different BE Dbs (Mainly different clubs I am associated with), The same FE
is on 4 different machines in the various clubs and only the relevant BE.

One solution is to have 2 tables, one table defining the Club ( in your case
Fred's computer or John's computer) and a second linked table that has the
path of all relevant files such as where the BE database is stored, where
the club logos are stored etc., and more relevant where Excel files and PDF
or Word files are stored.
These 2 tables are stored on the FE database, so on Fred's machine the Excel
path might be "C:\Documents and Settings\Fred\My Documents\Excel
Spreadsheets" and on Johns Machine "C:\My Documents\Excel"

Each Club has a Selected = True to define the active club. VBA routines
ensure there is only 1 club selected and therefore only 1 set of paths.
Then use a DLookup or equivalent in your export routine to find the correct
path with the criteria that Selected = true

HTH

Phil

"evenlater" <evanca...@gmail.comwrote in message

news:d7**********************************@k7g2000h sd.googlegroups.com...
I have an Access application on a terminal server. Sometimes my users
need to export reports to pdf, rtf or xls files and save them to their
own client device hard drives.
They can do that right now the way I have this set up, but it's
confusing and slow. When they browse for a place to save the reports,
they see all of the drives on the terminal server as well as their own
client drives. So they're likely to want to choose "My
Documents" (which is on the TS) instead of choosing "C Drive on [User
Machine]".
Furthermore, even if they do follow the correct path to their client
My Documents folder, it takes forever to do so. To remedy this, I've
set it up so that everybody has a "Documents" folder in the root of
their C drives so they can just dump reports into that folder without
having to navigate through multiple folders.
What I'm wondering is if there's a way to force the user to save to
that folder, without even letting them see all of the directories
available to them on the terminal server.
Anybody dealt with this issue? Have any clever ideas?
Aug 29 '08 #3

P: n/a
Here is the routine to output a report as a PDF file to a location defined
by PDFPath in the Paths table. Regret not much of my work here, but thanks
to Ken & Paul

Phil

Option Compare Database
Option Explicit

'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long

'Write settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lpFileName As String) As Long

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

' These functions used with procedures from
' Microsoft Access 95 How-To
'(c) 1998 Ken Getz and Paul Litwin
' All rights reserved.

' Other modules from this source are:
' basDefaultPrinter
' basGetPrinters
' basIniFile
' basPrintTypes
' basToken

' You'll also need defaultprt.zip

Private drexisting As aht_tagDeviceRec
'Const AcrobatName = "Acrobat PDFWriter"
'Const AcrobatDriver = "PDFWRITR"
'Const AcrobatPort = "LPT1:"

Const AcrobatName = "PDF995"
Const AcrobatDriver = "PDF995"
Const AcrobatPort = "NE01:"

Sub ResetDefaultPrinter()

Call ahtSetDefaultPrinter(drexisting)

End Sub

Function ChangeToAcrobat()
If ahtGetDefaultPrinter(drexisting) Then
Dim dr As aht_tagDeviceRec
With dr
.drDeviceName = AcrobatName
.drDriverName = AcrobatDriver
.drPort = AcrobatPort
End With
Call ahtSetDefaultPrinter(dr)
End If

End Function

Sub ChangePdfFileName(NewFileName As String)

Call aht_apiWriteProfileString("Acrobat PDFWriter", "PDFFileName",
NewFileName)

End Sub

Function PDFWrite(ReportName As String, PDFPath As String, ToKill As
Boolean, _
Optional RptCaption As String, Optional StrCriteria As String) As String
'?pdfwrite("RptWaitingList", "C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC\PDFS", true)

' Runs an Access report to PDF995 to create a pdf file from the report.
' Input parameters are the name of the report within the current database,
' the path for the output file, and an optional criteria for the report

' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is
set to 0
' when pdf995 is idle. This codes uses that as a completion flag as it seems
to be
' the most reliable indication that PDF995 is done writing the pdf file.
' Note: The application.printer object is not valid in Access 2000
' and earlier. In that case, set the printer in the report to pdf995
' and comment out the references herein to the application.printer

Dim SyncFile As String, MaxWaittime As Long
Dim IniFileName As String ', tmpPrinter As Printer
Dim OutputFile As String, CaptionFile As String
Dim X As Long
Dim TmpOutputFile As String, TmpAutoLaunch As String

' set the location of the PDF995.ini and the pdfsync files
IniFileName = "c:\pdf995\res\pdf995.ini"
SyncFile = "c:\documents and settings\all users\application
data\pdf995\res\pdfsync.ini"

' build the output file name from the path parameter and the report name
If Mid(PDFPath, Len(PDFPath), 1) <"\" Then PDFPath = PDFPath & "\"
OutputFile = PDFPath & ReportName & ".pdf"

If RptCaption = "" Then
RptCaption = ReportName
End If
CaptionFile = PDFPath & RptCaption & ".pdf" ' This appears
to be the name that is saved

' PDF995 operates asynchronously. We need to determine when it is done
so we can
' continue. This is done by creating a file and having PDF995 delete it
using the
' ProcessPDF parameter in its ini file which runs a command when it is
complete.

' save current settings from the PDF995.ini file
TmpOutputFile = ReadINIfile("PARAMETERS", "Output File", IniFileName)
TmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", IniFileName)

' remove previous pdf if it exists
On Error Resume Next

If ToKill = True Then 'if you need a new file
If Dir(CaptionFile) <"" Then ' and can find it
Kill CaptionFile ' delete it
End If
End If

On Error GoTo Cleanup

' setup new values in PDF995.ini
X = WritePrivateProfileString("PARAMETERS", "Output File", OutputFile,
IniFileName)
X = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0",
IniFileName)

' change the default printer to PDF995
' if running on Access 2000 or earlier, comment out the next two lines
'Set tmpPrinter = Application.Printer
'Application.Printer = Application.Printers("PDF995")

Call ChangeToAcrobat ' Set default printer to PDF995

'print the report
DoCmd.OpenReport ReportName, acViewNormal, , StrCriteria

Call ResetDefaultPrinter ' Reset default printer

' cleanup delay to allow PDF995 to finish up. When flagfile is nolonger
present, PDF995 is done.
Sleep (1000)
MaxWaittime = 30000 'If pdf995 isn't done in 5 min, quit anyway
Do While ReadINIfile("PARAMETERS", "Generating PDF CS", SyncFile) = "1"
And MaxWaittime 0
Sleep (1000)
MaxWaittime = MaxWaittime - 1000
Loop

' restore the original default printer and the PDF995.ini settings
Cleanup:
Sleep (1000)
X = WritePrivateProfileString("PARAMETERS", "Output File",
TmpOutputFile, IniFileName)
X = WritePrivateProfileString("PARAMETERS", "AutoLaunch", TmpAutoLaunch,
IniFileName)
X = WritePrivateProfileString("PARAMETERS", "Launch", "", IniFileName)
'On Error Resume Next

' if running on Access 2000 or earlier, comment out the next line
'Application.Printer = tmpPrinter
PDFWrite = CaptionFile ' Name of saved file

End Function

Function ReadINIfile(sSection As String, sEntry As String, sFilename As
String) As String

Dim X As Long
Dim sDefault As String
Dim sRetBuf As String, iLenBuf As Integer
Dim sValue As String

'Six arguments
'Explanation of arguments:
'sSection: ini file section (always between brackets)
'sEntry : word on left side of "=" sign
'sDefault$: value returned if function is unsuccessful
'sRetBuf$ : the value you're looking for will be copied to this buffer
string
'iLenBuf% : Length in characters of the buffer string
'sFileName: Path to the ini file

sDefault$ = ""
sRetBuf$ = String$(256, 0) '256 null characters
iLenBuf% = Len(sRetBuf$)
X = GetPrivateProfileString(sSection, sEntry, _
sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, X)

End Function
"evenlater" <ev*******@gmail.comwrote in message
news:cc**********************************@z72g2000 hsb.googlegroups.com...
Interesting. But how do you force the user to save exported reports to
the path specified in your tables? When they click the buttons to
export to Word or Excel or PDF, do they still get the browse dialog
box?

On Aug 28, 5:31 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
I have a similar situation where I use a front end db and all sorts of
different BE Dbs (Mainly different clubs I am associated with), The same
FE
is on 4 different machines in the various clubs and only the relevant BE.

One solution is to have 2 tables, one table defining the Club ( in your
case
Fred's computer or John's computer) and a second linked table that has the
path of all relevant files such as where the BE database is stored, where
the club logos are stored etc., and more relevant where Excel files and
PDF
or Word files are stored.
These 2 tables are stored on the FE database, so on Fred's machine the
Excel
path might be "C:\Documents and Settings\Fred\My Documents\Excel
Spreadsheets" and on Johns Machine "C:\My Documents\Excel"

Each Club has a Selected = True to define the active club. VBA routines
ensure there is only 1 club selected and therefore only 1 set of paths.
Then use a DLookup or equivalent in your export routine to find the
correct
path with the criteria that Selected = true

HTH

Phil

"evenlater" <evanca...@gmail.comwrote in message

news:d7**********************************@k7g2000h sd.googlegroups.com...
I have an Access application on a terminal server. Sometimes my users
need to export reports to pdf, rtf or xls files and save them to their
own client device hard drives.
They can do that right now the way I have this set up, but it's
confusing and slow. When they browse for a place to save the reports,
they see all of the drives on the terminal server as well as their own
client drives. So they're likely to want to choose "My
Documents" (which is on the TS) instead of choosing "C Drive on [User
Machine]".
Furthermore, even if they do follow the correct path to their client
My Documents folder, it takes forever to do so. To remedy this, I've
set it up so that everybody has a "Documents" folder in the root of
their C drives so they can just dump reports into that folder without
having to navigate through multiple folders.
What I'm wondering is if there's a way to force the user to save to
that folder, without even letting them see all of the directories
available to them on the terminal server.
Anybody dealt with this issue? Have any clever ideas?

Aug 29 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.