没有具体出错的地方,先把最有可能的地方贴出来 Public Function aspexcel(ByVal SQLStr As String) As Boolean Set cnn = New ADODB.Connection cnn.Open = "Provider =SQLOLEDB;Initial Catalog=emg;Data Source=2号;UID=sa;Pwd=123456" Set rs = New ADODB.Recordset Set cmd = New ADODB.Command rs.Open SQLStr, cnn Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open(getOutPath) Set xlSheet = xlBook.Sheets(1) For j = 0 To rs.Fields.Count 'fileds(0)开始 xlSheet.Cells(1, j + 1) = rs.Fields(j).Name Next j rs.MoveFirst For i = 1 To rs.RecordCount While Not rs.EOF For j = 1 To rs.Fields.Count xlSheet.Cells(i + 1, j) = rs.Fields(j).Value Next j rs.MoveNext Wend Next i xlSheet.Range("A2").CopyFromRecordset rs xlApp.Visible = True rs.Close cnn.ClosexlBook.SaveAs (getOutPath) xlBook.Close xlApp.Quit Set xlApp = Nothing Set xlBook = Nothing Set xlSheet = Nothing aspexcel = True Exit Function End Function
逻辑有问题rs.MoveFirst For i = 1 To rs.RecordCount While Not rs.EOF For j = 1 To rs.Fields.Count xlSheet.Cells(i + 1, j) = rs.Fields(j).Value Next j rs.MoveNext Wend Next i 中的 For i = 1 To rs.RecordCount 和 While Not rs.EOF 实际上是重复的循环 如果只是需要将程序修改为无错误信息,将rs.MoveFirst移到For i = 1 To rs.RecordCount即可
将rs.MoveFirst移到For i = 1 To rs.RecordCount的下面
楼上说的对,While Not rs.EOF和for i....重复了。但报错的原因应该是循环记数初值和终值错误,正确的写法是: For i = 0 To rs.RecordCount - 1 For j = 0 To rs.Fields.Count - 1 xlSheet.Cells(i + 1, j) = rs.Fields(j).Value Next j rs.MoveNext Next i还有,你说“没有具体出错的地方,先把最有可能的地方贴出来”。很奇怪你是怎么调试的?难道当报错时,不指向错误行吗?
你的写法不对,因为我不但有rs.Fields(j).Value,还有rs.Fields(j).name,这要在第一行显示的啊 ----------------------- For i = 1 To rs.RecordCount For j = 1 To rs.Fields.Count xlSheet.Cells(i + 1, j) = rs.Fields(j-1).Value Next j rs.MoveNext Next i
你看清我上面的回复,循环起始值和终始值不一样的,不仅仅是j和j+1。 你的情况有二种写法: 1. For i = 0 To rs.RecordCount - 1 For j = 0 To rs.Fields.Count - 1 xlSheet.Cells(i + 1, j+1) = rs.Fields(j).Value Next j rs.MoveNext Next i2. For i = 1 To rs.RecordCount For j = 1 To rs.Fields.Count xlSheet.Cells(i + 1, j) = rs.Fields(j-1).Value Next j rs.MoveNext Next i
哦,1有点问题。根据你代码推敲,应该: For i = 0 To rs.RecordCount - 1 For j = 0 To rs.Fields.Count - 1 xlSheet.Cells(i + 2, j+1) = rs.Fields(j).Value Next j rs.MoveNext Next i
Public Function aspexcel(ByVal SQLStr As String) As Boolean
Set cnn = New ADODB.Connection
cnn.Open = "Provider =SQLOLEDB;Initial Catalog=emg;Data Source=2号;UID=sa;Pwd=123456"
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
rs.Open SQLStr, cnn
Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open(getOutPath)
Set xlSheet = xlBook.Sheets(1)
For j = 0 To rs.Fields.Count 'fileds(0)开始
xlSheet.Cells(1, j + 1) = rs.Fields(j).Name
Next j rs.MoveFirst
For i = 1 To rs.RecordCount
While Not rs.EOF
For j = 1 To rs.Fields.Count
xlSheet.Cells(i + 1, j) = rs.Fields(j).Value
Next j
rs.MoveNext
Wend
Next i
xlSheet.Range("A2").CopyFromRecordset rs
xlApp.Visible = True
rs.Close
cnn.ClosexlBook.SaveAs (getOutPath)
xlBook.Close
xlApp.Quit Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
aspexcel = True
Exit Function
End Function
For i = 1 To rs.RecordCount
While Not rs.EOF
For j = 1 To rs.Fields.Count
xlSheet.Cells(i + 1, j) = rs.Fields(j).Value
Next j
rs.MoveNext
Wend
Next i
中的 For i = 1 To rs.RecordCount 和 While Not rs.EOF 实际上是重复的循环
如果只是需要将程序修改为无错误信息,将rs.MoveFirst移到For i = 1 To rs.RecordCount即可
For i = 0 To rs.RecordCount - 1
For j = 0 To rs.Fields.Count - 1
xlSheet.Cells(i + 1, j) = rs.Fields(j).Value
Next j
rs.MoveNext
Next i还有,你说“没有具体出错的地方,先把最有可能的地方贴出来”。很奇怪你是怎么调试的?难道当报错时,不指向错误行吗?
-----------------------
For i = 1 To rs.RecordCount
For j = 1 To rs.Fields.Count
xlSheet.Cells(i + 1, j) = rs.Fields(j-1).Value
Next j
rs.MoveNext
Next i
For j = 0 To rs.Fields.Count-1 'fileds(0)开始
xlSheet.Cells(1, j + 1) = rs.Fields(j).Name
Next j
你的情况有二种写法:
1.
For i = 0 To rs.RecordCount - 1
For j = 0 To rs.Fields.Count - 1
xlSheet.Cells(i + 1, j+1) = rs.Fields(j).Value
Next j
rs.MoveNext
Next i2.
For i = 1 To rs.RecordCount
For j = 1 To rs.Fields.Count
xlSheet.Cells(i + 1, j) = rs.Fields(j-1).Value
Next j
rs.MoveNext
Next i
For j = 0 To rs.Fields.Count - 1
xlSheet.Cells(i + 2, j+1) = rs.Fields(j).Value
Next j
rs.MoveNext
Next i
rs.Fields(index)中的index是由0开始到rs.RecordCount-1结束
超出这个范围,就有可能报你说的错误