clientdocuments.vb

这是一个VBA开发的项目实例

        Me.chkbCopyPaper.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.chkbCopyPaper.Location = New System.Drawing.Point(151, 72)
        Me.chkbCopyPaper.Name = "chkbCopyPaper"
        Me.chkbCopyPaper.Size = New System.Drawing.Size(97, 16)
        Me.chkbCopyPaper.TabIndex = 67
        Me.chkbCopyPaper.Text = "Copy Paper"
        '
        'chkbCompPaper
        '
        Me.chkbCompPaper.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.chkbCompPaper.Location = New System.Drawing.Point(16, 72)
        Me.chkbCompPaper.Name = "chkbCompPaper"
        Me.chkbCompPaper.Size = New System.Drawing.Size(128, 16)
        Me.chkbCompPaper.TabIndex = 66
        Me.chkbCompPaper.Text = "Computer Paper"
        '
        'Label2
        '
        Me.Label2.Font = New System.Drawing.Font("Tahoma", 8.25!, (System.Drawing.FontStyle.Bold Or System.Drawing.FontStyle.Underline), System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.Location = New System.Drawing.Point(16, 48)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(240, 23)
        Me.Label2.TabIndex = 65
        Me.Label2.Text = "Products to be Included in the Proposal:"
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(224, 16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(128, 23)
        Me.Label1.TabIndex = 63
        Me.Label1.Text = "Proposal Expiry Date:"
        '
        'ExpiryDate
        '
        Me.ExpiryDate.BackColor = System.Drawing.Color.LightCyan
        Me.ExpiryDate.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.ExpiryDate.Location = New System.Drawing.Point(362, 16)
        Me.ExpiryDate.Name = "ExpiryDate"
        Me.ExpiryDate.Size = New System.Drawing.Size(105, 21)
        Me.ExpiryDate.TabIndex = 64
        Me.ExpiryDate.Text = ""
        '
        'lblClient_Name
        '
        Me.lblClient_Name.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblClient_Name.Location = New System.Drawing.Point(16, 16)
        Me.lblClient_Name.Name = "lblClient_Name"
        Me.lblClient_Name.Size = New System.Drawing.Size(90, 23)
        Me.lblClient_Name.TabIndex = 61
        Me.lblClient_Name.Text = "Proposal Date:"
        '
        'ProposalDate
        '
        Me.ProposalDate.BackColor = System.Drawing.Color.LightCyan
        Me.ProposalDate.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.ProposalDate.Location = New System.Drawing.Point(108, 16)
        Me.ProposalDate.Name = "ProposalDate"
        Me.ProposalDate.Size = New System.Drawing.Size(105, 21)
        Me.ProposalDate.TabIndex = 62
        Me.ProposalDate.Text = ""
        '
        'ClientDocuments
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.BackColor = System.Drawing.Color.PowderBlue
        Me.ClientSize = New System.Drawing.Size(565, 448)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.TabControl1, Me.Button1, Me.btnUpdate, Me.grpDocumentOption, Me.lblLabelFormDes})
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None
        Me.Name = "ClientDocuments"
        Me.Text = "ClientDocuments"
        Me.grpDocumentOption.ResumeLayout(False)
        Me.TabControl1.ResumeLayout(False)
        Me.TabPage1.ResumeLayout(False)
        Me.Panel1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim NewMDIParent As New MainMDI()
        NewMDIParent.Show()
        NewMDIParent.Hide()
        Me.Close()
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If radbAnalysis.Checked = True Then
            Call AnalysisReport()
        End If
        If radbProposal.Checked = True Then
            Call ProposalCreation()
        End If
        If radbPresentation.Checked = True Then
            Call PresentationCreation()
        End If
    End Sub
    Private Sub AnalysisReport()
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim varIndustryType As String
        Dim varTotalValue As Decimal

        Try
            xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
            xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
            xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

            ' Place some text in the second row of the sheet.
            xlSheet.Cells(1, 1) = "This is Competitor Analysis Report for Customer: " & varCompanyName.SharedValue & "  Customer ID: " & varCompanyName.SharedClientID


            clientConnection.Open()
            strSqlStatement = "SELECT * FROM tblProductUsageAndCompAnalysis WHERE Client_ID = " & varCompanyName.SharedClientID
            clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
            clientDataReader = clientCommand.ExecuteReader()
            While clientDataReader.Read

                varIndustryType = clientDataReader.Item(1)
                xlSheet.Cells(2, 1) = "Customer's Industry Type is: " & varIndustryType
                xlSheet.Cells(4, 1) = "Products"
                xlSheet.Cells(4, 2) = "Order Value(in Dollars)"
                xlSheet.Cells(4, 3) = "Current Provider"
                xlSheet.Cells(4, 4) = "% of Total Order Value"
                xlSheet.Cells(6, 1) = "Computer Paper"
                xlSheet.Cells(6, 2) = IIf(IsDBNull(clientDataReader.Item(4)), 0, clientDataReader.Item(4))
                xlSheet.Cells(6, 3) = IIf(IsDBNull(clientDataReader.Item(5)), "NA", clientDataReader.Item(5))
                xlSheet.Cells(7, 1) = "Copy Paper"
                xlSheet.Cells(7, 2) = IIf(IsDBNull(clientDataReader.Item(8)), 0, clientDataReader.Item(8))
                xlSheet.Cells(7, 3) = IIf(IsDBNull(clientDataReader.Item(9)), "NA", clientDataReader.Item(9))
                xlSheet.Cells(8, 1) = "Gel Ink Pen"
                xlSheet.Cells(8, 2) = IIf(IsDBNull(clientDataReader.Item(12)), 0, clientDataReader.Item(12))
                xlSheet.Cells(8, 3) = IIf(IsDBNull(clientDataReader.Item(13)), "NA", clientDataReader.Item(13))
                xlSheet.Cells(9, 1) = "Highlighter"
                xlSheet.Cells(9, 2) = IIf(IsDBNull(clientDataReader.Item(16)), 0, clientDataReader.Item(16))
                xlSheet.Cells(9, 3) = IIf(IsDBNull(clientDataReader.Item(17)), "NA", clientDataReader.Item(17))
                xlSheet.Cells(10, 1) = "Scotch Tape"
                xlSheet.Cells(10, 2) = IIf(IsDBNull(clientDataReader.Item(20)), 0, clientDataReader.Item(20))
                xlSheet.Cells(10, 3) = IIf(IsDBNull(clientDataReader.Item(21)), 0, clientDataReader.Item(21))
                xlSheet.Cells(11, 1) = "Glue Stic"
                xlSheet.Cells(11, 2) = IIf(IsDBNull(clientDataReader.Item(24)), 0, clientDataReader.Item(24))
                xlSheet.Cells(11, 3) = IIf(IsDBNull(clientDataReader.Item(25)), "NA", clientDataReader.Item(25))

            End While
            clientDataReader.Close()
            clientConnection.Close()
            xlSheet.Cells(13, 1) = "Total Order Value is: "
            xlSheet.Range("A1:D13").Select()
            xlApp.Selection.Font.Bold = True
            xlSheet.Columns("A:A").ColumnWidth = 20
            xlSheet.Columns("B:B").ColumnWidth = 25
            xlSheet.Columns("C:C").ColumnWidth = 25
            xlSheet.Range("B6:B13").Select()
            xlSheet.Range("B13").Activate()
            xlApp.ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
            xlSheet.Range("B13").Select()
            xlApp.Selection.Font.Bold = True

            xlSheet.Range("D6").Select()
            xlApp.ActiveCell.FormulaR1C1 = "=(RC[-2]/R[7]C[-2])*100"
            xlSheet.Range("D7").Select()
            xlApp.ActiveCell.FormulaR1C1 = "=(RC[-2]/R[6]C[-2])*100"
            xlSheet.Range("D8").Select()
            xlApp.ActiveCell.FormulaR1C1 = "=(RC[-2]/R[5]C[-2])*100"
            xlSheet.Range("D9").Select()
            xlApp.ActiveCell.FormulaR1C1 = "=(RC[-2]/R[4]C[-2])*100"
            xlSheet.Range("D10").Select()
            xlApp.ActiveCell.FormulaR1C1 = "=(RC[-2]/R[3]C[-2])*100"
            xlSheet.Range("D11").Select()
            xlApp.ActiveCell.FormulaR1C1 = "=(RC[-2]/R[2]C[-2])*100"
            xlSheet.Range("D6:D11").Select()
            xlApp.Selection.NumberFormat = "0.00"

            xlSheet.Range("D6:D13").Select()
            xlSheet.Range("D13").Activate()
            xlApp.ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"


            'for center align
            xlSheet.Range("B6:B13").Select()
            With xlApp.Selection
                .HorizontalAlignment = -4108
            End With
            xlSheet.Range("H1:H1").Select()
            ' Show the sheet."

            xlSheet.Application.Visible = True
            xlSheet = Nothing
            xlBook = Nothing
            xlApp = Nothing
        Catch
            MsgBox(Err.Description)
        End Try

    End Sub
    Private Sub ProposalCreation()

        Dim tempDoc As Word.Document
        Dim mainDoc As Word.Document
        Dim newDocName As String
        Dim varProducts As String
        Dim varIssues As String
        Dim varSolutions As String

        Try
            docApp = CType(CreateObject("Word.Application"), Word.Application)
            'xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
            'docApp = New Word.Application()
            docApp.Visible = True
            docApp.Application.Documents.Open("c:\easyselling\ProposalTemplate.doc")
            tempDoc = CType(docApp.ActiveDocument, Word.Document)
            docApp.Selection.WholeStory()
            docApp.Selection.Copy()
            mainDoc = CType(docApp.Documents.Add, Word.Document)