OutputTo the user"s My Documents directory or to TMP?

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.

Re: OutputTo the user"s My Documents directory or to TMP?

On Wed, 9 Jan 2008 17:05:49 -0800 (PST), John Bartley K7AAY
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.


>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.

Re: OutputTo the user"s My Documents directory or to TMP?

> On Wed, 9 Jan 2008 17:05:49 -0800 (PST), John Bartley K7AAY
>
> wrote:
> >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 Jan 9, 5:35 pm, Tom van Stiphout 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.


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.

Re: OutputTo the user"s My Documents directory or to TMP?

On Thu, 10 Jan 2008 17:05:21 -0800 (PST), John Bartley K7AAY
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.


>
>> On Wed, 9 Jan 2008 17:05:49 -0800 (PST), John Bartley K7AAY
>>
>> wrote:
>> >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 Jan 9, 5:35 pm, Tom van Stiphout 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.
>
>
>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.

Re: OutputTo the user"s My Documents directory or to TMP?

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