Private Sub Form_Load() On Error GoTo Err PathName = "D:\WorkBook.xls" strcnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & PathName & ";Extended Properties='Excel 8.0;HDR=Yes'" Set cnn = New ADODB.Connection cnn.Open strcnn strsql_db = "select * from [6月$]" cnn.Execute strsql_db Set Rs_db = New ADODB.Recordset Rs_db.Open strsql_db, cnn, adOpenStatic, adLockOptimistic
For j = 1 To 13 'j=1 是从excel的第二行读起 a(i) Rs_db.Fields(0).Value 'A b(i) Rs_db.Fields(2).Value 'B c(i) Rs_db.Fields(3).Value 'C d(i) Rs_db.Fields(4).Value 'D i = i+1 Rs_db.MoveNext Next j
如果还是存为Excel,可以这样做:'引用Excel("工程"/"引用"/Microsoft Excel Object X.0 Library)Private Sub Command1_Click() Dim xlApp As New Excel.Application '定义并创建EXCEL对象 Dim xlBook As Excel.Workbook '创建工作簿 Dim xlSheet As Excel.Worksheet xlApp.DisplayAlerts = False ' xlApp.Visible = True '让Excel可见 Set xlBook = xlApp.Workbooks.Open(App.Path & "\Test.xls") '打开Excel文件,准备复制
Set xlSheet = xlBook.Sheets(1) xlSheet.Range("A1:D3").Copy '复制数据 xlBook.Close False
Set xlBook = xlApp.Workbooks.Add '添加一个新的工作簿 xlBook.ActiveSheet.Paste '粘贴数据 xlBook.SaveAs "c:\result.xls" '另存 xlApp.Quit '关闭Exel Set xlBook = Nothing Set xlApp = Nothing End Sub
For j = 1 To 13 'j=1 是从excel的第二行读起,不包括标题 a(i) =Rs_db.Fields(0).Value 'A b(i)= Rs_db.Fields(2).Value 'B c(i) =Rs_db.Fields(3).Value 'C d(i)= Rs_db.Fields(4).Value 'D i = i+1 Rs_db.MoveNext Next j
On Error GoTo Err
PathName = "D:\WorkBook.xls" strcnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & PathName & ";Extended Properties='Excel 8.0;HDR=Yes'"
Set cnn = New ADODB.Connection
cnn.Open strcnn
strsql_db = "select * from [6月$]"
cnn.Execute strsql_db
Set Rs_db = New ADODB.Recordset
Rs_db.Open strsql_db, cnn, adOpenStatic, adLockOptimistic
For j = 1 To 13 'j=1 是从excel的第二行读起
a(i) Rs_db.Fields(0).Value 'A
b(i) Rs_db.Fields(2).Value 'B
c(i) Rs_db.Fields(3).Value 'C
d(i) Rs_db.Fields(4).Value 'D
i = i+1
Rs_db.MoveNext
Next j
Dim xlApp As New Excel.Application '定义并创建EXCEL对象 Dim xlBook As Excel.Workbook '创建工作簿
Dim xlSheet As Excel.Worksheet
xlApp.DisplayAlerts = False
' xlApp.Visible = True '让Excel可见 Set xlBook = xlApp.Workbooks.Open(App.Path & "\Test.xls") '打开Excel文件,准备复制
Set xlSheet = xlBook.Sheets(1)
xlSheet.Range("A1:D3").Copy '复制数据
xlBook.Close False
Set xlBook = xlApp.Workbooks.Add '添加一个新的工作簿
xlBook.ActiveSheet.Paste '粘贴数据
xlBook.SaveAs "c:\result.xls" '另存 xlApp.Quit '关闭Exel
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
a(i) =Rs_db.Fields(0).Value 'A
b(i)= Rs_db.Fields(2).Value 'B
c(i) =Rs_db.Fields(3).Value 'C
d(i)= Rs_db.Fields(4).Value 'D
i = i+1
Rs_db.MoveNext
Next j