最好是你自己錄制一個宏,然后去研究,這樣很有幫助. 以下有個例子.注意要先引用excelPublic Sub To_Excel(ByVal RecEXCEL As ADODB.Recordset) Dim o_Excel As Excel.Application Dim xlsSheet As Excel.Worksheet Dim i, k As Long Dim i_Count As Long Dim i_Sum, i_Use As Long Screen.MousePointer = 11 Set o_Excel = CreateObject("excel.application") o_Excel.Workbooks.Add Set xlsSheet = o_Excel.Worksheets.Add If Option1.Value Then o_Excel.Range("D1") = "職工宿舍安排表" Else o_Excel.Range("D1") = "職工外宿登記表" End If With RecEXCEL '表頭 For k = 0 To .Fields.Count - 1 xlsSheet.Cells(2, k + 1) = .Fields(k).Name Next k
o_Excel.Range("A3").CopyFromRecordset RecEXCEL End With
Screen.MousePointer = 0 o_Excel.Application.Visible = True Set o_Excel = Nothing End Sub
创建Excel,把数据存入Excel Private Sub ComExport_Click() Dim xlApp As New Excel.Application Dim xlBook As New Excel.Workbook '定義Excel工作簿對象 Dim xlSheet As New Excel.Worksheet '定義Excel工作表對象
Set xlBook = xlApp.Workbooks.add On Error Resume Next Set xlSheet = xlBook.Worksheets(1) If k = 2 Then 'by 機台編號 str_eqid = "" n = 0 M = 1 '得到的str_eqid 用與excel For M = 0 To ListSbbh.ListCount - 1 If ListSbbh.Selected(M) = True Then str_eqid = str_eqid & Trim(ListSbbh.List(M)) If n < ListSbbh.SelCount Then str_eqid = str_eqid End If n = n + 1 End If Next M xlSheet.Cells(1, 4) = "EQ Down Top10 Report" xlSheet.Cells(2, 1) = "Date:" xlSheet.Cells(2, 2) = Format(DTPickerStart.Value, "yyyy-mm-dd") & " 07:30:00" xlSheet.Cells(2, 3) = "TO" xlSheet.Cells(2, 4) = Format(DTPickerEnd.Value + 1, "yyyy-mm-dd") & " 07:30:00" xlSheet.Cells(3, 1) = "Eqid:" xlSheet.Cells(3, 2) = str_eqid
line = 4 Do While Not rsgzxx.EOF xlSheet.Cells(4, line).Value = rsgzxx("poenomenon").Value xlSheet.Cells(5, line).Value = rsgzxx("quantity").Value
line = line + 1 rsgzxx.MoveNext Loop End If xlBook.SaveAs FileName:=savepath, FileFormat:=xlNormal, _ PassWord:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False xlBook.Saved = True '保存到Excel MsgBox "保存成功!", vbOKOnly, "信息" '結束EXcel進程 xlApp.Quit ' Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = Nothing
errhandler:
Exit Sub
End Sub
呵呵,发错了 好像有个vba的书吧
去WORD或者EXCEL中录制宏看看就一目了然了,比什么VBA的书都全!!!
方法1:(使用部件) 你可以在VB中使用Microsoft Internet Controls部件,调用代码如下: WebBrowser1.Navigate "c:\test.doc" '("c:\test.doc"为文件在硬盘上的路径,你用这种方法调用Word文档、Excel表格、各种图片都行!) '如下代码可以加载相应的工具栏dim tt as object '(该代码是加载所有工具栏,你也可以选择一些) For Each tt In WebBrowser1.Document.commandbars On Error Resume Next tt.Visible = True On Error GoTo 0 Next方法2:(使用API)Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPrivate Sub Command1_Click() ShellExecute 0, "open", "C:\test.doc", vbNullString, vbNullString, 0 '("C:\test.doc"中可以是硬盘上任意文件) End Sub'顺便说一句,如果看了感觉能帮上你的忙,请给些分,谢了!
'-------- 一种思路,仅供参靠 --------------
If MsgBox("确定要操作注册号为:“" + zcdm + "”的原始记录?", vbYesNo + vbQuestion, "提示") = vbYes Then
strsql = "select 注册号 from " + this_table + " where 注册号 = '" + zcdm + "'" ss = exec_sql("select 原始录入标志 from 计划跟踪 where 计划编号 = '" + jhbh + "'") If ss = "0" Then ss = exec_sql(strsql) If ss = "" Then strsql = "insert into dbo." + this_table + " (注册号) VALUES('" + zcdm + "')" cnn.Execute strsql End If End If
If dg_jhgz.TextMatrix(dg_jhgz.RowSel, 2) = "0" Then ed_flag = 0 Else ed_flag = 1 End If Me.Caption = "原始记录录入—编辑注册号为 " + zcdm + " 的原始记录。" this_str = App.path + "\temp.doc" mb_str = App.path + "\mb_code.doc" get_mb = Read_ysjl_mb(mb_str, "Z录入模块代码模板.doc") If get_mb = False Then Exit Sub get_mb = Read_ysjl_mb(this_str, this_table + ".doc") If get_mb = False Then Exit Sub If inti_ysjl_doc = -1 Then Set odoc = appwd.Documents.Open(this_str, , False, False, "yunyan") Set mydoc = appwd.Documents.Open(mb_str, , False, False, "yunyan") Else Set odoc = appwd.Documents.Open(this_str, , False, False, "yunyan") End If copy_ymyj odoc, mydoc mydoc.Activate odoc.Close False Set odoc = Nothing this_str = Trim(cnn.ConnectionString) appwd.Run "doc_inti", operator, this_str, this_table, zcdm, ed_flag, False, "", 0, jhbh, jy_bm On Error Resume Next Fm_jw_getrw_over.Show 1 If appwd.Visible Then appwd.Run "cmdback" Me.Caption = "原始记录录入" dg_jhgz_inti jhbh, 0 End If Sub doc_inti(oper As String, cn_str As String, tablename As String, zcm As String, Optional ed_flag As Integer = 0, Optional flag As Boolean = False, Optional copy_zcm As String = "", Optional sh_jc As Integer = 0, Optional jhbh As String = "", Optional bm As String = "")
Set adoPrimaryRS = New ADODB.Recordset adoPrimaryRS.Open doc_sql, cnn, adOpenKeyset, adLockOptimistic If adoPrimaryRS.RecordCount = 0 Then adoPrimaryRS.AddNew End If data_inti zcm CommandBars("Forms").Visible = False
End Sub'============原始记录控制菜单代码================ Private Sub add_menubutton() Dim mycontrol As CommandBarButton Dim ss As String ' If shenhe_jc = 3 Then Exit Sub On Error Resume Next ss = CommandBars.Item("原始记录控制菜单").Name If ss = "原始记录控制菜单" Then Set move_mbar = CommandBars.Item("原始记录控制菜单") Exit Sub End If Set move_mbar = CommandBars.Add("原始记录控制菜单", msoBarTop, , False) 'msoBarBottom msoBarFloating With move_mbar .RowIndex = msoBarRowLast .Visible = True End With Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=1) With mycontrol .FaceId = 1 .Caption = "结果分析" .ToolTipText = "原始记录结果分析" .Style = msoButtonCaption .OnAction = "cmdjgfx_click" End With Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=2) With mycontrol .FaceId = 2 .Caption = "编辑" .ToolTipText = "编辑原始记录" .Style = msoButtonCaption .OnAction = "cmdedit_click" End With Set mycontrol = move_mbar.Controls _ .Add(Type:=msoControlButton, ID:=3) With mycontrol .FaceId = 3 .Caption = "保存" .ToolTipText = "保存原始记录" .Style = msoButtonCaption .OnAction = "cmdupdate_click" End With Set mycontrol = move_mbar.Controls _ .Add(Type:=msoControlButton, ID:=4) With mycontrol .FaceId = 4 .Caption = "取消" .ToolTipText = "取消操作" .Style = msoButtonCaption .OnAction = "cmdcancel_click" End With Set mycontrol = move_mbar.Controls _ .Add(Type:=msoControlButton, ID:=5) With mycontrol .FaceId = 5 .Caption = "复制" .ToolTipText = "复制操作,仅限于同一受检单位设备。" .Style = msoButtonCaption .OnAction = "cmdcopy_click" End With mbar_flag = False Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=6) With mycontrol .FaceId = 6 .Caption = "整改意见" .ToolTipText = "整改意见录入与查阅" .Style = msoButtonCaption .OnAction = "cmd_zgyj_luru_click" End With Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=7) With mycontrol .FaceId = 7 .Caption = "数据录入选择" + "[×]" .ToolTipText = "数据录入选择菜单,[√]表示点击操作有效,[×]则表示点击操作无效" .Style = msoButtonCaption .OnAction = "movemenu_click" End With Set mycontrol = move_mbar.Controls _ .Add(Type:=msoControlButton, ID:=11) With mycontrol .FaceId = 11 .Caption = "刷新CAD图片" .ToolTipText = "刷新CAD容器示意图" .Style = msoButtonCaption .OnAction = "cmd_rqtp_refresh" .Enabled = False End With Set mycontrol = move_mbar.Controls _ .Add(Type:=msoControlButton, ID:=12) With mycontrol .FaceId = 12 .Caption = "关闭" .ToolTipText = "关闭 Microsoft Word 窗口" .Style = msoButtonCaption .OnAction = "cmdclose" End With End SubPrivate Sub cmdcopy_click() Ufm_muli_select.Show 1 End SubPrivate Sub cmdback() ActiveDocument.Application.Visible = False ActiveDocument.Unprotect Selection.WholeStory Selection.TypeBackspace ' del_menubutton End SubSub del_menubutton() On Error Resume Next ActiveDocument.Application.CommandBars("原始记录控制菜单").Delete End Sub
以下有個例子.注意要先引用excelPublic Sub To_Excel(ByVal RecEXCEL As ADODB.Recordset)
Dim o_Excel As Excel.Application
Dim xlsSheet As Excel.Worksheet
Dim i, k As Long
Dim i_Count As Long
Dim i_Sum, i_Use As Long Screen.MousePointer = 11
Set o_Excel = CreateObject("excel.application")
o_Excel.Workbooks.Add
Set xlsSheet = o_Excel.Worksheets.Add If Option1.Value Then
o_Excel.Range("D1") = "職工宿舍安排表"
Else
o_Excel.Range("D1") = "職工外宿登記表"
End If
With RecEXCEL '表頭 For k = 0 To .Fields.Count - 1
xlsSheet.Cells(2, k + 1) = .Fields(k).Name
Next k
o_Excel.Range("A3").CopyFromRecordset RecEXCEL End With
Screen.MousePointer = 0
o_Excel.Application.Visible = True
Set o_Excel = Nothing
End Sub
Private Sub ComExport_Click()
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook '定義Excel工作簿對象
Dim xlSheet As New Excel.Worksheet '定義Excel工作表對象
Dim line As Integer, M As Integer, n As Integer
Dim savepath As String '定義保存路徑
CommonDialog1.CancelError = True '設置cancelError為ture
On Error GoTo errhandler
CommonDialog1.Flags = cdlOFNHideReadOnly
CommonDialog1.FileName = "Report"
CommonDialog1.DefaultExt = ".xls"
CommonDialog1.Filter = "Excel(*.xls)|*.xls|Text(*.txt)|*.txt"
CommonDialog1.FilterIndex = 1
CommonDialog1.Flags = &H2
CommonDialog1.ShowSave
If ERR.Number = cdlCancel Then
Exit Sub
End If
savepath = CommonDialog1.FileName
''######################以下是匯入到excel
Set xlApp = CreateObject("Excel.Application")
' xlApp.Visible = True '根据操作人是否需見到Excel此處可設TRUE 或FALSE
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.add
On Error Resume Next
Set xlSheet = xlBook.Worksheets(1)
If k = 2 Then 'by 機台編號
str_eqid = ""
n = 0
M = 1 '得到的str_eqid 用與excel
For M = 0 To ListSbbh.ListCount - 1
If ListSbbh.Selected(M) = True Then
str_eqid = str_eqid & Trim(ListSbbh.List(M))
If n < ListSbbh.SelCount Then
str_eqid = str_eqid
End If
n = n + 1
End If
Next M
xlSheet.Cells(1, 4) = "EQ Down Top10 Report"
xlSheet.Cells(2, 1) = "Date:"
xlSheet.Cells(2, 2) = Format(DTPickerStart.Value, "yyyy-mm-dd") & " 07:30:00"
xlSheet.Cells(2, 3) = "TO"
xlSheet.Cells(2, 4) = Format(DTPickerEnd.Value + 1, "yyyy-mm-dd") & " 07:30:00"
xlSheet.Cells(3, 1) = "Eqid:"
xlSheet.Cells(3, 2) = str_eqid
xlSheet.Cells(4, 1) = "Bug Poenomenon"
xlSheet.Cells(5, 1) = "Quantity"
rsgzxx.MoveFirst
line = 4
Do While Not rsgzxx.EOF
xlSheet.Cells(4, line).Value = rsgzxx("poenomenon").Value
xlSheet.Cells(5, line).Value = rsgzxx("quantity").Value
line = line + 1
rsgzxx.MoveNext
Loop
End If xlBook.SaveAs FileName:=savepath, FileFormat:=xlNormal, _
PassWord:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
xlBook.Saved = True '保存到Excel
MsgBox "保存成功!", vbOKOnly, "信息"
'結束EXcel進程
xlApp.Quit '
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
errhandler:
Exit Sub
End Sub
好像有个vba的书吧
你可以在VB中使用Microsoft Internet Controls部件,调用代码如下:
WebBrowser1.Navigate "c:\test.doc" '("c:\test.doc"为文件在硬盘上的路径,你用这种方法调用Word文档、Excel表格、各种图片都行!)
'如下代码可以加载相应的工具栏dim tt as object '(该代码是加载所有工具栏,你也可以选择一些)
For Each tt In WebBrowser1.Document.commandbars
On Error Resume Next
tt.Visible = True
On Error GoTo 0
Next方法2:(使用API)Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPrivate Sub Command1_Click()
ShellExecute 0, "open", "C:\test.doc", vbNullString, vbNullString, 0 '("C:\test.doc"中可以是硬盘上任意文件)
End Sub'顺便说一句,如果看了感觉能帮上你的忙,请给些分,谢了!
'-------- 一种思路,仅供参靠 --------------
If MsgBox("确定要操作注册号为:“" + zcdm + "”的原始记录?", vbYesNo + vbQuestion, "提示") = vbYes Then
strsql = "select 注册号 from " + this_table + " where 注册号 = '" + zcdm + "'"
ss = exec_sql("select 原始录入标志 from 计划跟踪 where 计划编号 = '" + jhbh + "'")
If ss = "0" Then
ss = exec_sql(strsql)
If ss = "" Then
strsql = "insert into dbo." + this_table + " (注册号) VALUES('" + zcdm + "')"
cnn.Execute strsql
End If
End If
If dg_jhgz.TextMatrix(dg_jhgz.RowSel, 2) = "0" Then
ed_flag = 0
Else
ed_flag = 1
End If
Me.Caption = "原始记录录入—编辑注册号为 " + zcdm + " 的原始记录。"
this_str = App.path + "\temp.doc"
mb_str = App.path + "\mb_code.doc"
get_mb = Read_ysjl_mb(mb_str, "Z录入模块代码模板.doc")
If get_mb = False Then Exit Sub
get_mb = Read_ysjl_mb(this_str, this_table + ".doc")
If get_mb = False Then Exit Sub
If inti_ysjl_doc = -1 Then
Set odoc = appwd.Documents.Open(this_str, , False, False, "yunyan")
Set mydoc = appwd.Documents.Open(mb_str, , False, False, "yunyan")
Else
Set odoc = appwd.Documents.Open(this_str, , False, False, "yunyan")
End If
copy_ymyj odoc, mydoc
mydoc.Activate
odoc.Close False
Set odoc = Nothing
this_str = Trim(cnn.ConnectionString)
appwd.Run "doc_inti", operator, this_str, this_table, zcdm, ed_flag, False, "", 0, jhbh, jy_bm
On Error Resume Next
Fm_jw_getrw_over.Show 1
If appwd.Visible Then appwd.Run "cmdback"
Me.Caption = "原始记录录入"
dg_jhgz_inti jhbh, 0
End If
Sub doc_inti(oper As String, cn_str As String, tablename As String, zcm As String, Optional ed_flag As Integer = 0, Optional flag As Boolean = False, Optional copy_zcm As String = "", Optional sh_jc As Integer = 0, Optional jhbh As String = "", Optional bm As String = "")
Set adoPrimaryRS = New ADODB.Recordset
adoPrimaryRS.Open doc_sql, cnn, adOpenKeyset, adLockOptimistic
If adoPrimaryRS.RecordCount = 0 Then
adoPrimaryRS.AddNew
End If
data_inti zcm
CommandBars("Forms").Visible = False
End Sub'============原始记录控制菜单代码================
Private Sub add_menubutton()
Dim mycontrol As CommandBarButton
Dim ss As String
' If shenhe_jc = 3 Then Exit Sub
On Error Resume Next
ss = CommandBars.Item("原始记录控制菜单").Name
If ss = "原始记录控制菜单" Then
Set move_mbar = CommandBars.Item("原始记录控制菜单")
Exit Sub
End If
Set move_mbar = CommandBars.Add("原始记录控制菜单", msoBarTop, , False) 'msoBarBottom msoBarFloating
With move_mbar
.RowIndex = msoBarRowLast
.Visible = True
End With
Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=1)
With mycontrol
.FaceId = 1
.Caption = "结果分析"
.ToolTipText = "原始记录结果分析"
.Style = msoButtonCaption
.OnAction = "cmdjgfx_click"
End With
Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=2)
With mycontrol
.FaceId = 2
.Caption = "编辑"
.ToolTipText = "编辑原始记录"
.Style = msoButtonCaption
.OnAction = "cmdedit_click"
End With
Set mycontrol = move_mbar.Controls _
.Add(Type:=msoControlButton, ID:=3)
With mycontrol
.FaceId = 3
.Caption = "保存"
.ToolTipText = "保存原始记录"
.Style = msoButtonCaption
.OnAction = "cmdupdate_click"
End With
Set mycontrol = move_mbar.Controls _
.Add(Type:=msoControlButton, ID:=4)
With mycontrol
.FaceId = 4
.Caption = "取消"
.ToolTipText = "取消操作"
.Style = msoButtonCaption
.OnAction = "cmdcancel_click"
End With
Set mycontrol = move_mbar.Controls _
.Add(Type:=msoControlButton, ID:=5)
With mycontrol
.FaceId = 5
.Caption = "复制"
.ToolTipText = "复制操作,仅限于同一受检单位设备。"
.Style = msoButtonCaption
.OnAction = "cmdcopy_click"
End With
mbar_flag = False
Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=6)
With mycontrol
.FaceId = 6
.Caption = "整改意见"
.ToolTipText = "整改意见录入与查阅"
.Style = msoButtonCaption
.OnAction = "cmd_zgyj_luru_click"
End With
Set mycontrol = move_mbar.Controls.Add(Type:=msoControlButton, ID:=7)
With mycontrol
.FaceId = 7
.Caption = "数据录入选择" + "[×]"
.ToolTipText = "数据录入选择菜单,[√]表示点击操作有效,[×]则表示点击操作无效"
.Style = msoButtonCaption
.OnAction = "movemenu_click"
End With
Set mycontrol = move_mbar.Controls _
.Add(Type:=msoControlButton, ID:=11)
With mycontrol
.FaceId = 11
.Caption = "刷新CAD图片"
.ToolTipText = "刷新CAD容器示意图"
.Style = msoButtonCaption
.OnAction = "cmd_rqtp_refresh"
.Enabled = False
End With
Set mycontrol = move_mbar.Controls _
.Add(Type:=msoControlButton, ID:=12)
With mycontrol
.FaceId = 12
.Caption = "关闭"
.ToolTipText = "关闭 Microsoft Word 窗口"
.Style = msoButtonCaption
.OnAction = "cmdclose"
End With
End SubPrivate Sub cmdcopy_click()
Ufm_muli_select.Show 1
End SubPrivate Sub cmdback()
ActiveDocument.Application.Visible = False
ActiveDocument.Unprotect
Selection.WholeStory
Selection.TypeBackspace
' del_menubutton
End SubSub del_menubutton()
On Error Resume Next
ActiveDocument.Application.CommandBars("原始记录控制菜单").Delete
End Sub
去看一下 关于excel的
我贴在那了
以EXCEL为例:
Dim AppLi As New Excel.Application
Dim WokBok As New Excel.Workbook
Dim WokSet As New Excel.Worksheet
Set AppLi = CreateObject("Excel.Application")
Set WokBok = AppLi.Workbooks.Open(ExCelPath) 'ExCelPath 为以存在的EXCEL
Set WokSet = WokBok.Worksheets(1)For j = 0 To MeDtGri.Columns.Count - 1
WokSet.Cells(1, j + 1) = j
Next jAppLi.Visible = True
'release objext
Set AppLi = Nothing
Set WokBok = Nothing
Set WokSet = Nothing