Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports Office = Microsoft.Office.Core
Imports Word = Microsoft.Office.Interop.Word
Imports MSForms = Microsoft.Vbe.Interop.Forms

' Office integration attribute. Identifies the startup class for the document. Do not modify.
<Assembly: System.ComponentModel.DescriptionAttribute("OfficeStartupClass, Version=1.0, Class=CommandBar.OfficeCodeBehind")>

Public Class OfficeCodeBehind

    Friend WithEvents ThisDocument As Word.Document
    Friend WithEvents ThisApplication As Word.Application
    Private CBar As Office.CommandBar
    Private CBarComboBox As Office.CommandBarComboBox
    Private WithEvents CBarButton As Office.CommandBarButton

    Private Sub CBarButton_Click(ByVal btn As Office.CommandBarButton, ByRef Cancel As Boolean) Handles CBarButton.Click
        'Get word ready to receive data
        Dim sln As Word.Selection = Nothing
        sln = ThisApplication.Selection
        ThisApplication.Options.Overtype = False

        'Get customer id back into individual fields
        Dim wrkString As String = CBarComboBox.Text
        Dim Customer As String() = wrkString.Split("-"c)

        'Get customer information from database
        Dim conn As SqlConnection = New SqlConnection("data source=localhost; initial catalog=Northwind;integrated security=true")
        Dim cmd As SqlCommand = New SqlCommand("select * from customers where customerid = @CustID", conn)
        cmd.Parameters.Add("@CustID", Customer(0))
        Dim dr As SqlDataReader
        conn.Open()
        dr = cmd.ExecuteReader()

        'Output customer Address Data to word
        Do While dr.Read()
            sln.TypeText(dr("CustomerID").ToString + vbCrLf)
            sln.TypeText(dr("CompanyName").ToString + vbCrLf)
            sln.TypeText(dr("ContactName").ToString + vbCrLf)
            sln.TypeText(dr("Address").ToString + vbCrLf)
            sln.TypeText(dr("City").ToString() + ", ")
            sln.TypeText(dr("Region").ToString() + "  ")
            sln.TypeText(dr("PostalCode").ToString + vbCrLf)
            sln.TypeText(dr("Country").ToString())
        Loop
        dr.Close()
        conn.Close()
    End Sub

    Private Function SetupCommandBar() As Boolean
        Try
            'Create command bar for this document
            ThisApplication.CustomizationContext = ThisDocument
            CBar = ThisApplication.CommandBars.Add("Customer Form")

            'Add a combo box to the command bar
            Dim objType As Object = Office.MsoControlType.msoControlDropdown
            CBarComboBox = CType(CBar.Controls.Add(objType), Office.CommandBarComboBox)
            CBarComboBox.Caption = "Select: "
            CBarComboBox.Style = Office.MsoComboStyle.msoComboLabel
            CBarComboBox.ListIndex = 0
            CBarComboBox.Width = 250
            CBarComboBox.BeginGroup = True

            'Add items to the combo box from database
            Dim conn As New SqlConnection("data source=localhost;initial catalog=Northwind;integrated security=true")
            Dim cmd As New SqlCommand("Select CustomerID, CompanyName from customers", conn)
            Dim dr As SqlDataReader
            conn.Open()
            dr = cmd.ExecuteReader
            Dim i As Integer = 0
            While dr.Read
                i += 1
                CBarComboBox.AddItem(String.Format("{0} - {1}", dr("CustomerID"), dr("CompanyName")))
            End While
            dr.Close()
            conn.Close()

            'Add a button to the command bar to insert data
            CBarButton = CType(CBar.Controls.Add(CType(1, Object)), Office.CommandBarButton)
            CBarButton.Style = Office.MsoButtonStyle.msoButtonCaption
            CBarButton.Caption = "Insert"

            'Make the new command bar visible
            CBar.Visible = True
            Return True
        Catch ex As Exception
            MessageBox.Show("Error creating CommandBar: " + ex.Message, "Customer Form", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function


    ' Determines whether ActiveX controls will be set to
    ' Run mode even if Word is in High security mode.
    ' Set this variable to True if your solution uses
    ' ActiveX controls.
    Private toggleActiveXControls As Boolean = False

#Region "Generated initialization code"

    ' Default constructor.
    Public Sub New()
    End Sub

    Private designModeClose As Boolean = False

    ' Required procedure. Do not modify.
    Public Sub _Startup(ByVal application As Object, ByVal document As Object)
        ThisApplication = CType(application, Word.Application)
        ThisDocument = CType(document, Word.Document)

        ' Check if the document is in Design mode.
        If (ThisDocument.FormsDesign = True) Then
            ' Set ActiveX controls to Run mode.
            If (toggleActiveXControls = True) Then
                ThisDocument.ToggleFormsDesign()
            End If

            ' Check if the ThisDocument is a document and not a template.
            If (ThisDocument.Path <> "") Then
                ' Set the shutdown procedure to use design mode and then call the Open handler.
                designModeClose = True
                ThisDocument_Open()

                ' Ensure that Open is not called twice.
                RemoveHandler ThisDocument.Open, AddressOf ThisDocument_Open
            End If
        End If
    End Sub

    ' Required procedure. Do not modify.
    Public Sub _Shutdown()
        ' If the document is in design mode, call the Close handler.
        If (designModeClose = True) Then
            ThisDocument_Close()
        End If

        ThisApplication = Nothing
        ThisDocument = Nothing
    End Sub

    ' Returns the control with the specified name in ThisDocument.
    Overloads Function FindControl(ByVal name As String) As Object
        Return FindControl(name, ThisDocument)
    End Function

    ' Returns the control with the specified name in the specified document.
    Overloads Function FindControl(ByVal name As String, ByVal document As Word.Document) As Object
        Try
            Dim inlineShape As Word.InlineShape
            For Each inlineShape In document.InlineShapes
                If (inlineShape.Type = Word.WdInlineShapeType.wdInlineShapeOLEControlObject) Then
                    Dim oleControl As Object = inlineShape.OLEFormat.Object
                    Dim oleControlType As Type = oleControl.GetType()
                    Dim oleControlName As String = CType(oleControlType.InvokeMember("Name", _
                    Reflection.BindingFlags.GetProperty, Nothing, oleControl, Nothing), String)
                    If (String.Compare(oleControlName, name, True, System.Globalization.CultureInfo.InvariantCulture) = 0) Then
                        Return oleControl
                    End If
                End If
            Next

            Dim shape As Word.Shape
            For Each shape In document.Shapes
                If (shape.Type = Microsoft.Office.Core.MsoShapeType.msoOLEControlObject) Then
                    Dim oleControl As Object = shape.OLEFormat.Object
                    Dim oleControlType As Type = oleControl.GetType()
                    Dim oleControlName As String = CType(oleControlType.InvokeMember("Name", _
                    Reflection.BindingFlags.GetProperty, Nothing, oleControl, Nothing), String)
                    If (String.Compare(oleControlName, name, True, System.Globalization.CultureInfo.InvariantCulture) = 0) Then
                        Return oleControl
                    End If
                End If
            Next

        Catch Ex As Exception
            ' Returns Nothing if the control is not found.
        End Try
        Return Nothing
    End Function
#End Region

    ' Called when the document is opened.
    Private Sub ThisDocument_Open() Handles ThisDocument.Open
        SetupCommandBar()
    End Sub

    ' Called when the document is closed.
    Private Sub ThisDocument_Close() Handles ThisDocument.Close

        ' Ensure that Close is not called twice.
        designModeClose = False
    End Sub
End Class
