Word Report using excel database

[samfolds]

Hello all,

I'm looking for a way to save/print several reports by changing only certain information in that report. Those information are placed in an excel database and all I want to do is somehow create a word template and link each column to a certain place. For example, say my excel database had the columns :

NamesAddressPhoneSam1234 pool st.123-456-7891Jack4321 sack st.323-525-9874Gil654 8th avenue325-525-8787Andrew987 9th st.322-252-5252

And my reports looks like this :

*****************************************
Hello "NAME",

blablablabla "PHONE" blablablabla.

blablablablablabla "ADDRESS".
*****************************************


I would like to loop through all the rows (1 report per row) and save the report in the form : Report-2010-"NAME".doc ...

Can someone enlight me on how to do that?

Thank you very much.

Samfolds

 

[prasadavasare]

Hi,
Can anybody resolve this problem....I need to fixed this.....

Thank you,

 

[Derek Brown]

Could you post the code that you are using, please - it is sometimes not easy to determine an error when looking at only part of the code. I can then try it on my system to see if I can find a solution.
Also please confirm the versions that you are using - XP and Office 2007?

 

[prasadavasare]

Thank You Derek...I am using Excel 2007.....I am getting Run time error 13 Type-Mismatch.............. Here is the code I am using.

Option Explicit
Sub Create_Letters()
' Note: you will need to add error-trapping
Dim objX As Object
Dim rng1 As Range
Dim rng2 As Range
Dim wb As Workbook
Dim wsControl As Worksheet
Dim wsData As Worksheet
'
Dim oApp As Word.Application
Dim oBookMark As Word.Bookmark
Dim oDoc As Word.Document
'
Dim strDocumentFolder As String
Dim strTemplate As String
Dim strTemplateFolder As String
Dim lngTemplateNameColumn As Long
Dim strWordDocumentName As String
Dim lngDocumentNameColumn As Long
Dim lngRecordKount As Long ' not used but retained for future use
'
Set wb = ThisWorkbook
Set wsControl = wb.Worksheets("Control Sheet")
wsControl.Activate
Set wsData = wb.Worksheets(wsControl.[Data_Sheet].Value)
strTemplateFolder = wsControl.[Template_Folder].Value
strDocumentFolder = wsControl.[Document_Folder].Value
wsData.Activate
lngTemplateNameColumn = wsData.[Template_Name].Column
lngDocumentNameColumn = wsData.[Document_Name].Column
' number of letters required:
' must not have any blank cells in column A - except at the end
Set rng1 = wsData.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
lngRecordKount = rng1.Rows.Count
'
Set oApp = CreateObject("Word.Application")
'Set oApp = New Word.Application
' Process each record in turn
For Each rng2 In rng1
strTemplate = strTemplateFolder & "\" & wsData.Cells(rng2.Row, lngTemplateNameColumn)
strWordDocumentName = strDocumentFolder & "\" & wsData.Cells(rng2.Row, lngDocumentNameColumn)
' check that template exists
If Dir(strTemplate) = "" Then
MsgBox strTemplate & " not found"
GoTo Tidy_Exit
End If
Set oDoc = oApp.Documents.Add
oApp.Selection.InsertFile strTemplate
' locate each bookmark
For Each oBookMark In oDoc.Bookmarks
Set objX = wsData.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, LookAt:=xlWhole)
If Not objX Is Nothing Then
' found
If Right(oBookMark.Name, 4) = "Date" Then
oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
ElseIf Right(oBookMark.Name, 6) = "Amount" Then
oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
Else
oBookMark.Range.Text = wsData.Cells(rng2.Row, objX.Column)
End If
Else
MsgBox "Bookmark '" & oBookMark.Name & "' not found", vbOKOnly + vbCritical, "Error"
GoTo Tidy_Exit
End If
Next oBookMark
'
oDoc.SaveAs strWordDocumentName
oDoc.Close
Next rng2
'
Tidy_Exit:
On Error Resume Next
Set oDoc = Nothing
Set oBookMark = Nothing
Set objX = Nothing
Set rng1 = Nothing
Set rng2 = Nothing
oApp.Quit
Set oApp = Nothing
'
Set wsData = Nothing
Set wsControl = Nothing
Set wb = Nothing
End Sub
'This has been tested - in fact it was all created from scratch. I use both 2003 and 2007 and my code was more complex and had different requirements, so I created this Excel 2003 example to meet your requirements.

 

[Derek Brown]

I have copied the code into an Excel workbook and it "compiles" OK.
I need to create the files, worksheets etc. before I can try running it.
From your last post it seems that you are back to the original error at the following line:

Set oApp = CreateObject("Word.Application")

Could you confirm that my understanding is correct, please?
Also, could you let me know the 'References' (Tools/References) that have been selected AND the order that they appear (sometimes that is important).
Are you running on Windows XP?

 

[prasadavasare]

Hi Derek,
Yes....you are correct I am getting the error on same line. I have checked Visual Basic for Application, Microsoft Excel 12.0 Object Library, OLE Automation, Microsoft Office 12.0 Object Library,Microsoft ActiveX data objects 6.0 Library, Microsoft Word 12.0 Object Library, Microsoft Shell Controls and Automation.
I am using windows Vista

 

[Derek Brown]

Thanks - leave it with me for a few hours and I will set up my files and worksheets and see what I can find.

 

[Derek Brown]

Right, this is intriguing ....
First, some background - the code originally was used and tested using both Office 2003 and Office 2007 on Windows XP. I am now using Windows 7 Ultimate 64-bit and Office 2010 32-bit (64-bit Office 2010 is troublesome).
I copied the code that you posted, set the 'References' exactly as you have set and .... it worked first time without any problem.
The only differences are that you are using Vista and I would have set fewer 'References' when I originally tested the code. That is assuming that this is the only code in the workbook.
For this particular code to run you only need "Visual Basic for Application, Microsoft Excel 12.0 Object Library and Microsoft Word 12.0 Object Library" to be set.
My suggestion is to remove the unwanted references, save the workbook, close and then re-open the workbook, compile the code and try again. If you cannot remove all the references because they are needed by other code in the workbook, do as I have just described but ensure that the three 'essential' ones for this code are at the top of the list.
Try that first and let me know how it goes.

 

[prasadavasare]

Hi Derek,

Thank you very much for your efforts....but Bad news.....I did try with just the 3 references you have given....still not working....still getting the same error...the same error....

 

[prasadavasare]

Hi Derek,
I have done some changes in the code....that may be resolving the problem for the line for which I am getting error....but now I am facing the new error on the other line....can you please check and let me know:

The line I have changed is:
Dim oApp As Object
Dim oDoc As Object


The error now I am facing in line is:
For Each oBookMark In oDoc.Bookmarks

The error is Type mismatch


I Hope this might help you in solving the issue...the entire code I am pasting again for better picture

Option Explicit
Sub Create_Letters()
' Note: you will need to add error-trapping
Dim objX As Object
Dim rng1 As Range
Dim rng2 As Range
Dim wb As Workbook
Dim wsControl As Worksheet
Dim wsData As Worksheet
'
Dim oApp As Object
Dim oBookMark As Word.Bookmark
Dim oDoc As Object
'
Dim strDocumentFolder As String
Dim strTemplate As String
Dim strTemplateFolder As String
Dim lngTemplateNameColumn As Long
Dim strWordDocumentName As String
Dim lngDocumentNameColumn As Long
Dim lngRecordKount As Long ' not used but retained for future use
'
Set wb = ThisWorkbook
Set wsControl = wb.Worksheets("Control Sheet")
wsControl.Activate
Set wsData = wb.Worksheets(wsControl.[Data_Sheet].Value)
strTemplateFolder = wsControl.[Template_Folder].Value
strDocumentFolder = wsControl.[Document_Folder].Value
wsData.Activate
lngTemplateNameColumn = wsData.[Template_Name].Column
lngDocumentNameColumn = wsData.[Document_Name].Column
' number of letters required:
' must not have any blank cells in column A - except at the end
Set rng1 = wsData.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
lngRecordKount = rng1.Rows.Count
'
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
'Set oApp = New Word.Application
' Process each record in turn
For Each rng2 In rng1
strTemplate = strTemplateFolder & "\" & wsData.Cells(rng2.Row, lngTemplateNameColumn)
strWordDocumentName = strDocumentFolder & "\" & wsData.Cells(rng2.Row, lngDocumentNameColumn)
' check that template exists
If Dir(strTemplate) = "" Then
MsgBox strTemplate & " not found"
GoTo Tidy_Exit
End If
Set oDoc = oApp.Documents.Add
oApp.Selection.InsertFile strTemplate
' locate each bookmark
For Each oBookMark In oDoc.Bookmarks
Set objX = wsData.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, LookAt:=xlWhole)
If Not objX Is Nothing Then
' found
If Right(oBookMark.Name, 4) = "Date" Then
oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "dd mmmm yyyy")
ElseIf Right(oBookMark.Name, 6) = "Amount" Then
oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "£#,##0.00")
Else
oBookMark.Range.Text = wsData.Cells(rng2.Row, objX.Column)
End If
Else
MsgBox "Bookmark '" & oBookMark.Name & "' not found", vbOKOnly + vbCritical, "Error"
GoTo Tidy_Exit
End If
Next oBookMark
'
oDoc.SaveAs strWordDocumentName
oDoc.Close
Next rng2
'
Tidy_Exit:
On Error Resume Next
Set oDoc = Nothing
Set oBookMark = Nothing
Set objX = Nothing
Set rng1 = Nothing
Set rng2 = Nothing
oApp.Quit
Set oApp = Nothing
'
Set wsData = Nothing
Set wsControl = Nothing
Set wb = Nothing
End Sub
'This has been tested - in fact it was all created from scratch. I use both 2003 and 2007 and my code was more complex and had different requirements, so I created this Excel 2003 example to meet your requirements.

 

[Derek Brown]

That change does work OK for me.
Unfortuinately I don't have Vista and Excel 2007 available to try with that, but I wonder if we are getting closer ....
Try changing:

Dim oBookMark As Word.Bookmark

to:

Dim oBookMark As Object

That too works for me and by not declaring the application at that point, means that we are using 'late binding'.
By making this change, it is no long necessary to have the 'Reference' to Word so you could also try removing that.