Question

VB6.0如何判断excel文件是否已打开？

两个问题
Q1.VB6.0如何判断excel文件是否已打开？不要说用这个If Dir ("D:\temp\excel.bz") <> "" Then '由VB关闭EXCEL .因为根本找不到.bz的文件.
Q2.写一个存在且已打开的excel文件，代码如下，如何才能在第二次写数据时以追加方式写入，而不提示是否覆盖。
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'写文件
Private Sub Command1_Click()
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Application.Workbooks.Open("d:\test.xls")
Set xlSheet = xlBook.Worksheets(1)
For i = 1 To 10 '读取10行
For j = 1 To 10 '读取10列
xlSheet.Cells(i, j) = i * j
Next j
Next i
xlSheet.SaveAs "d:\test.xls" '按指定文件名存盘
xlApp.Quit '结束EXCEL对象
Set xlApp = Nothing '释放xlApp对象
End Sub

Answer 1

Q1
在打开工作薄之前，可以用这个方法来查看工作薄是否打
on error resume next
set wk=xlapp.workbooks("test")
if err then xlapp.workbooks.open("d\test.xls")
on error goto 0

Q2
第二个问题，可用SAVEAS方法，关键是构建一个新文件名，并且这个文件名在当前目录并不存在,下面代码仅供参考，可以变化很大
k=1
curpath="d:\"
do
if dir("test"). & K & ".xls")<>"" then
.......saveas
exit do
k=K+1
loop

Answer 2

测试 WorkBook 是否已开启
Sub IsWorkBookOpen()
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("test.xls")
If wBook Is Nothing Then
MsgBox "Workbook is not open"
Set wBook = Nothing
On Error GoTo 0
Else
MsgBox "Yes it is open"
Set wBook = Nothing
On Error GoTo 0
End If
End Sub

Answer 3

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
这三句的AS后面都加NEW关键字
不支持推荐的on error resume next
on error resume next是不负责任地一种用法
简直是掩耳盗铃，自欺欺人

追问

可有更好的方法？谢谢

追答

这方法不够好？

Answer 4

Private Sub Command1_Click()
FileName = "c:\dh.xls"
On Error GoTo err
Name "c:\dh.xls" As "c:\dh2.xls"
Name "c:\dh2.xls" As "c:\dh.xls"
'代码
Exit Sub
err:
MsgBox "文件已经打开"
End Sub

Answer 5

围观打酱油