Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

exporting to a MS word and MS Word from access programmatically

Posted on 2006-04-24
18
Medium Priority
?
568 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:spacetraveller
  • 7
  • 7
  • 4
18 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16527865
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
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 16527870
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
 

Author Comment

by:spacetraveller
ID: 16527941
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 54

Expert Comment

by:nico5038
ID: 16528092
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
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 16528374
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
 

Author Comment

by:spacetraveller
ID: 16528731
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16528984
Why dynamically creating the report ?
Did you check the Word Mailmerge ?

Nic;o)
0
 

Author Comment

by:spacetraveller
ID: 16529171


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

Accepted Solution

by:
nico5038 earned 300 total points
ID: 16529793
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
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 16530386
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
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 16530397
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
 
LVL 19

Assisted Solution

by:Richard Daneke
Richard Daneke earned 1200 total points
ID: 16530409
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
 

Author Comment

by:spacetraveller
ID: 16532652
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
 

Author Comment

by:spacetraveller
ID: 16546501
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
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 16546916
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
 

Author Comment

by:spacetraveller
ID: 16547246
sorry sorry sorry ... I mixed up the accepted answer and the assisted answer...

st
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 16547427
st,
IF that is your BIGGEST mistake of the day.......
Good to hear that your stuff is working!
0
 

Author Comment

by:spacetraveller
ID: 16547508
yes,  it worked!!! Thanks DoDahd
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question