请各位看下面的代码:
Private Sub Command1_Click()
Dim i As Integer
Dim j As Integer
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
SetxlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
On Error Resume Next
Set xlBook = xlApp.Workbooks.Open("d:\text2.xls")
Set xlSheet = xlBook.Worksheets(1)
For j = 0 To DataGrid1.Columns.Count - 1
xlSheet.Cells(1, j + 1) = DataGrid1.Columns.Item(j).Caption
Next j
xlSheet.Cells(6, 1) = "i"
Adodc1.Recordset.MoveFirst
For i = 0 To Adodc1.Recordset.RecordCount - 1
DataGrid1.Row = i
For j = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = j
'MsgBox DataGrid1.TextIf IsNull(DataGrid1.Text) = False Then
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
End If
Next j
Next i
End SubPrivate Sub Form_Load()
Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Password=material2000;Persist Security Info=True;User ID=materialadmin;Initial Catalog=material;Data Source=10.63.208.71"
Adodc1.RecordSource = "select * from material where materialcode like '0352%'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End Sub运行以后,可以导出电子表格,但是发现有跳过记录的现象
就是recordset并非按顺序走,莫名其妙的跳过几个,然后用最后一条记录补齐剩下的记录数,非常奇怪,现在已经被搞糊涂了!还请各位高手指点一下!
Private Sub Command1_Click()
Dim i As Integer
Dim j As Integer
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
SetxlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
On Error Resume Next
Set xlBook = xlApp.Workbooks.Open("d:\text2.xls")
Set xlSheet = xlBook.Worksheets(1)
For j = 0 To DataGrid1.Columns.Count - 1
xlSheet.Cells(1, j + 1) = DataGrid1.Columns.Item(j).Caption
Next j
xlSheet.Cells(6, 1) = "i"
Adodc1.Recordset.MoveFirst
For i = 0 To Adodc1.Recordset.RecordCount - 1
DataGrid1.Row = i
For j = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = j
'MsgBox DataGrid1.TextIf IsNull(DataGrid1.Text) = False Then
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
End If
Next j
Next i
End SubPrivate Sub Form_Load()
Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Password=material2000;Persist Security Info=True;User ID=materialadmin;Initial Catalog=material;Data Source=10.63.208.71"
Adodc1.RecordSource = "select * from material where materialcode like '0352%'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End Sub运行以后,可以导出电子表格,但是发现有跳过记录的现象
就是recordset并非按顺序走,莫名其妙的跳过几个,然后用最后一条记录补齐剩下的记录数,非常奇怪,现在已经被搞糊涂了!还请各位高手指点一下!
Public Conn As New ADODB.Connection
Public strConn As StringPrivate Sub Command1_Click()
ExporToExcel strConn
End SubPrivate Sub Form_Load() strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"
Conn.CursorLocation = adUseClient
Conn.Open strConn
If Rs.State <> adStateClosed Then Rs.Close
Rs.Open "Select * from jobs", Conn, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = Rs
End SubPublic Function ExporToExcel(strOpen As String)
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable With Rs_Data
If Rs_Data.State <> adStateClosed Then Rs_Data.Close
.Open "Select * from jobs", Conn, adOpenStatic, adLockOptimistic
End With
With Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If Irowcount = .RecordCount Icolcount = .Fields.Count
End With Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1")) With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With xlQuery.FieldNames = True
xlQuery.Refresh xlApp.Application.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = NothingEnd Function
我来个简单点的
Dim myexcel As New Excel.Application
Dim mybook As New Excel.Workbook
Dim mysheet As New Excel.WorksheetSet mybook = myexcel.Workbooks.Add '添加一个新的BOOK
Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET
Dim rows As Integer
rows = 1
mysheet.Cells(rows, 1).Value = "数据项"
mysheet.Cells(rows, 2).Value = "数据项"
... ...
rows = rows + 1
mysheet.Cells(rows, 1).CopyFromRecordset Adodc1.Recordset
myexcel.Visible = True
'使用应用程序对象的 Quit 方法关闭 Excel。
myexcel.Quit
'释放该对象变量
Set myexcel = Nothing
Set mybook = Nothing
Set mysheet = Nothing
这句话出错:Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
无效的过程或参数调用!
Dim newbook As Excel.Workbook
Dim newsheet As Excel.Worksheet
Set newapp = CreateObject("excel.application")
Set newbook = newapp.Workbooks.Add
Set newsheet = newbook.ActiveSheetnewapp.Visible = True
m = Adodc1.Recordset.Fields.Count
n = Adodc1.Recordset.RecordCount
'填写标题
For i = 1 To m
newsheet.Cells(1, i) = Adodc1.Recordset.Fields(i - 1).Name
Next
Adodc1.Recordset.MoveFirst
'填写内容
If n <> 0 Then
For i = 1 To n
For j = 1 To mnewsheet.Cells(i + 1, j) = Adodc1.Recordset(j - 1)Next
Adodc1.Recordset.MoveNext
Next
End If