代码如下:
Public Function DBRead(StationName)
Dim i, j, k As Integer
Dim ArrB() As String
Dim strtime As String
Dim pathname As String pathname = App.Path & "\name.xls" Rs.ActiveConnection = Conn.ConnectionString
Rs.Source = sqlstr
Rs.Open
Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.Workbooks.Open(pathname)
xlapp.Visible = True
Set xlSheet = xlbook.Worksheets(1)
xlSheet.Activate
If Rs.BOF Then
MsgBox "没有符合条件的记录!请重新输入条件!"
Form1.ETimeTxt.Text = "YYYY-MM-DD,HH:MM:SS"
Form1.BTimeTxt.Text = "YYYY-MM-DD,HH:MM:SS"
GoTo over
Else
Do
DataVar = DataVar & Rs("log_Date").Value & "," & Rs("log_Time").Value & "," & Rs("Hcon").Value & "," & Rs("State").Value & "," & Rs("Load").Value & "," & Rs("Allweight").Value & "," & Rs("MFiux").Value & "," & Rs("FFiux").Value & "#"
Rs.MoveNext
Loop Until Rs.EOF
End If
Rs.Close
'xlSheet.Delete
'On Error Resume Next
Worksheets.Add
xlSheet.Name = StationID
Set xlSheet = xlbook.Worksheets(StationID)
xlSheet.Activate
xlSheet.Cells(1, 1) = "子站名称"
xlSheet.Cells(1, 2) = "日期"
xlSheet.Cells(1, 3) = "时间"
xlSheet.Cells(1, 4) = "小时浓度"
xlSheet.Cells(1, 5) = "状态位"
xlSheet.Cells(1, 6) = "负载率"
xlSheet.Cells(1, 7) = "总重量"
xlSheet.Cells(1, 8) = "主流量"
xlSheet.Cells(1, 9) = "辅流量"
ArrB = Split(DataVar, "#")
j = 2
For i = 0 To UBound(ArrB)
If ArrB(i) <> "" Then
ArrS = Split(ArrB(i), ",")
ArrS(1) = Jud_Time(ArrS(1))
If Len(ArrS(1)) = 7 Then
ArrS(1) = "0" & ArrS(1)
End If
strtime = ArrS(0) & "," & ArrS(1)
If strtime >= Btime And strtime <= Etime Then
For k = 0 To UBound(ArrS)
xlSheet.Cells(j, k + 2) = ArrS(k)
Next k
xlSheet.Cells(j, 1) = StationID
End If
End If
j = j + 1
Next i
'xlSheet.Move After:=Worksheets(Worksheets.Count)
over:
xlbook.Close (True) '关闭工作簿
xlapp.Quit '结束EXCEL对象
Set xlapp = Nothing '释放xlApp对象
End Function函数第一遍运行没有问题,但是当第二次调用时,语句Worksheets.Add处就会报“对象'Worksheets' 的方法'_Global' 失败”的错误。
应该怎么解决啊~~~
Public Function DBRead(StationName)
Dim i, j, k As Integer
Dim ArrB() As String
Dim strtime As String
Dim pathname As String pathname = App.Path & "\name.xls" Rs.ActiveConnection = Conn.ConnectionString
Rs.Source = sqlstr
Rs.Open
Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.Workbooks.Open(pathname)
xlapp.Visible = True
Set xlSheet = xlbook.Worksheets(1)
xlSheet.Activate
If Rs.BOF Then
MsgBox "没有符合条件的记录!请重新输入条件!"
Form1.ETimeTxt.Text = "YYYY-MM-DD,HH:MM:SS"
Form1.BTimeTxt.Text = "YYYY-MM-DD,HH:MM:SS"
GoTo over
Else
Do
DataVar = DataVar & Rs("log_Date").Value & "," & Rs("log_Time").Value & "," & Rs("Hcon").Value & "," & Rs("State").Value & "," & Rs("Load").Value & "," & Rs("Allweight").Value & "," & Rs("MFiux").Value & "," & Rs("FFiux").Value & "#"
Rs.MoveNext
Loop Until Rs.EOF
End If
Rs.Close
'xlSheet.Delete
'On Error Resume Next
Worksheets.Add
xlSheet.Name = StationID
Set xlSheet = xlbook.Worksheets(StationID)
xlSheet.Activate
xlSheet.Cells(1, 1) = "子站名称"
xlSheet.Cells(1, 2) = "日期"
xlSheet.Cells(1, 3) = "时间"
xlSheet.Cells(1, 4) = "小时浓度"
xlSheet.Cells(1, 5) = "状态位"
xlSheet.Cells(1, 6) = "负载率"
xlSheet.Cells(1, 7) = "总重量"
xlSheet.Cells(1, 8) = "主流量"
xlSheet.Cells(1, 9) = "辅流量"
ArrB = Split(DataVar, "#")
j = 2
For i = 0 To UBound(ArrB)
If ArrB(i) <> "" Then
ArrS = Split(ArrB(i), ",")
ArrS(1) = Jud_Time(ArrS(1))
If Len(ArrS(1)) = 7 Then
ArrS(1) = "0" & ArrS(1)
End If
strtime = ArrS(0) & "," & ArrS(1)
If strtime >= Btime And strtime <= Etime Then
For k = 0 To UBound(ArrS)
xlSheet.Cells(j, k + 2) = ArrS(k)
Next k
xlSheet.Cells(j, 1) = StationID
End If
End If
j = j + 1
Next i
'xlSheet.Move After:=Worksheets(Worksheets.Count)
over:
xlbook.Close (True) '关闭工作簿
xlapp.Quit '结束EXCEL对象
Set xlapp = Nothing '释放xlApp对象
End Function函数第一遍运行没有问题,但是当第二次调用时,语句Worksheets.Add处就会报“对象'Worksheets' 的方法'_Global' 失败”的错误。
应该怎么解决啊~~~
-------------
改为:
xlapp.Worksheets.Add