OutputTo problem

[Yves]

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

 

[Stefan Hoffmann]

hi Yves,

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

 

[Yves via AccessMonster.com]

Thanks Stefan!

I tried it and it did work.

Yves

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

 

[Yves via AccessMonster.com]

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?

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

 

[Stefan Hoffmann]

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

 

[Yves via AccessMonster.com]

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

 

[strive4peace]

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
*

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


***********

hi Yves,

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

 

[Stefan Hoffmann]

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

 

[Yves via AccessMonster.com]

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]

hi Yves,

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.

Using Excel automation would look like that (using early binding):

Dim ap As Excel.Application
Dim rs As ADODB.Recordset

Set ap = New Excel.Application
ap.Workbooks.Add

Set rs = New ADODB.Recordset
rs.Open "QueryName", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
ap.Range("A1").CopyFromRecordset rs
'This method works for data rows smaller than 2k size.
'Otherwise you need a Do loop to do it for each field.

rs.Close
Set rs = Nothing

'All formatting can be done here.
'Use the Excel workbook, record a macro containing all steps
'for formatting.
'Copy this macro from Excel.
'Append ap. before each object (method call).

ap.Visible = True
Set ap = Nothing



mfG
--> stefan <--