本文将告诉你如何使用VB代码连接Office应用程序,并简要接触一下在文件中输入数据的方法。实际上,在VB中用代码与Word和Excel进行会话并控制它们,是可行的。但是请注意,首先需要在机器上安装office应用程序,才能在VB代码中存取它们的对象。 下面就是一些例子,告诉你如何与这些程序会话,并控制它们。 Option Explicit Dim xlsApp As Excel.Application Dim wrdApp As Word.Application 只要相关的对象库已经被选择,在应用程序中进行对象变量的赋值是可能的。Microsoft Excel 8.0对象库是相对于Excel的,而 Microsoft Word 8.0 对象库是为Word服务的。 在VB的IDE环境中,从“工程”菜单中选择“引用”,可以看到系统可用的所有库列表。Private Sub Command1_Click() Set xlsApp = Excel.Application With xlsApp 'Show Excel .Visible = True 'Create a new workbook .Workbooks.Add 'Put text in to the cell that is selected .ActiveCell.Value = "Hi" 'Put text into A3 regardless of the selected cell .Range("A3").Value = "This is an example of connecting to Excel" End With End Sub 在上面的程序段中,我们在变量xlsApp中建立了一个对象,这样Excel就对用户可见了。当Excel象这样启动后,并不包含一个工作簿,所以必须创建或者执行打开操作。这里,我们建立了一个新的工作簿,然后,就可以操作其中的信息,或者打印,或者保存,或者你任意想做的事情。Private Sub Command2_Click() 'close the workbook xlsApp.Workbooks.Close 'Close Excel xlsApp.Quit End Sub 上面这段代码执行关闭程序的功能。首先,关闭工作簿,这将出现一个提示对话框,询问用户是否想保存修改;然后,退出应用程序。Private Sub Command3_Click() Set wrdApp = New Word.Application With wrdApp 'Show Word .Visible = True 'Create New Document .Documents.Add 'Add text to the document .ActiveDocument.Content.Text = "Hi" .ActiveDocument.Content.Text = "This is a test example" End With End Sub 上面这段代码中,在变量wrdApp中设置引用Word程序的对象。同样,当Word按照这种方式启动后,不会包含一个文档,所以,必须执行建立或者打开操作。这里是建立了一个新文档,然后可以操作其中的信息了,打印、保存、发送邮件,等等... 但是,在Word文档中放置文本并非容易!特别是与Excel一起工作时。为了简单地在特定的地方放置文本,需要有一个book标记。这意味着,需要事先建立一个模板。Private Sub Command4_Click() 'Close the current document wrdApp.ActiveDocument.Close 'Close Word wrdApp.Quit End Sub 上面这段代码的功能是关闭应用程序。首先,关闭当前文档,这时可能需要用户保存修改。然后,退出程序。Private Sub Form_Unload(Cancel As Integer) 'Clear the memory Set xlsApp = Nothing Set wrdApp = Nothing End Sub 最后一段代码就是关闭VB应用程序。这是优秀程序员编程的好习惯。 Well I hope this brief tutorial is helpful. It does not touch on much of what you can do to the office applications once they're open, but should give you an idea of how to get started. 好了,简单的介绍到此结束。我希望能抛砖引玉,让你更加随意地操作Office应用程序!
Dim ExlApp As Excel.Application Dim xlsWorkBook As Excel.Workbook Dim xlsSheet As Excel.Worksheet Set xlsWorkBook = ExlApp.Workbooks.Open("e:\a.xls")
Dim ExlApp As Excel.Application Dim xlsWorkBook As Excel.Workbook Dim xlsSheet As Excel.Worksheet Set xlsWorkBook = ExlApp.Workbooks.Open("e:\a.xls")
Private Sub opt_all_Click()On Error GoTo error sql = "select* from Y_YSDWBH " Set rs = ExecuteSQL(sql) Set MSHFlexGrid1.DataSource = rs With MSHFlexGrid1 .TextMatrix(0, 0) = "单位编号" .TextMatrix(0, 1) = "单位名称" .TextMatrix(0, 2) = "单位简称" .TextMatrix(0, 3) = "单位类别" .TextMatrix(0, 4) = "单位级数" .TextMatrix(0, 5) = "单位明细" .TextMatrix(0, 6) = "备注" End With Dim i, j As Integer Dim Exl As Variant Set Exl = CreateObject("Excel.Application") Exl.Workbooks.Add '由EXCEL里面的宏得来的 Range("A1:A65536").Select Selection.NumberFormatLocal = "@" '将网格中的数据导入到EXCEL表中 For i = 0 To MSHFlexGrid1.Rows - 1 For j = 0 To MSHFlexGrid1.Cols - 1 Exl.Cells(1 + i, j + 1) = MSHFlexGrid1.TextMatrix(i, j) Next j Next i DeleteFile ("c:\Documents and Settings\Administrator\My Documents\Resume.xlw") DeleteFile (" c:\My Documents \Resume.xlw") Exl.SaveWorkspace '退出Excel Application Exl.Quit MsgBox "数据已导出至Excel文件中,同时请注意导出的数据禁止对其操作! ", vbInformation, "提示"error: opt_all.Value = False opt_exit.Value = False Exit Sub Me.ShowEnd Sub我是将数据库中的数据先导入表格中了然后在导出EXCEL 不知道楼主要的是不是类似的问题
本文将告诉你如何使用VB代码连接Office应用程序,并简要接触一下在文件中输入数据的方法。实际上,在VB中用代码与Word和Excel进行会话并控制它们,是可行的。但是请注意,首先需要在机器上安装office应用程序,才能在VB代码中存取它们的对象。 下面就是一些例子,告诉你如何与这些程序会话,并控制它们。 Option Explicit Dim xlsApp As Excel.Application Dim wrdApp As Word.Application 只要相关的对象库已经被选择,在应用程序中进行对象变量的赋值是可能的。Microsoft Excel 8.0对象库是相对于Excel的,而 Microsoft Word 8.0 对象库是为Word服务的。 在VB的IDE环境中,从“工程”菜单中选择“引用”,可以看到系统可用的所有库列表。Private Sub Command1_Click() Set xlsApp = Excel.Application With xlsApp 'Show Excel .Visible = True 'Create a new workbook .Workbooks.Add 'Put text in to the cell that is selected .ActiveCell.Value = "Hi" 'Put text into A3 regardless of the selected cell .Range("A3").Value = "This is an example of connecting to Excel" End With End Sub 在上面的程序段中,我们在变量xlsApp中建立了一个对象,这样Excel就对用户可见了。当Excel象这样启动后,并不包含一个工作簿,所以必须创建或者执行打开操作。这里,我们建立了一个新的工作簿,然后,就可以操作其中的信息,或者打印,或者保存,或者你任意想做的事情。Private Sub Command2_Click() 'close the workbook xlsApp.Workbooks.Close 'Close Excel xlsApp.Quit End Sub 上面这段代码执行关闭程序的功能。首先,关闭工作簿,这将出现一个提示对话框,询问用户是否想保存修改;然后,退出应用程序。Private Sub Command3_Click() Set wrdApp = New Word.Application With wrdApp 'Show Word .Visible = True 'Create New Document .Documents.Add 'Add text to the document .ActiveDocument.Content.Text = "Hi" .ActiveDocument.Content.Text = "This is a test example" End With End Sub 上面这段代码中,在变量wrdApp中设置引用Word程序的对象。同样,当Word按照这种方式启动后,不会包含一个文档,所以,必须执行建立或者打开操作。这里是建立了一个新文档,然后可以操作其中的信息了,打印、保存、发送邮件,等等... 但是,在Word文档中放置文本并非容易!特别是与Excel一起工作时。为了简单地在特定的地方放置文本,需要有一个book标记。这意味着,需要事先建立一个模板。Private Sub Command4_Click() 'Close the current document wrdApp.ActiveDocument.Close 'Close Word wrdApp.Quit End Sub 上面这段代码的功能是关闭应用程序。首先,关闭当前文档,这时可能需要用户保存修改。然后,退出程序。Private Sub Form_Unload(Cancel As Integer) 'Clear the memory Set xlsApp = Nothing Set wrdApp = Nothing End Sub 最后一段代码就是关闭VB应用程序。这是优秀程序员编程的好习惯。 Well I hope this brief tutorial is helpful. It does not touch on much of what you can do to the office applications once they're open, but should give you an idea of how to get started. 好了,简单的介绍到此结束。我希望能抛砖引玉,让你更加随意地操作Office应用程序!
'存字段长度值 Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) With Rs_Dzgl_Receipt If .RecordCount < 1 Then MsgBox ("没有记录!") Exit Sub End If xlSheet.Cells(1, 4).Value = .Fields("bt") xlSheet.Cells(2, 1).Value = .Fields("invoice") xlSheet.Cells(2, 9).Value = .Fields("packdate") xlSheet.Cells(3, 1).Value = .Fields("")
'合并单元格 Dim nIcol As Integer
xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(5, 9)).Select With xlApp.Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = True End With
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, 9)).Select With xlApp.Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = True End With '网格线 With xlSheet .Range(.Cells(1, 1), .Cells(1, 9)).Font.Name = "黑体" '设标题为黑体字 .Range(.Cells(1, 1), .Cells(1, 9)).Font.Bold = True '标题字体加粗 .Range(.Cells(1, 1), .Cells(1, 9)).Borders.LineStyle = xlContinuous '设表格边框样式 End With
'显示表格 Dim ExclFileName As String ExclFileName = App.Path & "\箱单" & Text1(1).Text & ".xls" If Dir(ExclFileName) <> "" Then Kill ExclFileName End If xlSheet.SaveAs (ExclFileName) xlApp.Application.Visible = True '交还控制给Excel xlSheet.PrintPreview ' xlApp.Application.Quit ' xlApp.Quit End With
一定要: 引用 Microsoft Excel X.0 Object library
to lihonggen0(李洪根,用.NET,标准答案来了) :请问一下,我的问题是这样的,我的程序调用word,在有的机器上可以运行,有的机器不能,提示的错误是“加载DLL错误”,重新引用了Microsoft Word 9.0 Object library还是不行。
Dim xlsApp As Excel.Application
Dim wrdApp As Word.Application 只要相关的对象库已经被选择,在应用程序中进行对象变量的赋值是可能的。Microsoft Excel 8.0对象库是相对于Excel的,而 Microsoft Word 8.0 对象库是为Word服务的。 在VB的IDE环境中,从“工程”菜单中选择“引用”,可以看到系统可用的所有库列表。Private Sub Command1_Click()
Set xlsApp = Excel.Application
With xlsApp
'Show Excel
.Visible = True
'Create a new workbook
.Workbooks.Add
'Put text in to the cell that is selected
.ActiveCell.Value = "Hi"
'Put text into A3 regardless of the selected cell
.Range("A3").Value = "This is an example of connecting to Excel"
End With
End Sub
在上面的程序段中,我们在变量xlsApp中建立了一个对象,这样Excel就对用户可见了。当Excel象这样启动后,并不包含一个工作簿,所以必须创建或者执行打开操作。这里,我们建立了一个新的工作簿,然后,就可以操作其中的信息,或者打印,或者保存,或者你任意想做的事情。Private Sub Command2_Click()
'close the workbook
xlsApp.Workbooks.Close
'Close Excel
xlsApp.Quit
End Sub 上面这段代码执行关闭程序的功能。首先,关闭工作簿,这将出现一个提示对话框,询问用户是否想保存修改;然后,退出应用程序。Private Sub Command3_Click()
Set wrdApp = New Word.Application
With wrdApp
'Show Word
.Visible = True
'Create New Document
.Documents.Add
'Add text to the document
.ActiveDocument.Content.Text = "Hi"
.ActiveDocument.Content.Text = "This is a test example"
End With
End Sub 上面这段代码中,在变量wrdApp中设置引用Word程序的对象。同样,当Word按照这种方式启动后,不会包含一个文档,所以,必须执行建立或者打开操作。这里是建立了一个新文档,然后可以操作其中的信息了,打印、保存、发送邮件,等等... 但是,在Word文档中放置文本并非容易!特别是与Excel一起工作时。为了简单地在特定的地方放置文本,需要有一个book标记。这意味着,需要事先建立一个模板。Private Sub Command4_Click()
'Close the current document
wrdApp.ActiveDocument.Close
'Close Word
wrdApp.Quit
End Sub 上面这段代码的功能是关闭应用程序。首先,关闭当前文档,这时可能需要用户保存修改。然后,退出程序。Private Sub Form_Unload(Cancel As Integer)
'Clear the memory
Set xlsApp = Nothing
Set wrdApp = Nothing
End Sub 最后一段代码就是关闭VB应用程序。这是优秀程序员编程的好习惯。 Well I hope this brief tutorial is helpful. It does not touch on much of what you can do to the office applications once they're open, but should give you an idea of how to get started. 好了,简单的介绍到此结束。我希望能抛砖引玉,让你更加随意地操作Office应用程序!
Dim xlsWorkBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet Set xlsWorkBook = ExlApp.Workbooks.Open("e:\a.xls")
Dim xlsWorkBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet Set xlsWorkBook = ExlApp.Workbooks.Open("e:\a.xls")
sql = "select* from Y_YSDWBH "
Set rs = ExecuteSQL(sql)
Set MSHFlexGrid1.DataSource = rs
With MSHFlexGrid1
.TextMatrix(0, 0) = "单位编号"
.TextMatrix(0, 1) = "单位名称"
.TextMatrix(0, 2) = "单位简称"
.TextMatrix(0, 3) = "单位类别"
.TextMatrix(0, 4) = "单位级数"
.TextMatrix(0, 5) = "单位明细"
.TextMatrix(0, 6) = "备注"
End With
Dim i, j As Integer
Dim Exl As Variant
Set Exl = CreateObject("Excel.Application")
Exl.Workbooks.Add
'由EXCEL里面的宏得来的
Range("A1:A65536").Select
Selection.NumberFormatLocal = "@"
'将网格中的数据导入到EXCEL表中
For i = 0 To MSHFlexGrid1.Rows - 1
For j = 0 To MSHFlexGrid1.Cols - 1
Exl.Cells(1 + i, j + 1) = MSHFlexGrid1.TextMatrix(i, j)
Next j
Next i
DeleteFile ("c:\Documents and Settings\Administrator\My Documents\Resume.xlw")
DeleteFile (" c:\My Documents \Resume.xlw")
Exl.SaveWorkspace
'退出Excel Application
Exl.Quit
MsgBox "数据已导出至Excel文件中,同时请注意导出的数据禁止对其操作! ", vbInformation, "提示"error:
opt_all.Value = False
opt_exit.Value = False
Exit Sub
Me.ShowEnd Sub我是将数据库中的数据先导入表格中了然后在导出EXCEL
不知道楼主要的是不是类似的问题
Dim xlsApp As Excel.Application
Dim wrdApp As Word.Application 只要相关的对象库已经被选择,在应用程序中进行对象变量的赋值是可能的。Microsoft Excel 8.0对象库是相对于Excel的,而 Microsoft Word 8.0 对象库是为Word服务的。 在VB的IDE环境中,从“工程”菜单中选择“引用”,可以看到系统可用的所有库列表。Private Sub Command1_Click()
Set xlsApp = Excel.Application
With xlsApp
'Show Excel
.Visible = True
'Create a new workbook
.Workbooks.Add
'Put text in to the cell that is selected
.ActiveCell.Value = "Hi"
'Put text into A3 regardless of the selected cell
.Range("A3").Value = "This is an example of connecting to Excel"
End With
End Sub
在上面的程序段中,我们在变量xlsApp中建立了一个对象,这样Excel就对用户可见了。当Excel象这样启动后,并不包含一个工作簿,所以必须创建或者执行打开操作。这里,我们建立了一个新的工作簿,然后,就可以操作其中的信息,或者打印,或者保存,或者你任意想做的事情。Private Sub Command2_Click()
'close the workbook
xlsApp.Workbooks.Close
'Close Excel
xlsApp.Quit
End Sub 上面这段代码执行关闭程序的功能。首先,关闭工作簿,这将出现一个提示对话框,询问用户是否想保存修改;然后,退出应用程序。Private Sub Command3_Click()
Set wrdApp = New Word.Application
With wrdApp
'Show Word
.Visible = True
'Create New Document
.Documents.Add
'Add text to the document
.ActiveDocument.Content.Text = "Hi"
.ActiveDocument.Content.Text = "This is a test example"
End With
End Sub 上面这段代码中,在变量wrdApp中设置引用Word程序的对象。同样,当Word按照这种方式启动后,不会包含一个文档,所以,必须执行建立或者打开操作。这里是建立了一个新文档,然后可以操作其中的信息了,打印、保存、发送邮件,等等... 但是,在Word文档中放置文本并非容易!特别是与Excel一起工作时。为了简单地在特定的地方放置文本,需要有一个book标记。这意味着,需要事先建立一个模板。Private Sub Command4_Click()
'Close the current document
wrdApp.ActiveDocument.Close
'Close Word
wrdApp.Quit
End Sub 上面这段代码的功能是关闭应用程序。首先,关闭当前文档,这时可能需要用户保存修改。然后,退出程序。Private Sub Form_Unload(Cancel As Integer)
'Clear the memory
Set xlsApp = Nothing
Set wrdApp = Nothing
End Sub 最后一段代码就是关闭VB应用程序。这是优秀程序员编程的好习惯。 Well I hope this brief tutorial is helpful. It does not touch on much of what you can do to the office applications once they're open, but should give you an idea of how to get started. 好了,简单的介绍到此结束。我希望能抛砖引玉,让你更加随意地操作Office应用程序!
'存字段长度值
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1) With Rs_Dzgl_Receipt
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Sub
End If
xlSheet.Cells(1, 4).Value = .Fields("bt")
xlSheet.Cells(2, 1).Value = .Fields("invoice")
xlSheet.Cells(2, 9).Value = .Fields("packdate")
xlSheet.Cells(3, 1).Value = .Fields("")
'合并单元格
Dim nIcol As Integer
xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(5, 9)).Select
With xlApp.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, 9)).Select
With xlApp.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
'网格线
With xlSheet
.Range(.Cells(1, 1), .Cells(1, 9)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, 9)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(1, 9)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
'显示表格
Dim ExclFileName As String
ExclFileName = App.Path & "\箱单" & Text1(1).Text & ".xls"
If Dir(ExclFileName) <> "" Then
Kill ExclFileName
End If
xlSheet.SaveAs (ExclFileName)
xlApp.Application.Visible = True
'交还控制给Excel
xlSheet.PrintPreview
' xlApp.Application.Quit
' xlApp.Quit
End With
引用 Microsoft Excel X.0 Object library
但是要完整的调用,包括格式,建议你用F1Book:)