New at macros, trying to send a report to file so it will work on any
machine.

I can't figure out how to send to the TMP directory, or to MY
DOCUMENTS without knowing the user's name... what do I put in the
OutputFile box when creating a macro to send it to either the
environment variable TMP (there's one on every machine), or the
user's MyDocuments directory, without hardcoding for a specific user?

Thank you kindly.

On Wed, 9 Jan 2008 17:05:49 -0800 (PST), John Bartley K7AAY
<john.bartley (AT) gmail (DOT) com> wrote:

I don't know how to do this in a macro either (they are so limited I
don't bother with them), but in VBA you could use:
Environ("TEMP")
(note: not TMP)
to get the location of the Temp folder.

To get the My Documents folder you have to do a bit more work. See
this page: http://www.mvps.org/access/api/api0054.htm

-Tom.

On Jan 9, 5:35 pm, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:


Tom, I appreciate your response, but I am still not up to speed on
Access, much less incorporating VBA into the database, so your no-
doubt spot-on proposal is so over my head.

Anyone know how to do this within the confines of Macros sans VBA?

Thank you all kindly.

On Thu, 10 Jan 2008 17:05:21 -0800 (PST), John Bartley K7AAY
<john.bartley (AT) gmail (DOT) com> wrote:

I am 99% sure this cannot be done in a macro. You'll have to write
some VBA, or hire someone to do it for you.

-Tom.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=559
Thanks to Ken Puls who posted the above explanation.

Here, I check to make sure C:\TEMP exists.
If it does not, I create it
If it does, I delete all files named tbl*.xls



Attribute VB_Name = "modKillTempXLS"
Option Explicit

Function FileOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file
' or folder exists, false if not.
'PathName : Supports Windows mapped drives or UNC
' : Supports Macintosh paths
'File usage : Provide full file path and extension
'Folder usage : Provide full folder path
' Accepts with/without trailing "\" (Windows)
' Accepts with/without trailing ":" (Macintosh)

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select

'Resume error checking
On Error GoTo 0
End Function

Function ZapOutputFile() As Boolean
'Test if directory or file exists
If FileOrDirExists("C:\TEMP") Then
Kill "C:\temp\tbl*.xls"
Else
MkDir "C:\temp"
End If
Exit Function
End Function