Automatiz�cia Microsoft Excel z programu Visual Basic

Tento �l�nok ukazuje ako vytvori� a manipulova� s Excel pomocou automatiz�cie z jazyka Visual Basic.

Existuj� dva sp�soby na ovl�danie automatiza�n� server: Autor pomocou neskoro z�v�zn� alebo v�asn� v�zbu. S koncom v�zba, met�dy nie s� viazan� a� do spustenia a automatiza�n� server je deklarovan� ako objekt. S skor� z�v�zn�, va�u �iados� vie v �ase n�vrhu presn� typ objektu bude komunik�cie s a m��e vyhl�si� jeho objekty ako �pecifick� typ. T�to vzorka pou��va v�asn� v�zbu, ktor� sa pova�uje za lep�ie vo v��ine pr�padov preto�e poskytuje neodmyslite�n� v��� v�kon a lep�ie typ bezpe�nos�.

Vykonan� akcia skor� viaza� na automatiza�n� server, mus�te nastavi� odkaz na ktor� Kni�nica typov servera. V jazyku Visual Basic, to sa realizuje prostredn�ctvom odkazy dial�gov� okno n�js� pod projekt | Ponuky odkazy. Na tejto vzorke, budete je potrebn� prida� odkaz na kni�nice typov pre program Excel, m��ete spusti� k�d. Pozri kroky ni��ie ako prida� odkaz.

Budova automatiz�cia vzorky

1. Spustite program Visual Basic a vytvorenie nov�ho �tandardn�ho EXE projektu. Form1 sa vytvor� na z�klade predvolen�ho nastavenia.
2. Kliknite na polo�kuProject a potom kliknite na tla�idlo Odkazy. V Odkazyzobraz� sa dial�gov� okno. Prejdite nadol v zozname, k�m nen�jdete Programu Microsoft Excel objekt kni�nica, a potom vyberte polo�ku Prida� odkaz do programu Excel. Ak spr�vne Objektov� kni�nica pre va�u verziu programu Excel v zozname nezobraz�, uistite sa, �e m�te verziu programu Excel spr�vne .
   
   Pozn�mky
   • Ak chcete program Microsoft Office Excel 2007, typu Kni�nica sa zobraz� ako Objektov� kni�nica programu Microsoft Excel 12.0 v Odkazyzoznam.
   • Ak chcete program Microsoft Office Excel 2003, typu Kni�nica sa zobraz� ako Objektov� kni�nica programu Microsoft Excel 11.0 v Odkazyzoznam.
   • Ak chcete program Microsoft Excel 2002, Kni�nica typov sa zobraz� akoObjektov� kni�nica programu Microsoft Excel 10.0 v Odkazy zoznam
   • Ak chcete program Microsoft Excel 2000, Kni�nica typov sa zobraz� akoObjektov� kni�nica programu Microsoft Excel 9.0 v Odkazy zoznam.
   • Ak chcete programu Microsoft Excel 97, Kni�nica typov sa zobraz� akoObjektov� kni�nica programu Microsoft Excel 8.0 v Odkazy zoznam
3. Kliknite na polo�ku ok Zatvorte Odkazy dial�gov� okno.
4. Prida� CommandButton na Form1.
5. V okne k�d pre Form1 vlo�i� nasleduj�ce k�d:
   

      Option Explicit
      
      Private Sub Command1_Click()
         Dim oXL As Excel.Application
         Dim oWB As Excel.Workbook
         Dim oSheet As Excel.Worksheet
         Dim oRng As Excel.Range
         
   
         'On Error GoTo Err_Handler
         
      ' Start Excel and get Application object.
         Set oXL = CreateObject("Excel.Application")
         oXL.Visible = True
         
      ' Get a new workbook.
         Set oWB = oXL.Workbooks.Add
         Set oSheet = oWB.ActiveSheet
         
      ' Add table headers going cell by cell.
         oSheet.Cells(1, 1).Value = "First Name"
         oSheet.Cells(1, 2).Value = "Last Name"
         oSheet.Cells(1, 3).Value = "Full Name"
         oSheet.Cells(1, 4).Value = "Salary"
         
   
      ' Format A1:D1 as bold, vertical alignment = center.
         With oSheet.Range("A1", "D1")
            .Font.Bold = True
            .VerticalAlignment = xlVAlignCenter
         End With
         
      ' Create an array to set multiple values at once.
         Dim saNames(5, 2) As String
         saNames(0, 0) = "John"
         saNames(0, 1) = "Smith"
         saNames(1, 0) = "Tom"
         saNames(1, 1) = "Brown"
         saNames(2, 0) = "Sue"
         saNames(2, 1) = "Thomas"
         saNames(3, 0) = "Jane"
   
         saNames(3, 1) = "Jones"
         saNames(4, 0) = "Adam"
         saNames(4, 1) = "Johnson"
         
       ' Fill A2:B6 with an array of values (First and Last Names).
         oSheet.Range("A2", "B6").Value = saNames
         
       ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
         Set oRng = oSheet.Range("C2", "C6")
         oRng.Formula = "=A2 & "" "" & B2"
         
       ' Fill D2:D6 with a formula(=RAND()*100000) and apply format.
         Set oRng = oSheet.Range("D2", "D6")
         oRng.Formula = "=RAND()*100000"
         oRng.NumberFormat = "$0.00"
         
       ' AutoFit columns A:D.
         Set oRng = oSheet.Range("A1", "D1")
         oRng.EntireColumn.AutoFit
         
       ' Manipulate a variable number of columns for Quarterly Sales Data.
         Call DisplayQuarterlySales(oSheet)
         
       ' Make sure Excel is visible and give the user control
       ' of Microsoft Excel's lifetime.
         oXL.Visible = True
         oXL.UserControl = True
         
       ' Make sure you release object references.
         Set oRng = Nothing
         Set oSheet = Nothing
         Set oWB = Nothing
         Set oXL = Nothing
         
      Exit Sub
      Err_Handler:
         MsgBox Err.Description, vbCritical, "Error: " & Err.Number
      End Sub
      
      Private Sub DisplayQuarterlySales(oWS As Excel.Worksheet)
         Dim oResizeRange As Excel.Range
         Dim oChart As Excel.Chart
         Dim iNumQtrs As Integer
         Dim sMsg As String
         Dim iRet As Integer
         
       ' Determine how many quarters to display data for.
         For iNumQtrs = 4 To 2 Step -1
            sMsg = "Enter sales data for" & Str(iNumQtrs) & " quarter(s)?"
            iRet = MsgBox(sMsg, vbYesNo Or vbQuestion _
               Or vbMsgBoxSetForeground, "Quarterly Sales")
            If iRet = vbYes Then Exit For
         Next iNumQtrs
         
   
         sMsg = "Displaying data for" & Str(iNumQtrs) & " quarter(s)."
         MsgBox sMsg, vbMsgBoxSetForeground, "Quarterly Sales"
         
       ' Starting at E1, fill headers for the number of columns selected.
         Set oResizeRange = oWS.Range("E1", "E1").Resize(ColumnSize:=iNumQtrs)
   
         oResizeRange.Formula = "=""Q"" & COLUMN()-4 & CHAR(10) & ""Sales"""
         
       ' Change the Orientation and WrapText properties for the headers.
         oResizeRange.Orientation = 38
         oResizeRange.WrapText = True
         
       ' Fill the interior color of the headers.
         oResizeRange.Interior.ColorIndex = 36
         
       ' Fill the columns with a formula and apply a number format.
         Set oResizeRange = oWS.Range("E2", "E6").Resize(ColumnSize:=iNumQtrs)
         oResizeRange.Formula = "=RAND()*100"
         oResizeRange.NumberFormat = "$0.00"
         
       ' Apply borders to the Sales data and headers.
         Set oResizeRange = oWS.Range("E1", "E6").Resize(ColumnSize:=iNumQtrs)
         oResizeRange.Borders.Weight = xlThin
         
       ' Add a Totals formula for the sales data and apply a border.
         Set oResizeRange = oWS.Range("E8", "E8").Resize(ColumnSize:=iNumQtrs)
         oResizeRange.Formula = "=SUM(E2:E6)"
         With oResizeRange.Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
         End With
         
       ' Add a Chart for the selected data
         Set oResizeRange = oWS.Range("E2:E6").Resize(ColumnSize:=iNumQtrs)
         Set oChart = oWS.Parent.Charts.Add
         With oChart
            .ChartWizard oResizeRange, xl3DColumn, , xlColumns
            .SeriesCollection(1).XValues = oWS.Range("A2", "A6")
               For iRet = 1 To iNumQtrs
                  .SeriesCollection(iRet).Name = "=""Q" & Str(iRet) & """"
               Next iRet
            .Location xlLocationAsObject, oWS.Name
         End With
         
       ' Move the chart so as not to cover your data.
         With oWS.Shapes("Chart 1")
            .Top = oWS.Rows(10).Top
            .Left = oWS.Columns(2).Left
   
         End With
         
       ' Free any references.
         Set oChart = Nothing
         Set oResizeRange = Nothing
      
      End Sub
   
   						

6. Stla�en�m kl�vesu F5 na spustenie projektu.

Pre viac inform�ci� o automatiz�cia kancel�rskych, pros�m nav�t�vte Microsoft Office Development podporu lokalite: (c) Microsoft Corporation 1999, v�etky pr�va vyhraden�. Pr�spevky Richard R. Taylor, Microsoft Corporation.