Exporting reports: Question by many without answer 1

[hairwormman]

I have noticed that many people are having the same problem as I, but this forum has not been able to provide an answer. Thus I suspect that this is a flaw in Access, but let me give this question one more try (thanks for your patience!!).
When looking at a report in Access 2002, the text looks perfect: layout is good, spacing is as wanted, the text is all there. However, when you export this report to rtf or txt format (including MSWord),the text is different. Now different is not always bad, but different here is that the content is fundamentally changed. This includes the insertion of spaces, deletion of complete lines (either directly after a page break or anywhere within a page (this line of text is replaces by a blank line)), partial transfer of memo fields (where only 50 to 100 characters of 500 are copied, followed by blank lines where the text should be), and many other anomalies.
The main answer that I have read in this forum has been to use a snapshot viewer. This works great if this file is to be read only, but if you have to have an editable file this will not work. For my work, I must send a text formatted report to an automated web server, which parses out certain info. However, since the conversion often changes the content of the report the server returns the whole mess, and I have to spend hours making the word file look like the report in Access. What a waste of time!
Is there anything out there that will fix this problem? I have tried to contact MS regarding this issue, but it seems that I get the run around (they tell me "check your code" or "reinstall Office" or "it must be your hardware", etc.). I have done all of this, but after reading that dozens of similar posts exitst on this forum, it cannot be me. Does anyone have a suggestion of how to get in touch with a tech directly, to get Microsoft to write a patch for this problem?
Thanks to all of you for reading this and responding.

Cheers...Ben

 

[BNPMike]

Have a look at XML for this kind of thing.

 

[thecreator]

I have a similar problem and it seems to occur when a user opens and exports a report I have designed but their screen reolution is lower than mine.

The solution there is to design at something ugly like 640 x 480 so that nobody can get lower than that and it seems to work OK ...

OR

Export the fields you need for your report to Excel then do an automated mail merge to Word (call Word from Access and perform the merge on opening the document).

I use the following code to do a bulk letter generation from Access ... you will need a reference to the Microsoft Word 8.0 library (we are still Office 97 here) -

Option Compare Database
Option Explicit

Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, _
ByVal wCmd As Long) As Long

Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal CCh As Long) _
As Long

Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) _
As Long

Public Const GW_HWNDFIRST = 0
Public Const GW_HWNDLAST = 1
Public Const GW_HWNDNEXT = 2
Public Const GW_HWNDPREV = 3

' Variable for determining whether reports utilise
' the primary or secondary Local Governmet Code
Public ConGroupLevel As Integer

' This function returns the Caption Text of each window passed to
' it. If a window does not have a Caption bar, then this function
' returns a zero-length string ("")

Function GetAppName(Lnghwnd As Long)
Dim LngResult As Long
Dim StrWinText As String * 255
Dim LngCCh As Long
LngResult = GetWindowText(Lnghwnd, StrWinText, 255)
GetAppName = Left(StrWinText, LngResult)
End Function

' This function counts all instances of an application that are open,
' including any windows that are not visible.
' Arguments: LngHwnd = Any valid window handle.
' StrAppCaption = The window caption to search for.
' Example: GetCountOfWindows(hWndAccessApp,"Microsoft Access")
Function GetCountOfWindows(Lnghwnd, StrAppCaption)
Dim LngResult As Long
Dim LngICount As Long
Dim StrAppName As String

LngResult = GetWindow(Lnghwnd, GW_HWNDFIRST)
Do Until LngResult = 0
If IsWindowVisible(LngResult) Then
StrAppName = GetAppName(LngResult)
If InStr(1, StrAppName, StrAppCaption) Then
LngICount = LngICount + 1
End If
End If
LngResult = GetWindow(LngResult, GW_HWNDNEXT)
Loop
GetCountOfWindows = LngICount
End Function

Sub PrintLetters()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document

' if Word is already open, use this instance otherwise create a new instance
If GetCountOfWindows(hWndAccessApp, "Microsoft Word") > 0 Then
Set WordApp = GetObject(, "Word.Application")
Else
Set WordApp = CreateObject("Word.Application")
End If

' make Word active window and display base document
WordApp.Application.Visible = True
Set WordDoc = WordApp.Documents.Open("L:\High_Consumption_Base.doc")

'do merge from Excel sheet
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

'close the base document
WordDoc.Close SaveChanges:=wdDoNotSaveChanges

End Sub

Neither of these are the ultimate solution but maybe worth a shot if you are stuck for another way to go ??

Hope this helps

 

[ordendelfai]

I use this extensively and it works great:

http://www.lebans.com/ReportUtilities.htm.

You have to create a reference to the ReportSpecs.mde file (using Tools -> References) that the download comes with.

In your vba code module of your form you must declare:
Dim pf As clsPrintToFit

When you want to export a report use code similar to this:

Private Sub cmd_ExportMtg_Click()
Set pf = New clsPrintToFit 'This line must be included
pf.ExportToMSWord "rpt_OEmain_WordMerge" 'Report Name
End Sub

It exports the report in near adobe perfectness...

It is kind of a pain to figure it it out the first time, so if you need more clarification, just ask ;-)

~Joel