VB中對Excel編程操作實例

1、VB 中對 Excel 編程操作實例vb excel 編程技巧 ,實例 ,格式 ,合并單元格,表格線,頁面橫向Dim xlApp As excel.ApplicationDim xlBook As excel.WorkbookDim xlSheet As excel.WorksheetSet xlApp = CreateObject("Excel.Application")Set xlBook = xlApp.Workbooks.AddSet xlSheet = xlBook.Worksheets(1)Dim xRange As excel.RangeDim m5 As S

2、tringDim msgss As Boolean'On Error GoTo 99With xlSheetIf abcd = True Then tmp2 = "臺 ," Else tmp2 = " 個,"If dataM > 5 Then m5 = "數(shù)量 :" & dataM &tmp2 Else m5 = " ".Cells(1, 1).Value = lb1.Caption & m5 &Label15.Caption.C

3、ells(2, 1).Value = " 基本信息".Cells(6, 1).Value = " 額定性能" & Chr(10) &Text1.Text & "rpm".Cells(11, 1).Value = " 堵轉(zhuǎn) ".Columns(1).ColumnWidth = 8 ' 表格寬度.Columns(2).ColumnWidth = 18 ' 表格寬度'.Range(.Cells(1, 1), .Cells(1, 5).MergeCel

4、ls = True ' 合并.Range(.Cells(2, 1), .Cells(5, 1).MergeCells = True ' 合并單元格.Range(.Cells(6, 1), .Cells(10, 1).MergeCells = True ' 合并單元格.Range(.Cells(11, 1), .Cells(13, 1).MergeCells = True ' 合并單元格.Range(.Cells(1, 1), .Cells(13, dataM +2).Borders.LineStyle = xlContinuous ' 表格線.Range

5、(.Cells(2, 1), .Cells(13, dataM +2).HorizontalAlignment = xlCenter.Cells(1, 1).Font.Size = 12.Cells(1, 1).Font.Name = " 黑體 ".rowS(1).RowHeight = 30' 行高For i = 2 To 13.rowS(i).RowHeight = 20NextFor i = 0 To dataM.Columns(i + 3).ColumnWidth = 15 ' 表格寬度If i <> 0 Then .Ce

6、lls(2, i + 2).Font.Size = 5.Cells(2, i + 2).Value = Label1(i).CaptionIf dataM > 5 And i <> 0 And abcd = True Then.Cells(3, i + 2).Value = Trim(Left(Right(Label1(i).Caption,7), 3)Else.Cells(3, i + 2).Value = Label2(i).CaptionEnd IfIf i = 0 Or dataM < 6 Then .Cells(3, i + 2

7、).Value =Label2(i).Caption.Cells(4, i + 2).Value = Label3(i).Caption.Cells(5, i + 2).Value = Label4(i).Caption.Cells(6, i + 2).Value = Label5(i).Caption.Cells(7, i + 2).Value = Label6(i).Caption.Cells(8, i + 2).Value = Label7(i).Caption.Cells(9, i + 2).Value = Label8(i).Caption.Cells(10, i + 2).Valu

8、e = Label9(i).Caption.Cells(11, i + 2).Value = Label10(i).Caption.Cells(12, i + 2).Value = Label11(i).Caption.Cells(13, i + 2).Value = Label12(i).Caption'設(shè)置格式'.Columns(10).NumberFormatLocal = "0.00_.rowS(6).NumberFormatLocal = "0.000_ ".rowS(7).NumberFormatLocal = "0.0_ &

9、quot;.rowS(8).NumberFormatLocal = "0.0000_ ".rowS(9).NumberFormatLocal = "0_ ".rowS(10).NumberFormatLocal = "0_ ".rowS(11).NumberFormatLocal = "0.000_ ".rowS(12).NumberFormatLocal = "0.0_ ".rowS(13).NumberFormatLocal = "0_ "'.rowS(7).Hi

10、dden = True'.rowS(8).Hidden = True'.rowS(10).Hidden = True'.rowS(12).Hidden = True'.Names = lb1.Caption.PageSetup.Orientation = 2' 頁面為橫向End With 98If toexcel = 1 ThenIf abcd = True Then tmp2 = " 臺數(shù)據(jù) " Else tmp2 = " 個數(shù)據(jù)If dataM > 5 ThenxlBook.SaveAs File1.Pat

11、h & "" &Right(lb1.Caption, Len(lb1.Caption) - 5) & "(" &dataM & tmp2 & Text1.Text & "rpm).xls"msgss = Truetmp = File1.Path & "" & Right(lb1.Caption,Len(lb1.Caption) - 5) & "(&

12、quot; & dataM & tmp2 & Text1.Text & "rpm).xls"ElseIf Len(pathIs) < 5 ThenxlBook.SaveAs Left(File1.Path, Len(File1.Path) - (Len(File1.Path) - InStrRev(File1.Path, "") & Right(lb1.Caption, Len(lb1.Caption) - 5) & ".xls&quo

13、t;ElsexlBook.SaveAs File1.Path & "" & pathIs& "(" & Text1.Text & "rpm).xls"msgss = Truetmp = File1.Path & "" & pathIs & "("& Text1.Text & "rpm).xls"End IfEnd IfxlBook.Close&amp