Dim MyApp As Excel.Application
Dim MyBook As Excel.Workbook
Dim MySheet As Excel.Worksheet
Dim cn As ADODB.ConnectionSet MyApp = CreateObject("excel.application")
MyApp.Visible = False
Set MyBook = MyApp.Workbooks.Open("f:\abc.xls", , True)
Set MySheet = MyBook.Worksheets(1)MsgBox "excel opened ,ready to copy data"Set cn = New ADODB.Connection
cn.Open "provider=microsoft.jet.oledb.4.0;data source=f:\abc.mdb;Persist Security Info=False"For i = 1 To 15560ssql$ = "insert into chengji values ('" & MySheet.Cells(i, 2) & "','" & MySheet.Cells(i, 3) & "'," & MySheet.Cells(i, 7) & "," & MySheet.Cells(i, 9) & ",'" & MySheet.Cells(i, 10) & "'," & MySheet.Cells(i, 11) & ",'" & MySheet.Cells(i, 12) & "'," & MySheet.Cells(i, 13) & "," & MySheet.Cells(i, 15) & ")" cn.Execute ssql$
Next iMsgBox "all finished"cn.Close
MyBook.Close
MyApp.Quit
请问如果是内存问题的话,那么应该如何解决呢?不会让我把xls文件分成几个小的来分别处理吧?
Dim MyBook As Excel.Workbook
Dim MySheet As Excel.Worksheet
Dim cn As ADODB.ConnectionSet MyApp = CreateObject("excel.application")
MyApp.Visible = False
Set MyBook = MyApp.Workbooks.Open("f:\abc.xls", , True)
Set MySheet = MyBook.Worksheets(1)MsgBox "excel opened ,ready to copy data"Set cn = New ADODB.Connection
cn.Open "provider=microsoft.jet.oledb.4.0;data source=f:\abc.mdb;Persist Security Info=False"For i = 1 To 15560ssql$ = "insert into chengji values ('" & MySheet.Cells(i, 2) & "','" & MySheet.Cells(i, 3) & "'," & MySheet.Cells(i, 7) & "," & MySheet.Cells(i, 9) & ",'" & MySheet.Cells(i, 10) & "'," & MySheet.Cells(i, 11) & ",'" & MySheet.Cells(i, 12) & "'," & MySheet.Cells(i, 13) & "," & MySheet.Cells(i, 15) & ")" cn.Execute ssql$
Next iMsgBox "all finished"cn.Close
MyBook.Close
MyApp.Quit
请问如果是内存问题的话,那么应该如何解决呢?不会让我把xls文件分成几个小的来分别处理吧?
你可以分为几个小的来分别处理,试试看吧dim num as integer
for num =1 to 10
DataTrans 1556*num ,(1556*(num +1))-1
nextsub DataTrans(iStartRow,iEndRow)
copy你上面的代码
for循环换成
for i=iStartRow to iEndRow
end sub
我早就把它分成小的试过了,仍然只能复制三千多条啊,真不知道是什么原因啊
郁闷ing .............
到682个记录时,现象与你的一样,分成几段也不行!后想excel也是数据库,access也是数据库,让两个库的数据复制,代码如下:
conExcel.Open "provider=Microsoft.jet.oledb.4.0;Data source=" & ComDialog1.FileName & ";Extended Properties=Excel 8.0;" '打开excel数据库
Set rsExcel = New ADODB.Recordset
rsExcel.Open " Select * FROM [" & ExcelBiao & "]", conExcel, adOpenForwardOnly, adLockReadOnly
Set rsAccess = New ADODB.Recordset
rsAccess.Open "select * from " & biaoji, conn, adOpenStatic, adLockOptimistic以下你就会,我不写了…………
rsExcel.Open " Select * FROM [" & Sheetname & "$]", conExcel, adOpenForwardOnly, adLockReadOnly
这个Seheetname是我们平时看到的excel中的左下方的工作表的名称
2.access里的导入表引导,即不方便,也易丢失数据,我试验多次,只有代码方式才能使
复制的数据准确无误!!!!!!!!!!