OutputTo problem

Hi,

I wrote this code to export data to Excel:

Public StWherePublic As string

Dim strWhere As String
Dim stDocName As String

StWherePublic = "[Date] Between " & _
Format(Me.txtStart, "\#yyyy\/m\/d\#") & " And " & _
Format(Me.txtend, "\#yyyy\/m\/d\#")

stDocName = "S1_P1"
DoCmd.OutputTo acReport, stDocName, , , True

In the Open Event of the Report, I wrote this code:
If StWherePublic vbNullString Then
Me.Filter = StWherePublic
Me.FilterOn = True
StWherePublic = vbNullString
End If

I get the file completed in Excel but the problem that I have is that the on
Open Event does not get called (I put a Breakpoint and the code does not get
executed). So my data in Excel does not reflect the dates that the operator
put (the Filter is not working)

Please someone help me.

Thanks,
Yves

hi Yves,

Yves wrote:
Public StWherePublic As string

Dim strWhere As String
Dim stDocName As String

StWherePublic = "[Date] Between " & _
Format(Me.txtStart, "\#yyyy\/m\/d\#") & " And " & _
Format(Me.txtend, "\#yyyy\/m\/d\#")

stDocName = "S1_P1"
DoCmd.OutputTo acReport, stDocName, , , True

In the Open Event of the Report, I wrote this code:
If StWherePublic vbNullString Then
Me.Filter = StWherePublic
Me.FilterOn = True
StWherePublic = vbNullString
End If
Try the following:

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, strDoc



mfG
-- stefan --

Thanks Stefan!

I tried it and it did work.

Yves

Stefan Hoffmann wrote:
hi Yves,

Public StWherePublic As string

[quoted text clipped - 14 lines]
StWherePublic = vbNullString
End If
Try the following:

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, strDoc

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200701/1

Thanks Stefan!

Now I am experiencing an other problem with OutputTo. The data that gets
transfered to Excel are only the fields that are related to a table. All the
fields that I calculated in the report (VB code) are not trasfered to Excel.

Any suggestions?

Stefan Hoffmann wrote:
hi Yves,

Public StWherePublic As string

[quoted text clipped - 14 lines]
StWherePublic = vbNullString
End If
Try the following:

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, strDoc

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200701/1

hi Yves,

Yves via AccessMonster.com wrote:
Now I am experiencing an other problem with OutputTo. The data that gets
transfered to Excel are only the fields that are related to a table. All the
fields that I calculated in the report (VB code) are not trasfered to Excel.

Any suggestions?
I use always Excel automation instead of OutputTo.

http://www.mvps.org/access/modules/mdl0006.htm

mfG
-- stefan --

Stefan,

I just tried Excel automation and it did work. If I don't have the choice I
will go with that but I have a lot of work to do because all my reports are
already done and working fine. This mean that I will have to write a lot of
code.

Do you have any direction of why my calculated field would not be added to
Excel?

Thanks,
Yves

Stefan Hoffmann wrote:
hi Yves,

Now I am experiencing an other problem with OutputTo. The data that gets
transfered to Excel are only the fields that are related to a table. All the
fields that I calculated in the report (VB code) are not trasfered to Excel.

Any suggestions?
I use always Excel automation instead of OutputTo.

http://www.mvps.org/access/modules/mdl0006.htm

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200701/1

Hi guys, working on something similar, but my reports are based off a
combobox value which is passed as a filter to the report via command button
clcik(which works fine just for pulling up the report) . I'm having a
problem when I try to directly export the file to a word doc, the filter does
not pass on. An unfiltered word doc is created when I use my code below, any
suggestions? Thanks!

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String
Dim strWhere As String
stDocName = "Project_Report"
strWhere = "1=1 "
If Not IsNull(Me.Combo3) Then
strWhere = strWhere & " AND [Office] = """ & Me.Combo3 & """ "
Else
MsgBox "Slect Office location"
Exit Sub
End If

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, stDocName

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click


***********

"Stefan Hoffmann" wrote:

hi Yves,

Yves wrote:
Public StWherePublic As string

Dim strWhere As String
Dim stDocName As String

StWherePublic = "[Date] Between " & _
Format(Me.txtStart, "\#yyyy\/m\/d\#") & " And " & _
Format(Me.txtend, "\#yyyy\/m\/d\#")

stDocName = "S1_P1"
DoCmd.OutputTo acReport, stDocName, , , True

In the Open Event of the Report, I wrote this code:
If StWherePublic vbNullString Then
Me.Filter = StWherePublic
Me.FilterOn = True
StWherePublic = vbNullString
End If
Try the following:

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, strDoc



mfG
-- stefan --

Hi Dave

you must SAVE the filter with the report before you Output To...here is
a generic procedure you can put into a general module to do this:

'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#2/14/07#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

'use the hidden parameter to open if you don't want to see it
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Dave wrote:
Hi guys, working on something similar, but my reports are based off a
combobox value which is passed as a filter to the report via command button
clcik(which works fine just for pulling up the report) . I'm having a
problem when I try to directly export the file to a word doc, the filter does
not pass on. An unfiltered word doc is created when I use my code below, any
suggestions? Thanks!

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String
Dim strWhere As String
stDocName = "Project_Report"
strWhere = "1=1 "
If Not IsNull(Me.Combo3) Then
strWhere = strWhere & " AND [Office] = """ & Me.Combo3 & """ "
Else
MsgBox "Slect Office location"
Exit Sub
End If

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, stDocName

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click


***********

"Stefan Hoffmann" wrote:

hi Yves,

Yves wrote:
Public StWherePublic As string

Dim strWhere As String
Dim stDocName As String

StWherePublic = "[Date] Between " & _
Format(Me.txtStart, "\#yyyy\/m\/d\#") & " And " & _
Format(Me.txtend, "\#yyyy\/m\/d\#")

stDocName = "S1_P1"
DoCmd.OutputTo acReport, stDocName, , , True

In the Open Event of the Report, I wrote this code:
If StWherePublic vbNullString Then
Me.Filter = StWherePublic
Me.FilterOn = True
StWherePublic = vbNullString
End If
Try the following:

DoCmd.OpenReport stDocName, , , strWhere
DoCmd.OutputTo acReport, stDocName, , , True
DoCmd.Close acReport, strDoc



mfG
-- stefan --

hi Yves,

Yves via AccessMonster.com wrote:
Do you have any direction of why my calculated field would not be added to
Excel?
Not all report controls are rendered correctly to an Excel spread sheet.
I would think it is by design. But i don't see any logical structure
behind it.


mfG
-- stefan --

Stefan,

I fanally used Excel automation and it does work except for on reason. I put
in the Footer Group the code to export data to Excel and it does work but if
my report is longer than one page, I have to preview all pages to get the
data transfered into Excel.

Is there a way to run the report without having to go over all pages?
I don't need to see the report when I export data to Excel.

Thanks,
Yves

Stefan Hoffmann wrote:
hi Yves,

Do you have any direction of why my calculated field would not be added to
Excel?
Not all report controls are rendered correctly to an Excel spread sheet.
I would think it is by design. But i don't see any logical structure
behind it.

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200701/1