VB读写excel出错
些问题并非每次都出现,有时运行又是正常,当报错时,重新运行又正常,程序代码如下
Private Sub Command1_Click()
Dim sn As Integer '行数变量
Dim ex As Object
Dim exwbook As Object
Dim exsheet As Object
sn = 1
Shell ("taskkill /f /im excel.exe") '将内存中的运行excel关闭
Set ex = CreateObject("Excel.Application") '创建EXCEL应用类
Set exwbook = Nothing
Set exsheet = Nothing
If Dir("H:\work\Test Program\data\PMF.xls") = "" Then '判断EXCEL是否存在,不存在就创建
Set exwbook = ex.Workbooks().Add
Else
Set exwbook = ex.Workbooks.Open("H:\work\Test Program\data\PMF.xls") '如果文件存在,打开EXCEL工作簿,此行有时报错,见报错提示2
End If
ex.Visible = False '设置EXCEL不可见
Set exsheet = exwbook.Worksheets("sheet1") '打开EXCEL工作表
Do While ex.Cells(sn, 1) <> "" '依次判断第一列的单元格是否为空,不为空光标向下移一格,直至为空时追加数据,此行有时会报错,如报错提示1
sn = sn + 1
Loop
'''''''''''''''''''''''''''''''写入数据
ex.Cells(sn, 1) = sn
ex.Cells(sn, 2) = Date
ex.Cells(sn, 3) = Time '给单元格第sn行,第3列赋值
'''''''''''''''''''''''''''''''''''''''
exwbook.RunAutoMacros (xlAutoOpen)
ex.DisplayAlerts = False
exwbook.SaveAs "H:\work\Test Program\data\PMF.xls" '一般的数据保存格式
exwbook.Saved = True
exwbook.Close
ex.Quit
MsgBox "追加数据完成,退出EXCEL"
End Sub
错误提示1:自动化错误
错误提示2:实时错误'42',远程服务器不存在或不能使用
请各位指教!