DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Need help on

  1. #1
    Join Date
    Aug 2011
    Location
    chicago
    Posts
    1

    Unhappy Word Automation Help using VB

    Hi Guys,
    I need some help on VB console app, and iam new to VB. Here we go, the project , I have a word template, where iam gonna perform a mail merge and send it to the printer. This merge process have to created like a batch or exe file, which will be scheduled to run accordingly. So i started creating a console app using VB. This console should connect to the SQL database and produce me some filtered rows based upon my SQL query. Now i need to create an array for the data rows which i received from the SQL query and save it as a excel or odc format. So that i can use this excel or odc file as my word template data source for mail merge.

    Following is the code i did so far to retrieve the data rows from my SQL query.
    Code:
    Imports System.Data.SqlClient
    Imports System.Console
    Imports System.NullReferenceException
    Imports Microsoft.Office.Interop
    Imports System
    Imports System.Data
    
    Module Module1
    
        Sub Main()
    
            Dim sConnectionString As String = "Server= ServerName; Database = DbName;" & "Integrated Security = SSPI"
    
            Dim Connection As New SqlConnection()
            Connection.ConnectionString = sConnectionString
            Connection.Open()
           
     Dim Dastudent As New SqlDataAdapter("SELECT student.last_name, student.first_name, student.ssn, student.address1_line1, student.address1_line2, student.city1, student.state1, student.zip_code1, student.Graduation_date, awarddata.status_code FROM student INNER JOIN awarddata ON student.person_id = awarddata.person_id WHERE (awarddata.status_code = 'O') and (student.Graduation_date >= '2010/05/01') order by first_name", sConnectionString)
    
            Dim Dsstudent As New DataSet("Student")
            Dastudent.FillSchema(Dsstudent, SchemaType.Source, "Student")
            Dastudent.Fill(Dsstudent, "Student")
    
            Dim tblstudent As DataTable
            tblstudent = Dsstudent.Tables("Student")
    
            Dim drCurrent As DataRow
            For Each drCurrent In tblstudent.Rows
                Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7} {8}", _
                    drCurrent("first_name").ToString, _
                    drCurrent("last_name").ToString, _
                    drCurrent("ssn").ToString, _
                    drCurrent("address1_line1").ToString, _
                    drCurrent("address1_line2").ToString, _
                    drCurrent("state1").ToString, _
                    drCurrent("city1").ToString, _
                    drCurrent("zip_code1").ToString, _
                    drCurrent("Graduation_date").ToString)
            Next
            Console.ReadLine()
            Connection.Close()
        End Sub
    
    End Module
    As a result it displays all the data row on the exe. I want the the displayed list to be saved in a excel format , so that i can use the excel as my word recipient list and populate the word template and send it to printer.

    The flow looks like touching my nose keeping a fat girl in the middle. but thats the best i came up with.

    Iam using mail merge because , iam generating form letters to be populated. So for sure, i will apreciate if anyone can help me.

    You guys rok..
    Last edited by momsaidnogf; 08-17-2011 at 03:00 PM. Reason: no one helped

  2. #2
    Join Date
    Aug 2011
    Location
    chicago
    Posts
    1

    Unhappy me again

    As mentioned above, i made some code changes to my app and now i can store the sql results as a excel document. Now i have to write the code to start word document and use my abc.dot template to perform mail merge and send it to my network printer.

    I tried searching on net, i did some coding work too, able to create the exact code , but the word never opens up. dunno why, here is my code. Please help me.
    Code:
    Imports System
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.IO
    Imports System.Data.SqlClient
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Core
    
    Module Module1
        Public conn As New SqlConnection
        Public Filename As String
        Public chkexcel As Boolean
        Public oexcel As Excel.Application
        Public obook As Excel.Workbook
        Public osheet As Excel.Worksheet
        Public R As Integer
        Dim WithEvents oApp As Word.Application
    
        Sub Main()
            Try
                Dbopen()
                'File name and path, here i used abc file to be stored in Bin directory in the sloution directory
                Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
                'check if file already exists then delete it to create a new file
                If File.Exists(Filename) Then
                    File.Delete(Filename)
                End If
                If Not File.Exists(Filename) Then
                    chkexcel = False
                    'create new excel application
                    oexcel = CreateObject("Excel.Application")
                    'add a new workbook
                    obook = oexcel.Workbooks.Add
                    'set the application alerts not to be displayed for confirmation
                    oexcel.Application.DisplayAlerts = True
                    'check total sheets in workbook
                    Dim S As Integer = oexcel.Application.Sheets.Count()
                    'leaving first sheet delete all the remaining sheets
                    If S > 1 Then
                        oexcel.Application.DisplayAlerts = False
                        Dim J As Integer = S
                        Do While J > 1
                            oexcel.Application.Sheets(J).delete()
                            J = oexcel.Application.Sheets.Count()
                        Loop
                    End If
                    'to check the session of excel application
                    chkexcel = True
    
    
                    oexcel.Visible = True
                    'this procedure populate the sheet
                    Generate_Sheet()
                    'save excel file
                    obook.SaveAs(Filename)
                    'end application object and session
                    osheet = Nothing
                    oexcel.Application.DisplayAlerts = False
                    obook.Close()
                    oexcel.Application.DisplayAlerts = True
                    obook = Nothing
                    oexcel.Quit()
                    oexcel = Nothing
                    chkexcel = False
                    'mail excel file as an attachment
                    automail("dinesh.kumar@isac.illinois.gov", "Auto Excel File", "any message", Filename)
                End If
            Catch ex As Exception
                'mail error message
                automail("err.mail@somedomain.com", "Error Message", ex.Message, "")
            Finally
                Dbclose()
            End Try
        End Sub
        Public Sub automail(ByVal mail_to As String, ByVal subject As String, ByVal msg As String, ByVal filename As String)
            Dim myOutlook As New Outlook.Application()
            Dim myMailItem, attach As Object
            myMailItem = myOutlook.CreateItem(Outlook.OlItemType.olMailItem)
            myMailItem.Body = msg
            If File.Exists(filename) Then
                attach = myMailItem.Attachments
                attach.Add(filename)
            End If
            If Trim(mail_to) <> "" Then
                myMailItem.to = Trim(mail_to)
            End If
            myMailItem.SUBJECT = subject
            myMailItem.send()
            myMailItem = Nothing
            myOutlook = Nothing
        End Sub
        Public Sub Dbopen()
            'open connection for db.mdb stroed in the base directory
            conn.ConnectionString = "Server= isacsqlsrvrqa; Database = TprogQA;" & "Integrated Security = SSPI"
            conn.Open()
        End Sub
        Public Sub Dbclose()
            'check and close db connection
            If conn.State = ConnectionState.Open Then
                conn.Close()
                conn.Dispose()
                conn = Nothing
            End If
            'check and close excel application
            If chkexcel = True Then
                osheet = Nothing
                oexcel.Application.DisplayAlerts = False
                obook.Close()
                oexcel.Application.DisplayAlerts = True
                obook = Nothing
                oexcel.Quit()
                oexcel = Nothing
            End If
            End
        End Sub
        Sub Generate_Sheet()
            Console.WriteLine("Generating Auto Report")
            osheet = oexcel.Worksheets(1)
            'format headings
            osheet.Range("A1:I1").Font.Color = RGB(255, 255, 255)
            osheet.Range("A1:I1").Interior.ColorIndex = 5
            osheet.Range("A1:I1").Font.Bold = True
            osheet.Range("A1:I1").Font.Size = 10
            'columns heading
            osheet.Range("A1").Value = "last_name"
            osheet.Range("A1").BorderAround(8)
            osheet.Range("B1").Value = "first_name"
            osheet.Range("B1").BorderAround(8)
            osheet.Range("C1").Value = "ssn"
            osheet.Range("C1").BorderAround(8)
            osheet.Range("D1").Value = "address1_line1"
            osheet.Range("D1").BorderAround(8)
            osheet.Range("E1").Value = "address1_line2"
            osheet.Range("E1").BorderAround(8)
            osheet.Range("F1").Value = "city1"
            osheet.Range("F1").BorderAround(8)
            osheet.Range("G1").Value = "state1"
            osheet.Range("G1").BorderAround(8)
            osheet.Range("H1").Value = "zip_code1"
            osheet.Range("H1").BorderAround(8)
            osheet.Range("I1").Value = "Graduation_date"
            osheet.Range("I1").BorderAround(8)
    
            'populate data from DB
            Dim SQlQuery As String = "SELECT student.last_name, student.first_name, student.ssn, student.address1_line1, student.address1_line2, student.city1, student.state1, student.zip_code1, student.Graduation_date, awarddata.status_code FROM student INNER JOIN awarddata ON student.person_id = awarddata.person_id WHERE (awarddata.status_code = 'O') and (student.Graduation_date >= '2010/05/01')"
            Dim SQLCommand As New SqlCommand(SQlQuery, conn)
            Dim SQlReader As SqlDataReader = SQLCommand.ExecuteReader
            Dim R As Integer = 1
            While SQlReader.Read
                R = R + 1
                osheet.Range("A" & R).Value = SQlReader.GetValue(0).ToString
                osheet.Range("A" & R).BorderAround(8)
                osheet.Range("B" & R).Value = SQlReader.GetValue(1).ToString
                osheet.Range("B" & R).BorderAround(8)
                osheet.Range("C" & R).Value = SQlReader.GetValue(2).ToString
                osheet.Range("C" & R).BorderAround(8)
                osheet.Range("D" & R).Value = SQlReader.GetValue(3).ToString
                osheet.Range("D" & R).BorderAround(8)
                osheet.Range("E" & R).Value = SQlReader.GetValue(4).ToString
                osheet.Range("E" & R).BorderAround(8)
                osheet.Range("F" & R).Value = SQlReader.GetValue(5).ToString
                osheet.Range("F" & R).BorderAround(8)
                osheet.Range("G" & R).Value = SQlReader.GetValue(6).ToString
                osheet.Range("G" & R).BorderAround(8)
                osheet.Range("H" & R).Value = SQlReader.GetValue(7).ToString
                osheet.Range("H" & R).BorderAround(8)
                osheet.Range("I" & R).Value = SQlReader.GetValue(8).ToString
                osheet.Range("I" & R).BorderAround(8)
            End While
            SQlReader.Close()
            SQlReader = Nothing     
        End Sub
    Until here everything works fine and i get the result for what i coded. But after when i run the mergeit code, when i compile according to the code, it has to open the word doc, and mail merge. But instead result will extract to the excel and excel closes. but word never even opens.

    Code:
    Public Sub MergeIt()
            'Open the word application
            Dim oApp As Word.Application
            Dim oMainDoc As Word.Document
            'Opening the Word Template
            oApp = CreateObject("word.Application")
            oMainDoc = GetObject("C:\Documents and Settings\DKumar\My Documents\Downloads\Excel_Automation_src\bin\August_15.doc", "Word.Document")
            oMainDoc.Application.Visible = True
            'Perform mail merge
            With oMainDoc.MailMerge
                .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
    
                'Set up the mail merge data source
                oMainDoc.MailMerge.OpenDataSource _
        (Name:="C:\Documents and Settings\DKumar\My Documents\Downloads\Excel_Automation_src\bin\abc.xls", _
        LinkToSource:=True, Connection:="Entire Spreadsheet", _
     SQLStatement:=("SELECT * FROM `Sheet1$`"))
                : oMainDoc.MailMerge.Execute()
    
            End With
            'Confirming the 
            With oMainDoc
                .MailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
                .MailMerge.Execute(Pause:=False)
            End With
        End Sub
        
        Public Sub oMainDoc_MailMergeAfterMerge(ByVal Doc As Word.Document, ByVal DocResult As Word.Document)
    
            'When the mail merge is complete, 1) make Word visible,
            '2) close the mail merge document leaving only the resulting document
            'open and 3) display a message.
            Doc.Close(False)
            oApp.Visible = True
            'I will add the message later
        End Sub
        Public Sub final(ByVal cancel As Integer)
            oApp = Nothing
        End Sub
    End Module
    Last edited by Hack; 08-26-2011 at 09:18 AM. Reason: Added Code Tags

  3. #3
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    A lot of work, reading in SQL Server, transferring to Excel and then mail merge in Word.

    Word is able to read it's Mail Merge information directly from a database, without any code. A table or a view can become the source for a Mail Merge. You do not need to translate everything to Excel. You could prepare everything in Word and simply open and print the document. You would save a lot of code, the thing would be faster and won't require as much maintenance when you move from one version of Office to another.

    Unfortunately, showing you how to create a mail merge in Word would take too long and be out of context in this forum. Try to find a Word forum.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center