Private Sub Cmdexl_Click()Dim XlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim i
Set XlApp = CreateObject("Excel.Application") Set XlBook = XlApp.Workbooks.Add
Set XlSheet = XlBook.Worksheets(1) With XlSheet
.Name = "採購資料查詢明細"
.Range(.Cells(1, 1), .Cells(1, 10)).MergeCells = True
.Cells(1, 1) = "採購資料查詢明細"
.Range(.Cells(1, 1), .Cells(1, 10)).Font.Size = 20
.Range(.Cells(1, 1), .Cells(1, 10)).Font.Bold = True
.Range(.Cells(1, 1), .Cells(1, 10)).Font.Name = "標楷體"
.Range(.Cells(1, 1), .Cells(1, 10)).HorizontalAlignment = xlCenter
.Cells(2, 1) = "部門代碼"
.Cells(2, 2) = "部門名稱"
.Cells(2, 3) = "請購日期"
.Cells(2, 4) = "請購人"
.Cells(2, 5) = "請購單號"
.Cells(2, 6) = "項目"
.Cells(2, 7) = "請購數量"
.Cells(2, 8) = "已驗收數量"
.Cells(2, 9) = "廠商名稱"
.Cells(2, 10) = "規格說明"
.Range(.Cells(2, 1), .Cells(2, 10)).Font.Size = 12 '格式排列
.Range(.Cells(2, 1), .Cells(2, 10)).Font.Name = "ROMAN NEW TIME"
.Range(.Cells(2, 1), .Cells(2, 10)).Font.FontStyle = "粗體"
.Range(.Cells(2, 1), .Cells(2, 10)).HorizontalAlignment = xlCenter
If RSPOlist1.RecordCount > 0 Then
RSPOlist1.MoveFirst
i = 2 Do While Not RSPOlist1.EOF
.Cells(i + 1, 1) = Trim(ClearNULL(RSPOlist1("DEPTCODE")))
.Cells(i + 1, 2) = Trim(ClearNULL(RSPOlist1("CNAME")))
.Cells(i + 1, 3) = Trim(ClearNULL(RSPOlist1("Initialdate")))
.Cells(i + 1, 4) = Trim(ClearNULL(RSPOlist1("Empname")))
.Cells(i + 1, 5) = Trim(ClearNULL(RSPOlist1("Mro2sn")))
.Cells(i + 1, 6) = Trim(ClearNULL(RSPOlist1("Spec")))
.Cells(i + 1, 7) = Trim(ClearNULL(RSPOlist1("Initialcount")))
.Cells(i + 1, 8) = Trim(ClearNULL(RSPOlist1("Realcount")))
.Cells(i + 1, 9) = Trim(ClearNULL(RSPOlist1("Vendorname")))
.Cells(i + 1, 10) = Trim(ClearNULL(RSPOlist1("Detail"))) RSPOlist1.MoveNext
i = i + 1
Loop
XlApp.Columns.AutoFit
XlApp.Visible = True
End IfEnd With'
'Set RSPOlist1 = Nothing
Exit SubERR:
MsgBox ERR.Number & ":" & ERR.Description
End Sub
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim i
Set XlApp = CreateObject("Excel.Application") Set XlBook = XlApp.Workbooks.Add
Set XlSheet = XlBook.Worksheets(1) With XlSheet
.Name = "採購資料查詢明細"
.Range(.Cells(1, 1), .Cells(1, 10)).MergeCells = True
.Cells(1, 1) = "採購資料查詢明細"
.Range(.Cells(1, 1), .Cells(1, 10)).Font.Size = 20
.Range(.Cells(1, 1), .Cells(1, 10)).Font.Bold = True
.Range(.Cells(1, 1), .Cells(1, 10)).Font.Name = "標楷體"
.Range(.Cells(1, 1), .Cells(1, 10)).HorizontalAlignment = xlCenter
.Cells(2, 1) = "部門代碼"
.Cells(2, 2) = "部門名稱"
.Cells(2, 3) = "請購日期"
.Cells(2, 4) = "請購人"
.Cells(2, 5) = "請購單號"
.Cells(2, 6) = "項目"
.Cells(2, 7) = "請購數量"
.Cells(2, 8) = "已驗收數量"
.Cells(2, 9) = "廠商名稱"
.Cells(2, 10) = "規格說明"
.Range(.Cells(2, 1), .Cells(2, 10)).Font.Size = 12 '格式排列
.Range(.Cells(2, 1), .Cells(2, 10)).Font.Name = "ROMAN NEW TIME"
.Range(.Cells(2, 1), .Cells(2, 10)).Font.FontStyle = "粗體"
.Range(.Cells(2, 1), .Cells(2, 10)).HorizontalAlignment = xlCenter
If RSPOlist1.RecordCount > 0 Then
RSPOlist1.MoveFirst
i = 2 Do While Not RSPOlist1.EOF
.Cells(i + 1, 1) = Trim(ClearNULL(RSPOlist1("DEPTCODE")))
.Cells(i + 1, 2) = Trim(ClearNULL(RSPOlist1("CNAME")))
.Cells(i + 1, 3) = Trim(ClearNULL(RSPOlist1("Initialdate")))
.Cells(i + 1, 4) = Trim(ClearNULL(RSPOlist1("Empname")))
.Cells(i + 1, 5) = Trim(ClearNULL(RSPOlist1("Mro2sn")))
.Cells(i + 1, 6) = Trim(ClearNULL(RSPOlist1("Spec")))
.Cells(i + 1, 7) = Trim(ClearNULL(RSPOlist1("Initialcount")))
.Cells(i + 1, 8) = Trim(ClearNULL(RSPOlist1("Realcount")))
.Cells(i + 1, 9) = Trim(ClearNULL(RSPOlist1("Vendorname")))
.Cells(i + 1, 10) = Trim(ClearNULL(RSPOlist1("Detail"))) RSPOlist1.MoveNext
i = i + 1
Loop
XlApp.Columns.AutoFit
XlApp.Visible = True
End IfEnd With'
'Set RSPOlist1 = Nothing
Exit SubERR:
MsgBox ERR.Number & ":" & ERR.Description
End Sub
你EXCEL中打开录制宏,再用shu标选中其一个单元格,结束录制,
再编制这个宏,就得这个源代号了,
我就是这样学用VB操作EXCEL的
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim i
Set XlApp = CreateObject("Excel.Application") Set XlBook = XlApp.Workbooks.Add
Set XlSheet = XlBook.Worksheets(1) 'read cell A1 value to i
i=XlSheet.Range("A1").Value
XlBook.Close
XlApp.Quit
Set XlSheet= Nothing
Set XlBook = Nothing
Set XlApp = NothingExit SubERR:
MsgBox ERR.Number & ":" & ERR.Description
End Sub