Library tutorials & articles
Visual Basic 6.0 - Exporting a DataReport to Microsoft Word
Visual Basic 6.0 - Exporting a DataReport to Microsoft Word
How to: Export a DataReport to Microsoft Word (.doc)
By John D. Conway
In this article we'll create a simple database in Microsoft Access and then create a user interface in Visual Basic 6.0.
Putting everything together should be relatively easy because we'll use Adodc Componenet to make the connection to our Database.
We'll then add a DataEnvironment and DataReport and within minutes we'll be able to export our DataReport to a Microsoft Word Document. (.doc)
Creating our Database in Microsoft Access:
Create a blank database in Microsoft Access and name the database: DataReport. Now create a table by going to: Create Table in Design View and enter the Field Name(s) and Data Type(s) as shown in the image below. Save the table and name it: Customers.
Now, open the Customers Table and fill in the information you see in the image below.
Now let's move on to creating a user interface in Visual Basic 6.0. We're almost there. We"ll just place a few controls and in a short time we'll be able to Export our DataReport to a Microsoft Word. (.doc)
Creating our User Interface in Visual Basic 6.0
Open Visual Basic and choose: Standard EXE. Add a Adodc Component by going to Projects, Components and choose Microsoft ADO Data Control. Click Apply and then Close. You will now have a this Component in your ToolBox. Add 9 TextBox(s) named: Text1, Text2, Text3 and so on. Now add 9 Labels named: Label1, Label2, Label3 and so on. Change the Caption Property for each Label as shown in the image below. Add the Adodc Control from our ToolBox named: ADODC1. Add a CommandButton named: Command1. Change the Caption of the CommandButton1 to View Report. Now we just need to make the connection.
If you're wanting to Export a DataReport to Microsoft Word then I would guess that you already know the steps that need to be taken. This expanation will help those who are doing this for the first time or maybe have forgotten a step or two.
Right click ADODC1 control that we'd placed on Form1 and right click ADODC Properties. Choose: Use Connection String and left click Build. When prompted Choose Microsoft Jet 4.0 OLE DB Provider and click Next. Now, click the button to the right where you see 1. Select or enter a database name. Go to the location where you'd saved your database and double click. Check or Test your connection. If everything is on track you'll see a messagebox with the message "Test Connection Succeeded"-- Press OK to close the message and then press OK again.
Now, go to where you see RecordSource and left click. For the CommandType choose 2 - adCmdTable. Now go to Table or Stored Procedure Name and choose Customers (or whatever you'd named your Table) Now just click Apply and then click OK.
Now we'll set the DataSorce and DataField Properties to each Textbox that we'd place onto our Form1 earlier.
Click Text1 and then go to the Properties Window. Set the DataSource Property to ADODC1 and the DataField Property to CustID. (Note: All of the remaining TextBox(s) DataSource Properties will be set to ADODC1) Now go to Text2 and Set the DataSource Property. Set the DataField Property to LastName. Go to Text3 and Set the DataSource Property. Set the DataField Property to FirstName. Set the DataSource and DataField Properties for the remaining TextBox(s).
Adding a DataEnvironment and DataReport
Go to Project and choose More ActiveX Designers. Now choose Data Environment. Once opened - right click on Connection1 and choose Properties. Choose Microsoft Jet 4.0 OLE DB. Now, click the button to the right where you see 1. Select or enter a database name. Go to the location where you'd saved your database and double click. Check or Test your connection. If everything is on track you'll see a messagebox with the message "Test Connection Succeeded". Click OK.
Right click on connection again but this time choose Add Command. Now right click on Command1 and click Properties. Set the DatabaseObject to Table and then set the ObjectName to Customers. Click Apply and then click OK.
The final result will look like the image below.
We are now ready to add a DataReport.
Go to Project and Choose Add Data Report. Adjust the size of the DataEnvironment and DataReport to resemble the image below.
Click the DataReport. Go to DataSource and change the property to DataEnvironment1 and then change the DataMember Property to Command1.
Now place your cursor on CustomerID - left click and hold-Now just drag over to Detail(Section1) You'll see two CustID. Choose the one on the left and drag it above to PageHeading(Section2) Repeat the same process to place LastName, FirstName, MI, Phone, Address, City, State and Zip.
Now double click the commandbutton that's on Form1 to bring up the Code Window. Enter DataReport1.Show in the Command1_Click event.The final result will resemble the image below.
Start the project and then click the CommandButton to View Report. The report will look similar to the image below.
Notice the two buttons at the top left corner of your report. The button on the left will Print and the one on the right will allow us to Export. We'll use the Export feature in a moment.
You may need to adjust here and there to achieve the proper width of each column. This is where it will going to get a little tricky because we're only allowed a certain amount of space for the fields that we want to export.
What may look acceptable in the DataReport will not in Microsoft Word. So, I guess we can call this the experimental stage of the process. I've exported several DataReports this way and each time the results may vary. You'll get the hang of it once you've messed with it a time or two.
If you find yourself having trouble - there's a download at the bottom of this article that has everything you need.
Export a DataReport to Microsoft Word (.doc)
Start the project and click the View Report Button on the form. Click the Export button. Choose a location where you want to save the Documnent. Go to where you see File Name and type in the name DataReport.doc (Note: You must include .doc extension at the end of the File Name). Now go to Save as Type and choose TXT(*.txt) Now just click Save.
Go to where you'd saved the DataReport and double click to open. You will get a prompt that states "Microsoft Word can't import the specified format. The feature is not currently installed. Would you like to install it now?" Choose NO. The DataReport will now open in Microsoft Word. If the Report in Microsoft Word doesnt resemble the image below - Go to File ( In Microsoft Word) and then down to Page Setup...Go to PaperSize, choose LandScape and press OK. The report will adjust. If this doesnt fit all the fields the yoy will either have to - 1. Go back to the DataReport and make some adjustments, or 2. Go back to your DataBase and shorten the Field Names.
If the Report looks like you want it too and to avoid seeing the message that we encountered when we first opened MS Word to view the report - Go to file and choose Save As...Go to Save As Type and choose Word Document.
Download the project that accompanies this article
Source Code
http://home.comcast.net/~conwayjd/INDEX.HTML
Option Explicit
'view datareport
Private Sub Command1_Click()
DataReport1.Show
End Sub
Related articles
Related discussion
-
help me sql string error
by radha45 (2 replies)
-
SQL help needed
by vsjagtap (2 replies)
-
Importing CSV files into records on MS Access using VB
by jefftullin (3 replies)
-
Please help me...!
by jefftullin (1 replies)
-
How to programmatically create files in vb during runtime
by mickyfyn (2 replies)
Events coming up
-
Nov
19
C# 3.0 and LINQ with Visual Studio 2008 Training Course
London, United Kingdom
This course has been developed to help existing C#.NET 2.0 programmers and developers upgrade their .NET development skills and learn about the new features of Microsoft's C# 3.0 and LINQ to XML and ADO.NET using Visual Studio 2008 (currently codenamed Orcas).
Please Post the coding to get the hex Value of Character of Font which is given by the User to Display on the LED. Please Help me.
I want that coding to interact with Font Hex Value
Thanks but I have solved it now but I have another problem now, can we set the datareport into 1/2 page only? I mean like the 1/2 size of the short bond paper, I tried to use the reportwidth, the height, the printer.papersize but still don't work. the datareport is still in a one whole page.
Why nor Mailmerge it?
Hi.
Can you help me about the data report... What I want to be is that I want the datareport scale that is in landscape form.. but when I tried to adjust the report width or its width properties it gives an error "Report width is larger than the paper width." Can you help solve that problem....
thank you very much!
jireh
Hi,
Using data reports in VB6 is a headache espically when it is a little more complex than the case we have here. Most of the time reports contains parent data in the header in addition to child sub form or grid. Personally I had a lot of problems trying to do it through VB6 data enviroment report so I came to a trick to use internet explorer or Excel as my printing utilities where I have prepared a template for excel in which I export my data to print it and close the object all in the background. Same for internet explorer but it doesn't need a template. It is very fast and reliable (faster and more reliable than crystal reports) but of course you will need to use some excel VBA and HTML. I have enclosed below the last examples I used this code in.
HTML
Private Sub cmdExport_Click()
Dim A As Integer
Dim s As Integer
On Error GoTo E
If Me.txtField(0) = "" Or Me.txtField(7) = "" Or Not Me.MSHFlexGrid1.Rows > 0 Then
MsgBox "There is no result to Export", , "Export Failure"
Else
Dim str As String
str = "<html><head></head><body><h1><b>Specialized Medical Center Hospital</b></h><h3><b>Tel. 4164000 Fax 4648493</b></h><br><br><br>" & _
"<table><tr><td><b>Patient ID</b></td><td>" & _
Me.txtField(7) & _
"</td><td> </td><td><b>Register Date</b></td><td>" & _
Me.txtField(1) & _
"</td></tr><tr><td><b>Patient Name</b></td><td>" & _
Me.txtField(5) & _
"</td><td> </td><td><b>Register Time</b></td><td>" & _
Me.txtField(2) & _
"</td></tr><tr><td><b>Date of Birth</b></td><td>" & _
Me.txtField(4) & _
"</td><td> </td><td><b>Priority</b></td><td>" & _
Me.txtField(3) & _
"</td></tr><tr><td><b>Gender</b></td><td>" & _
Me.txtField(8) & _
"</td><td> </td><td><b>Doctor</b></td><td>" & _
Me.txtField(9) & _
"</td></tr><tr><td><b>Sample ID</b></td><td>" & _
Me.txtField(0) & _
"</td><td> </td><td><b>Specimen</b></td><td>" & _
Me.txtField(6) & _
"</td></tr></table><br><table border=1 width=650><tr height=33><td><b>Test Name</b></td><td><b>Result</b></td><td><b>Unit</b></td><td colspan=2><b>Reference Range</b></td><td><b>Result Date</b></td><td><b>Verified</b></td></tr>"
For A = 0 To Me.MSHFlexGrid1.Rows - 1
If ttype <> "limited" Or Me.MSHFlexGrid1.TextMatrix(A, 7) = "Verified" Then
str = str & "<tr height=42><td>" & Me.MSHFlexGrid1.TextMatrix(A, 1) & _
"</td><td>" & IIf(Me.MSHFlexGrid1.TextMatrix(A, 2) = "", " ", Me.MSHFlexGrid1.TextMatrix(A, 2)) & _
"</td><td>" & Me.MSHFlexGrid1.TextMatrix(A, 3) & _
"</td><td align=center>" & IIf(Me.MSHFlexGrid1.TextMatrix(A, 5) = "", " ", Me.MSHFlexGrid1.TextMatrix(A, 5)) & _
"</td><td>" & IIf(Me.MSHFlexGrid1.TextMatrix(A, 4) = "", " ", Me.MSHFlexGrid1.TextMatrix(A, 4)) & _
"</td><td>" & IIf(Me.MSHFlexGrid1.TextMatrix(A, 6) = "", " ", Me.MSHFlexGrid1.TextMatrix(A, 6)) & _
"</td><td>" & Me.MSHFlexGrid1.TextMatrix(A, 7) & "</td></tr>"
End If
Next
str = str & "</table><br><br><h6 align=right>Exported On " & Now() & " by " & title & " " & fullname & "</h></p></body></html>"
Dim Y As String
Y = InputBox("What do you want to name your file", "File Naming", "MRN")
Y = IIf(IsNull(Y), "MRN", Y)
WriteTextFile "c:\" & Y & ".html", str
MsgBox "Your file was saved as C:\" & Y & ".html", , "Save Confirmation"
End If
E1:
Exit Sub
E:
MsgBox "There was an error exporting the data", , "Export Failure"
On Error GoTo E1
End Sub
EXCEL
Private Sub cmdPrint_Click()
On Error GoTo E
If Me.txtField(0) = "" Or Me.txtField(7) = "" Or Not Me.MSHFlexGrid1.Rows > 0 Then
MsgBox "There is no result to print", , "Print Failure"
Else
Dim xl
Dim xlsheet
Dim xlwbook
Set xl = CreateObject("Excel.Application")
Dim A As Integer
Dim s As Integer
Set xlwbook = xl.Workbooks.Open("C:\Lab\report1.xls")
Set xlsheet = xlwbook.Sheets.Item(1)
With xlsheet
.range("B10") = Me.txtField(0)
.range("B11") = Me.txtField(7)
.range("B12") = Me.txtField(5)
.range("B13") = Me.txtField(4)
.range("B14") = Me.txtField(8)
.range("F10") = Me.txtField(1)
.range("F11") = Me.txtField(2)
.range("F12") = Me.txtField(3)
.range("F13") = Me.txtField(9)
.range("F14") = Me.txtField(6)
End With
x = 19
For A = 0 To Me.MSHFlexGrid1.Rows - 1
With xlsheet
If ttype <> "limited" Or Me.MSHFlexGrid1.TextMatrix(A, 7) = "Verified" Then
.range("A" & x) = Me.MSHFlexGrid1.TextMatrix(A, 1)
.range("B" & x) = Me.MSHFlexGrid1.TextMatrix(A, 2)
.range("C" & x) = Me.MSHFlexGrid1.TextMatrix(A, 3)
.range("D" & x) = Me.MSHFlexGrid1.TextMatrix(A, 5)
.range("E" & x) = Me.MSHFlexGrid1.TextMatrix(A, 4)
.range("F" & x) = Me.MSHFlexGrid1.TextMatrix(A, 6)
.range("G" & x) = Me.MSHFlexGrid1.TextMatrix(A, 7)
x = x + 1
End If
End With
Next
xlsheet.PrintOut
xl.ActiveWorkbook.Close False, "C:\Lab\report1.xls"
xl.Quit
xl.Quit
Set xl = Nothing
Set xlwbook = Nothing
End If
E1:
Exit Sub
E:
xl.ActiveWorkbook.Close False, "C:\Lab\report1.xls"
xl.Quit
xl.Quit
Set xl = Nothing
Set xlwbook = Nothing
MsgBox "There was an error printing your report please contact your IT representative", , "Print Failure"
On Error GoTo E1
End Sub
WHAT TO DO IF I WANT TO INCREASE THE PAGE LENGTH, LIKE I WANT TO TAKE THE PRINT OUT ON AN 132 Col. PAPER ON A PRINTER LIKE EPSON. HOW TO INCREASE THE PAGE SIZE BY PROGRAMMING OR TO GENERATE A REPORT ON 132 Col PAPER?
After designing the VB you can go for
Project Menu
Then References option and then Microsoft Word 11.0 Library
Now you can write code for displaying it.
You can find "More ActiveX Designers" by going to the Project menu. You will see "More ActiveX Designers" there. You can also find it here by: Go to your project window and right click as if your going to Add a new form, instead of choosing a form just select "More ActiveX Designers" and then Data Environment.
Hope this helps.
John
Thanks for this nice tutorial!
I actually tried to do this example my selef but, I have reached to dead point coz I was not able know how to load the DataEnvironment.
So please can you help me in this issue.
P.S. I tried to find the "More ActiveX Designers" from the "Component" list but I didn't find it.
Thanks once more for Developer Fusion's team for their valued efforts!!
Best regards,
Mahmoud Abu Baker