VBnet操做Excel的代码
代码示例 Private Sub writeToExcelstrTmp1 As String, colTmp1 As Collection Dim tmp1 Dim i1 As Integer, intCol As Integer, intRow As Integer Dim xlApp As New Excel.Application Dim xlBook As New Excel.Workbook Dim xlSheet As New Excel.Worksheet Dim strName As String, strArray1 As String Dim strS1 As String Dim strD1 As String strS1 CurrentProject.Path “\template.xls“ strD1 CurrentProject.Path “\“ CStratNow, “YYYYMMDDHHMMSS“ “aaa1.xls“ For i1 0 To UBoundstrTmp1 - 1 Debug.Print strTmp1i1 “ “ CStri1 Next i1 strName CurrentProject.Path “\aaa1.xls“ FileCopy strS1, strD1 Set xlApp CreateObject“Excel.Application“ xlApp.Visible False Set xlBook xlApp.Workbooks.OpenstrName Set xlBook xlApp.Workbooks.OpenstrD1 Set xlSheet xlBook.Worksheets1 With xlSheet .Range“F6“.Value strTmp11 .Range“H6“.Value strTmp12 .Range“F7“.Value CStrDate .Range“E10“.Value strTmp19 .Range“A15“.Value “To “ strTmp18 .Range“B26“.Value strTmp14 “PACKAGES“ .Range“B27“.Value strTmp15 “KGS“ .Range“B28“.Value strTmp16 “KGS“ .Range“B29“.Value strTmp17 “M3“ End With intCol 1 intRow 21 1 1 / 3939 代码示例 For i1 1 To colTmp1.Count strArray1 colTmp1.Itemi1 With xlSheet .CellsintRow, 1.Value strArray12 .CellsintRow, 2.Value strArray15 .CellsintRow, 4.Value strArray16 .CellsintRow, 5.Value strArray11 .CellsintRow, 6.Value strArray13 .CellsintRow, 7.Value strArray14 .CellsintRow, 8.Value strArray17 .CellsintRow, 9.Value strArray19 intRow intRow 1 xlApp.ActiveSheet.RowsintRow.Insert .CellsintRow, 1.Value strArray18 intRow intRow 1 xlApp.ActiveSheet.RowsintRow.Insert End With intRow intRow 1 xlApp.ActiveSheet.RowsintRow.Insert Next i1 xlApp.Visible True xlBook.Save xlBook.Close Set xlSheet Nothing Set xlBook Nothing xlApp.Quit tmp1 ShellstrName, 1 hWndDesk GetDesktopWindow r ShellcutehWndDesk, “Open“, strName, vbNullString, 0 “ Set rs1 CurrentDb.OpenRecordsetstrSQL rs1.MoveLast Debug.Print rs1.RecordCount lngCount rs1.RecordCount intFieldLength rs1.Fields.Count Debug.Print intFieldLength Debug.Print intFieldLength strA2 SplitsplitTable“HEADCOST1“, “,“ Debug.Print UBoundstrA2 With xlSheet For i1 0 To intFieldLength - 1 Debug.Print i1 Debug.Print strA1i1 3 3 / 3939 代码示例 .RangestrA1i1.Value getZValuestrA2i1 Next i1 End With If rs1.RecordCount 0 Then rs1.MoveFirst For i1 1 To lngCount For i2 1 To rs1.Fields.Count If IsNullrs1i2 - 1 Then strValue “ “ Else strValue rs1i2 - 1.Value End If xlSheet.Cellsi1 1, i2 strValue Next i2 rs1.MoveNext Next i1 rs1.MoveFirst Else MsgBox “未读取到数据“, vbCritical, “错误“ End If xlBook.Save xlBook.Close Set xlSheet Nothing Set xlBook Nothing xlApp.Quit Set xlApp Nothing rs1.Close Set rs1 Nothing End Sub 4 4 / 3939 代码示例 Private Sub Command1_Click Set xlApp CreateObject“Excel.Application“