Save Location

  • Thread starter Thread starter qkimchi via AccessMonster.com
  • Start date Start date
Q

qkimchi via AccessMonster.com

Hello,

After I run the report and then click on analyze it with MS Excell, it always
save to C:Mydocument. I want to change the location of the file to G:Report.
How would I do this? Thank you inadvance for your help.
 
Unless you can change this using Tools | Options | General tab | Default
Database Folder, you may have to just send it to Excel, then use File | Save
As to get it where you want it to go.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thank you!!! It works.You are great!



Jeff said:
Unless you can change this using Tools | Options | General tab | Default
Database Folder, you may have to just send it to Excel, then use File | Save
As to get it where you want it to go.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Hello,
Click to expand...
[quoted text clipped - 3 lines]
G:Report.
How would I do this? Thank you inadvance for your help.
Click to expand...
Click to expand...
 
Hello,

After I run the report and then click on analyze it with MS Excell, it always
save to C:Mydocument. I want to change the location of the file to G:Report.
How would I do this? Thank you inadvance for your help.
Click to expand...

Well, how complicated do you want things to get?

You COULD automate Excel using the Excel library (manually export your
data) and then use the Excel.Application.SaveAs() function to specify
the target path. I use this approach for one of my reports (the
standard DoCmd.OutputTo() function creates garbage). It is quite a bit
of work though.

-Kris
 
The following code will allow you to specify the location for saving a
report.

You can call the function from a custom button on a button bar or a custom
menu item.

In the button's On Action property enter
funOutputReportToXL

Or enter
=funOutputReportToXL()

Public Function funOutputReportToXL()
'Save the currently selected report to Excel Output
'Called from a button bar or menu item.


Dim strObjName As String
Dim intState As Integer
Dim intCurrObjType As Integer
On Error GoTo ERROR_Trap

intCurrObjType = Application.CurrentObjectType 'Get TYPE OF last Report,
'Form, Table, Module, Query, or Macro with cursor

strObjName = Application.CurrentObjectName 'Get object's name

intState = SysCmd(acSysCmdGetObjectState, intCurrObjType, strObjName)

If intCurrObjType = acReport Then
If intState = acObjStateOpen Then 'Report is open (naturally)

'Leaving 4th argument blank causes Access to ask for an output file
name
DoCmd.OutputTo acOutputReport, strObjName, acFormatXLS, , False
End If

Else
MsgBox "Please display a report to output to an excel document.", ,
"Preview a Report"
End If

Exit_Trap:
Exit Function

ERROR_Trap:

If Err.Number = 2501 Then
'Cancel pressed
Else
MsgBox Err.Number & ": " & Err.Description
End If

Resume Exit_Trap

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top