Losing report format when Exporting to word 3

[Stoffman]

I have just spent ages getting this report format correct. Now that it looks lovely my Boss wants to be able to export this to Word/Email. On doing this I notice that all the formatting is lost. Does anyone know of a trick to keep these formats. Table borders etc.

Cheers Chris

 

[KenReay]

Hi

See

http://www.Lebans.com,

you might find something there

Otherwise why Word, does he want to edit it?, if not why not PDF format?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Stoffman]

My Works PC does not allow me to look at News Groups. How do you set up Access to export to a PDF file then?

Cheers Chris

 

[KenReay]

Hi

http://www.lebans.com

is not a news group, it is a website with many useful routines, particulalry to do with printing

You install a PDF writer, look on Google, there are dozens of option

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SilentAiche]

Stoffman,

Welcome to Tek-Tips!

If I may ask, how did you export your Access Report to Word?

When I print-preview an access report, I click on Tools \Office Links \ Publish It with MS Word. It appeared that all I lost were headers and footers, which are usually fairly easy to recreate in Word.

Just a thought.

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[Stoffman]

I followed the same route to export the Report. It only saves it in RTF though.

I am running Access 97 and word 97 so maybe thats the reason why it doesn't keep the formatting.

Any more ideas anyone please. I have to get this done by the end of the week.

I can't believe that two MS products do not speak to each other properly. Then again??

Cheers Chris

 

[ZmrAbdulla]

You can't export Access report to Word keeping the format/images intact. You have two another option (for printing or sending to a person who doesn't have Access installed)
1) Print to PDF (there are free PDF printers available)
2) Export to Snapshot format (Snapshot Viewer is free from Microsoft)

Another option is Leban's as told by Ken Reay. But only one like Stephan Leban will dare to use the code because of the complexilty.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[earthandfire]

ZmrAbdulla, there is a third option. Create the report in Word and use Bookmarks or MailMerge to get the data.

Hope this helps.

 

[ZmrAbdulla]

Thanks earthandfire, I am unaware of that option.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[earthandfire]

ZmrAbdulla, how easy this is depends on the complexity of the report.

What I tend to do is produce the report template as a Word document. Use the Insert Bookmark option to place a Bookmark where the data has to go, giving the Bookmark the name of the Access query field. Any additional calculations, summaries are done in code, then using automation, create a new Word document based on the template, use a For Each loop to run through the fields in the query placing the data in the Bookmark and then place any data calculated in code in their Bookmarks. Finally save and/or print the Word document.

I don't have an example to hand, but I could dig one out later if you are interested.

 

[ZmrAbdulla]

Thanks, Please post an example when you are free. Many of us will get benefited it.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[earthandfire]

Zameer as promised

To produce a report in Word from Access:

Determine the structure of the report ie fixed text and imported data

Start a new Word document, type in the fixed text. For the imported data parts I tend to use tables especially where I might need the Access CanGrow feature.

Consider an invoice:

Customer Name/Address
Invoice Number
Invoice Date
*Items being invoiced
Sub Total
VAT Rate
Total VAT
Grand Total Cost

*Iems being invoiced :-
Quantity
Description
Unit Cost
Total Cost

Customer Name/Address would be multiline
Items being invoiced would probably be multiline
VAT Rate, Total VAT, Sub Total and Grand Total Cost would be in "fixed" positions below the Items being invoiced.

The size of the paper would determine the maximum number of Items being invoiced on any particular invoice.

In Word I would have:
Customer Name/Address as a single cell table
Items being invoiced as a four column one row table
all the other items as single cell tables

Using Bookmarks:
Place your cursor in the Customer Name/Address cell, form the menu Insert | Bookmarks..., type a name for your Bookmark eg Customer
Repeat this for all the single cell fields.
With items being invoiced we have a choice, if we assume that the page can take 10 items, we could create 40 separate Bookmarks (Quantity1, Description1 etc. etc), however I would only create four - one in each column (Quantity, Description, UnitCost and TotalCost).

When the document design is complete use File | Save As.., and select Word Template as the file type.


In Access:

Depending on the type of report, you may be able to generate all the required data from one or more queries, or you may have to do some manipulation of the data.

Assuming that the Bookmarks have been give the same names as the Fields:

for each fld in rs.fields
goto bookmark fld.name
insert fld
next fld

Otherwise:
goto bookmark <specify name>
insert <variable>
goto bookmark <specify name>
insert <variable>
...
...

In the invoice example, the Customer detail would come from several fields, so I just concatenate them into a single value. The Items being invoiced I would produce as four separate variables using the same concatenation method.

Simple Concat:

Sub MyConcat(ByRef Target As String, ByVal Source As String)

  If Source = "" Then
    Exit Sub
  Else
    If Target <> "" Then Target = Target & vbCrLf
    Target = Target & Source
  End If

End Sub

Sub DoConcat()

  Dim MasterString As String
  MasterString = ""
  MyConcat MasterString, ""
  MyConcat MasterString, "This is the first line"
  MyConcat MasterString, "This is the middle line"
  MyConcat MasterString, "This is the last line"
  Debug.Print MasterString

End Sub

The variables and Bookmarks should have the same names for simplicity so:
Dim Customer as string
Dim InvoiceNumber as string/Long whatever
etc.
etc.


InvoiceNumber can be assigned right away as the user requestedprinting this invoice

A few recordsets are needed (must be normalised )
dim rsInvoice as Recordset
rsCustomer
rsInvoiceItems
and others as required

Then
Set rsInvoice = CurrentDb.OpenRecordset("SELECT etc. etc. FROM Invoices WHERE InvoiceNumber = " & InvoiceNumber)
InvoiceDate, VATRate, CustomerID can be assigned from here

Set rsCustmers = CurrentDb.OpenRecordset("SELECT etc. etc. FROM Customers WHERE CustmerID = " & CustomerID)
Use the MyConcat procedure to build the Customers variable

With InvoiceItems we can process each item:
Quantity * UnitPrice will give TotalCost for this item and SubTotal + TotalCost for the running total
additionally run each field against MyConcat
MyConcat Quantity, rsInvoiceItems!Quantity
...
...


Calculate VAT and GrandTotal

I (fortunately) always know what version of Word I'll be using so I set the appropriate reference and can use early binding, which helps with Intellisense, if you doon't know then you will need to use CreateObject/GetObject.

Private wrd As Word.Application
Private doc As Word.Document

    Set wrd = New Word.Application
    Set doc = wrd.Documents.Add("Invoice")		'This will create a new document based on the Invoice template
    doc.Activate
    With wrd.ActiveDocument
      .Bookmarks(InvoiceNumber).Select
      wrd.Selection.TypeText InvoiceNumber, "")
      ...
      ...
    End With
    wrd.PrintOut False
    doc.Close wdDoNotSaveChanges
    Set doc = Nothing
    If wrd <> "" Then
      Set wrd = Nothing
    End If

If the user needs to interact with the Word document:

  'Enter with TheDoc name as a parameter
  'gWord2000Path is a global variable holding the path to Winword.exe
  'PrintPath is the folder in which the document is saved (temporarily)
  doc.SaveAs TheDoc
  Shell gWord2000Path & " " & TheDoc, vbMaximizedFocus
  'Wait for Word to open the document
  Do While Dir(PrintPath & "~*.DOC", vbHidden) = ""
    DoEvents
  Loop
  'Wait for the user to close Word
  Do While Dir(PrintPath & "~*.DOC", vbHidden) <> ""
    DoEvents
  Loop
  'The document was only saved temporarily so delete it
  Kill TheDoc

I hope this makes sense and is of use to someone.

 

[ZmrAbdulla]

Thanks earthandfire, I will try it later.

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)

 

[earthandfire]

Zameer, if you need any further explanations let me know.

 

[ZmrAbdulla]

Sure.. but later.. thanks for that

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)