Output To File path

[GohDiamond]

I'm using the "Output to" action in a macro (Access 2007). It presents me with argument fields to be entered.

• Object type
• Object Name
• Output Format
• Output File
• etc. etc...

Output File path is my focus here. Can I use a relative file path or does it have to be an absolute file path.

For example the database is in a folder on the desktop: \Desktop\DatabaseFolder\anydatabase.mdb
The destination of the output file is in a subfolder of the DatabaseFolder: \Desktop\DatabaseFolder\subfolder\anyexportfile.xls

Do I have to use a path starting with "C:\" ?
such as C:\Documents and Settings\my username\Desktop\DatabaseFolder\subfolder\anyexportfile.xls

or can I just use a relative path like this:
..\Desktop\DatabaseFolder\subfolder\anyexportfile.xls

Or is there another option. I want anyone that uses the database placed on their desktop to be able to output the resulting files to the subfolder in the DatabaseFolder without having to adjust the path code for their user name.

Thanks in advance.
Cheers!
Goh

 

[pbaldy]

This should let you find the current location:

http://www.mvps.org/access/general/gen0007.htm

You can add the subfolder to that.

 

[GohDiamond]

I'm posting the solution for my own benefit as much as for others.

I created a macro that worked using the action: Output To
I filled in the arguments that worked. Then converted the Macro to a VBA module. I pasted the advice from pbaldy above into the Module so it created 2 functions in the module.

1. Function CurrentDBDir() As String
2. Function Export_Excel()

'******************** Code Begin ****************
'Code courtesy of
'Terry Kreft & Ken Getz
'

Function CurrentDBDir() As String
Dim strDBPath As String
Dim strDBFile As String

strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))​

End Function
'******************** Code End ****************
'------------------------------------------------------------
' Export_Excel
'
'------------------------------------------------------------
Function Export_Excel()
On Error GoTo Export_Excel_Err

DoCmd.OutputTo acOutputQuery, "Corporate", "Excel97-Excel2003Workbook(*.xls)", CurrentDBDir & "Exports\Corporate\Corporate.xls", False, "", 0, acExportQualityPrint


Export_Excel_Exit:
Exit Function

As you can see I substituted CurrentDBDir in RED for part of the absolute path and left the remainder of the path in Purple for the exact folder location to export to along with the file name.xls

Then I created the actual user Macro with the action :RunCode and Function Export_Excel(). This worked to produce the results I was looking for.

I could call that function Export_Corporate(), then I could make more Functions in the same way in the same module naming each one of them Export_WhateverNameIChoose() and call those functions from the user macro action:RunCode in subsequent rows.

This is my story and my reminder if I ever have to look this up again.
Cheers
Goh