exporting to a MS word and MS Word from access programmatically

Hi,

I want to export the contents of the MS access REPORT to MS word and MS access, which ever is easier and has more features. How can I do it?


Thanks,
St
spacetravellerAsked:
Who is Participating?
 
nico5038Commented:
Hmm, better to use then a "dynamic" report template.
I use this code for a crosstable report that can have different (and varying) fieldnames:

To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

'Place headers
For intI = 0 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI

'Place correct controlsource
For intI = 0 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI

End Sub

Getting the idea ?

Nic;o)
0
 
nico5038Commented:
When opening the report in preview you'll see a button to export the report into MS Word format.
This will however "drop" lines and images :-(
You can however also use the .snp (snap) format that's readable by the snapshot reader or export to a .pdf file.

What do you want to use the exported report for ?

Nic;o)
0
 
Richard DanekeTrainerCommented:
Once a report in on the screen, Tools, Office Links... Publish with Word  will present your report as a Word Document.  If you save the report as Snapshot, you can share the report with others, however, they would need to have a (free) Microsoft Snapshot report viewer installed.  
The word report can be output by Macro or VisualBasic.
The snapshot report can be output by Macro or VisualBasic.
The word report can be edited.
The report can be saved as HTML and viewed in Internet Explorer.
Wich approach appeals to you?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
spacetravellerAuthor Commented:
Thanks Nic;o and DoDahD. How can I do it programmatically? Can you please explain with an example. I will be exporting some text data running from a single page to several pages. If I can export the logo that I am planning in the future, that would be great !

Thanks,
st
0
 
nico5038Commented:
The most flexible way will be to use the report query for a mailmerge.
Just open Word and start the mailmerge wizard.
Now you can define the needed document including images, lines, etc. and fill the fields from the access query.

Nic;o)
0
 
Richard DanekeTrainerCommented:
You can use VB or a Macro.

Assuming this is a report output, a Macro line would be Action = OutputTo, then in Action Arguments (POOR term, but this is the bottom part of the design Macro form):

Object Type = Report
Object Name = 'report name'
Output Format = Rich Text Format  (this is for Word, it is a combo box if you want to see the other options)
Output File = 'filename'  (when you leave this blank, you can pick a folder and a name to save the file)
Auto Start = No  (when you say Yes, Word will start and show you the report as a document)

BTW, using Rich Text Format will show logos and other graphic elements in your report.

This one line macro can be saved.  When you use the Command Button wizard in designing forms, you can tell the wizard the macro name to have this execute (great term!).
0
 
spacetravellerAuthor Commented:
Thanks Nic;o and DoDahd,

Following is a part of the code that I have implemented.

I am getting the report name from the user to open up a new report and I do a query after which I add some lines/borders to the report and I want to transfer the contents of this report into MS word or MS excel file.

''''''''''''''''''''''' Getting the Report name from the user
strReportName = InputBox("Enter the Report Name", "Report Name")
Set rptNew = CreateReport
DoCmd.Save , strReportName

'''''''''''''''''''''''' Executing Query
rptNew.RecordSource = "SELECT  tblFATS.* FROM tblFATS WHERE ID >= 10"

rptNew.Section("Detail").Height = 350


'''''''''''''''''''''''' First Column Data
Set txtReportColumns = CreateReportControl(rptNew.Name, acTextBox, _
, , "ID")

'''''''''''''''''''''''' First Column dimensions
Set lblReportLabel = CreateReportControl(rptNew.Name, acLabel, _
acPageHeader)
lblReportLabel.Name = "lblID"
lblReportLabel.Caption = "ID"
lblReportLabel.Width = 2000
lblReportLabel.Height = 300
lblReportLabel.FontBold = True

'''''''''''''''''''''''''' Second Column Data

'''''''''''''''''''''''''' Third Column Data

''''''''''''''''''''''''' Export to MS WORD /MS EXCEL ?????


Thanks A lot,
st
0
 
nico5038Commented:
Why dynamically creating the report ?
Did you check the Word Mailmerge ?

Nic;o)
0
 
spacetravellerAuthor Commented:


The report has to be created depending upon the users input. That is the reason I had to create them dynamically. I haven't tried the mail merge yet.

Thanks,
st
0
 
Richard DanekeTrainerCommented:
After you save your report, use the following VB:

DoCmd.OutputTo acOutputReport, "rptName" , acFormatRTF, "C:\FilePath\FileName" ,Yes


rptName is the name of the newly created report in ""
C:\FilePath\FileName is the full path and name for the output file in "" { .rtf is optional, so I dropped it in documentation}
all other values are reserved words.

I understand that the user is selecting a custom report, but I don't understand why a pop up form can't be used instead of an input message.  The form would permit record restriction and report layout options before 'calling' a saved report design which could be returned as an RTF file, Excel file, or Report Snapshot.  But, user requirements are just that, so I led with the code you needed.

0
 
Richard DanekeTrainerCommented:
BTW, output to Excel would be:

DoCmd.OutputTo acOutputReport, "rptName" , acFormatXLS, "C:\FilePath\FileName" ,Yes

 as AcFormat can be one of these AcFormat constants.
acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS

However, this will lose a lot of formatting and end up with mostly data and field headings.....
0
 
Richard DanekeTrainerCommented:
Jees, if I would slow down and read the questions.....

VB Code:

DoCmd.OutputTo acOutputReport, strReportName , acFormatRTF, "C:\FilePath\(folder0\" & strReportName ,Yes

strReportName as already supplied by your user and can be used as a filename as well
Yes will make Word open with your report.  If the code is excuted from the user, it will open your report that was created in Word for viewing and printing and whatever.
0
 
spacetravellerAuthor Commented:
DoDahd, Nic;o) Thank you very very much for your help. I will try these suggestions and get back to you.
THe input box is just for now, In the process I will incorporate that into a form that I am using as the front end.


Thanks,
st
0
 
spacetravellerAuthor Commented:
DoDahd, I tried and it worked! just as you told I did not get any graphics ported on the rtf file.
on the excel format even the column heading did not show up. instead it was Text0, text 1 .......
 Is there a way I can port the graphics also.


Thanks,
st
0
 
Richard DanekeTrainerCommented:
st,
I would use the SNP (Snapshot).  It will look like the report in Access.  Only users with the viewer installed will be able to see it, users cannot edit the file.
The viewer is free to distribute to all users that do not have Access installed.  (If Access is installed, they won't need to do anything special to open the report attachment.)
It is available if you use the following link http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en"
0
 
spacetravellerAuthor Commented:
sorry sorry sorry ... I mixed up the accepted answer and the assisted answer...

st
0
 
Richard DanekeTrainerCommented:
st,
IF that is your BIGGEST mistake of the day.......
Good to hear that your stuff is working!
0
 
spacetravellerAuthor Commented:
yes,  it worked!!! Thanks DoDahd
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.