Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 5th, 2003, 03:21 PM
Registered User
 
Join Date: Aug 2003
Location: Bournemouth, , United Kingdom.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel to word


I am having difficulty with some script that I have written, probably because I am learning this stuff as I go along!! The script below is basically to extract some data from a v large spreadsheet and populate that data into a letter via mail merge (this seems to me to be the best option). However Excel seems to hang for 2-3 mins before opening word, am I missing something fundamental? The other main problem is that the mail merge will only execute once after installation, in subsequent attempts to run the script the mail merge does not fully complete (i.e. the template document opens but not the merged doc). However to confuse matters if you go through the de-bugger 1 step at a time the mail merge completes.

If somebody could give me a few pointers on how to speed things up or even solve the above puzzle I would be v grateful. FYI I am running Office 2k Win XP Pro & have an Athalon 750 processor.

The script is:

'to open and populate the spreadsheet from which the mailmerge retreaves data

Application.Calculation = xlCalculationManual

Dim wdApp As Word.Application

strFilePath = "C:\trisigma\OfferLetters\" & Sheets("input").Range("C3").Value


Sheets("infoforol").Select
Range("A2:G2").Select
Selection.Copy

Workbooks.Open Filename:= _
"C:\TriSigma\HENDRED CALCULATOR\Export_to_offerlet.xls"
Range("A2:G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveWorkbook.Save
ActiveWorkbook.Close

'' THIS IS WHERE IT HANGS ''

On Error Resume Next
'try to establish link to open instance of word
Set wdApp = GetObject(, "word.application")
'if this fails, open Word

If wdApp Is Nothing Then
Set wdApp = GetObject("", "word.application")
End If

With wdApp

.Documents.Open Filename:="C:\trisigma\compensation.letter.doc"
.Visible = True


Range("A2:G2").Select
Range("F2").Activate
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Selection.HorizontalAlignment = xlLeft



With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

For Each docReport In appWord.Documents
If docReport.Name Like "Form Letters*" Then
docReport.Activate
Exit For
End If
Next

.ActiveDocument.SaveAs strFilePath





'Release object variable
Set wdApp = Nothing


Reply With Quote
  #2 (permalink)  
Old August 15th, 2003, 06:48 AM
Authorized User
 
Join Date: Aug 2003
Location: Penn Yan, New York, USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

sdowen,

Seems to me that you're going in reverse. If you goal is to extract data from a spreadsheet and run a mail merge process -- shouldn't you be running it from Word rather than Excel?

Fact is, you could simply turn on the Macro Recorder and record the process in Word. Then examine the recorded code and make variable whatever you needed for future processing.

Simply:

a) First set up the document with Fields (to which the spreadsheet data will be merged -- the field names should match the column names containing the data in the spreadsheet -- note: replace space with underscore characters)

a) Turn on the macro recorder (giving the macro a meaningful name)

b) Select the spreadsheet as the data source

c) Run the mail merge.

d) Stop the macro.

[Now you can examine the code and commands generated by Word.]

That's how I would do it, anyway.

CarlR

Reply With Quote
  #3 (permalink)  
Old June 20th, 2005, 03:41 AM
Registered User
 
Join Date: Jun 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I am trying to automate the Mail merge functionality of MS Word 2000, Excel 2000 as its datasource using ASP.Net/VB.Net but when I execute my code the following exception is generated :(:

System.Runtime.InteropServices.COMException (0x800A1722): Word was unable to open the data source. at Word.MailMerge.OpenDataSource(String Name, Object& Format, Object& ConfirmConversions, Object& ReadOnly, Object& LinkToSource, Object& AddToRecentFiles, Object& PasswordDocument, Object& PasswordTemplate, Object& Revert, Object& WritePasswordDocument, Object& WritePasswordTemplate, Object& Connection, Object& SQLStatement, Object& SQLStatement1) at FPA.JobIntegration.btnMailMerge_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\FPA\JobIntegration.aspx.vb:line 96


Please have a look at the code pasted below and do the needful.


Private Sub btnMailMerge_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMailMerge.Click
        Dim wrdApp As Word.Application
        Dim wrdDoc As Word._Document
        Dim wrdDoc1 As Word.MailMergeDataSource
        Dim wrdSelection As Word.Selection
        Dim wrdMailMerge As Word.MailMerge
        Dim wrdMergeFields As Word.MailMergeFields
        Dim fileWORDName, fileEXCELName As String

        Try
            wrdApp = New Word.Application

            wrdApp.ChangeFileOpenDirectory("C:\Inetpub\wwwroot \FPA\Templates\")
            fileWORDName = "ThankYou-Shalini.doc"

            wrdDoc = wrdApp.Documents.Open(FileName:=fileWORDName, ConfirmConversions:=False, ReadOnly _
            :=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate _
            :="", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="" _
            , Format:=Word.WdOpenFormat.wdOpenFormatAuto)

            wrdMailMerge = wrdDoc.MailMerge

            If wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdNotAMergeDocument Then
                wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
            End If

            wrdApp.Visible = True
            wrdDoc.ActiveWindow.Activate()
            wrdDoc.ActiveWindow.Visible = True

            fileEXCELName = "C:\Inetpub\wwwroot\FPA\Archive\FullfillmentReport 1.xls![FullfillmentReport]"


            wrdMailMerge.OpenDataSource(Name:=fileEXCELName, _
                ReadOnly:=False, LinkToSource:=True, _
                Connection:="DSN=Excel Files;DBQ=" & fileEXCELName & ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", _
                SQLStatement:="SELECT * FROM `Data`")


            ' Perform mail merge.
            wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
            wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
            wrdMailMerge.SuppressBlankLines = True
            wrdMailMerge.DataSource.FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
            wrdMailMerge.DataSource.LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
            wrdMailMerge.Execute(True)

            ' Close the original form document.
            'wrdDoc.Saved = True
            'wrdDoc.Close(False)

        Catch ex As Exception
            Response.Write(ex.ToString)
            Dim AllWORDProcess() As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessesByName("WIN WORD")
            Dim WordProcess As New System.Diagnostics.Process
            For Each WordProcess In AllWORDProcess
                WordProcess.Kill()
            Next
            WordProcess.Close()

            Dim AllEXCELProcess() As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessesByName("Exc el")
            Dim ExcelProcess As New System.Diagnostics.Process
            For Each ExcelProcess In AllEXCELProcess
                ExcelProcess.Kill()
            Next
            ExcelProcess.Close()
        Finally
            wrdMailMerge = Nothing
            wrdDoc = Nothing
            wrdApp = Nothing
        End Try

    End Sub




Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Charts to Word Sanjo VB How-To 1 March 31st, 2007 12:07 AM
Word to Excel hanzo Excel VBA 2 March 7th, 2007 05:18 AM
Merge to word from excel Kevinsharrison Access VBA 1 April 26th, 2005 07:40 AM
Excel Charts in Word migalley Excel VBA 4 March 5th, 2004 02:47 PM



All times are GMT -4. The time now is 05:36 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.