更新时间:2022-06-26 09:39:24
你在这里问了很多关于你的问题整个应用程序,更好地分解和测试每个,然后将它们放在一起。
You've asked a lot of questions here about your whole app, better to break things down and test each, then put them all together.
从Excel文件开始,Excel已经运行,如果是这样,文件已经打开,由用户说。获取或创建Excel并在必要时打开文件。完成后,如果打开文件则关闭文件,如果创建了隐藏实例,则退出Excel。否则
通常***留下用户拥有的东西。
Starting with the Excel file, is Excel already running and if so the file already open say by the user. get or create Excel and open the file if necessary. When done close the file if you opened it and quit Excel if you created a hidden instance. Otherwise normally best leave things as the user had them.
Sub test()
Dim bXL As Boolean
Dim bWB As Boolean
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim sFile As String
sFile = "<path-to-file>"
On Error Resume Next
' if xl is open we'll use it or create an invisible instance
Set xl = GetObject(, "excel.application")
On Error GoTo 0 ' or to our error handler
If Not xl Is Nothing Then
bXL = True
Else
Set xl = CreateObject("excel.application")
End If
If bXL Then
' if exl was open was our file already open
On Error Resume Next
Set wb = xl.Workbooks(sFile)
On Error GoTo 0 'or to our error handler
End If
If Not wb Is Nothing Then
bWB = True
Else
Set wb = xl.Workbooks.Open(sFile)
End If
If Not wb Is Nothing Then
MsgBox wb.Name
' get the array
Else
MsgBox "oops!"
' does the file exists, is it open in a 2nd excel instance, etc
' handle the error
Exit Sub
End If
' close the wb only if we opened it
If bWB = False Then
wb.Close , False
End If
' quit xl only if we created it
If Not bXL Then
xl.Quit
End If
End Sub
如上所述Excel对象模型的合格变量(早期绑定),您需要将Word项目中的引用添加到Excel。但是它必须是任何用户可能使用的最低版本,例如2003.如果那不是你应该
通过改变声明为Excel引用的任何变量来适应'后期绑定',例如
As written, with the qualified variables to the Excel object model (early binding), you'll need to add a reference in your Word project to Excel. However it must be to the lowest version any user might potentially use, eg 2003. If that's n/a to you should adapt to 'late binding' by changing any variables declared as an Excel reference, eg
Dim wb As Excel.Workbook ' early binding with excel reference
' or
Dim wb As Object ' late binding
在继续之前测试Excel的不同组合/打开和文件未打开。为简单起见,以上并不能满足相对罕见的情况,因为有多个Excel运行实例,你抓住了一个实例,但文件在另一个实例中打开了
。
Test different combinations of Excel not/open and the file not/open before moving on. For simplicity the above doesn't not cater fro the relatively rare scenaro there are multiple instances of Excel running and you grabbed an instance but the file was open in a different instance.
如果您的文件永远不可能打开,您可以在不打开它的情况下获取数据或使用ADO启动Excel实例。如果适用,快速搜索会找到一些示例。
If your file is never likely to be open you might be able to get your data without opening it or starting an Excel instance by using ADO. If applicable a quick search should find you some examples.