第1个回答 2020-07-14
123456789101112131415161718192021222324252627282930Sub dataToExcel()Dim conn As StringDim rs As New ADODB.RecordsetDim excel As ObjectDim workbook As ObjectDim sheet As ObjectDim i&, j& 'SQL数据库的连接字串 conn = "Provider=SQLOLEDB.1;Data source=服务器地址;initial catalog=数据库名称;User Id=数据库登录账号;Password=数据库登录账号" '打开到sql的连接,读取xxx表数据到rs rs.Open "select * from xxx", conn, adOpenKeyset, adLockReadOnly 'excel对象、工作簿、工作表 Set excel = CreateObject("Excel.Application") Set workbook = excel.workbooks().Add() Set sheet = workbook.sheets(1) excel.Visible = True '循环读取rs并写入到excel For i = 1 To rs.Fields.Count j = 1 rs.MoveFirst Do While Not rs.EOF sheet.cells(j, i) = rs.Fields(i - 1).Value rs.MoveNext j = j + 1 Loop Next rs.Close Set rs = Nothing End Sub