excel - 由于行数限制为 65,000，Microsoft Access 导出到 excel 文件时出错

Question

将我的 Microsoft 访问数据导出到 Excel 文件时遇到问题。

它给了我一个错误信息：

您选择的记录多于一次可以复制到剪贴板上的记录。将记录分成两组或更多组，然后一次复制并粘贴一组。一次可以粘贴的最大记录数约为 65,000。

我正在使用的代码：

FileCopy "S:\Users\File\Deposit.xls", strs & "\Deposit.xls"

标签： excelvbams-access

Answer 1

尝试这个：

Select External data
Got to Export
Hit Excel
You will be given a choice to select the destination for your data
Select where you want to save your exported data
In the Specify Report Options Area- you have the option to select and tick Export Data With Formatting And Layout
You also have the option to open the destination file after the export operation is complete- tick if you want to utilise this option
Hit OK
You may now get the above error at this stage if you have ticked the Export Data With Formatting And Layout Option and are trying to export more than 65,000 data lines
When the data is exported you can then hit Close

使用 VBA：

Sub transSpread()
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
           "tblSales", "C:\Sales.xls"
     MsgBox "Sales spreadsheet created"
End Sub

或者：

Option Compare Database
Option Explicit

' be sure to select Microsoft Excel Object Library in the References dialog box

Public myExcel As Excel.Application

Sub CopyToExcel()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim wbk As Excel.Workbook
   Dim myWorksheet As Excel.Worksheet
   Dim StartRange As Excel.Range
   Dim strConn As String
   Dim i As Integer
   Dim f As Variant

   On Error GoTo ErrorHandler

   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"

   Set conn = New ADODB.Connection

   Set myRecordset = New ADODB.Recordset
   With myRecordset
      .Open "Employees", strConn, _
          adOpenKeyset, adLockOptimistic
   End With

   Set myExcel = New Excel.Application
   Set wbk = myExcel.Workbooks.Add
   Set myWorksheet = wbk.ActiveSheet
   myExcel.Visible = True

   i = 1
   With myRecordset
      For Each f In .Fields
         With myWorksheet
            .Cells(1, i).Value = f.Name
            i = i + 1
         End With
      Next
   End With

   Set StartRange = myWorksheet.Cells(2, 1)
   StartRange.CopyFromrecordset myRecordset

   myRecordset.Close
   Set myRecordset = Nothing

   myWorksheet.Columns.AutoFit

   wbk.Close SaveChanges:=True, _
       FileName:="C:\ExcelFile.xls"

   myExcel.Quit
   Set conn = Nothing
   Exit Sub

ErrorHandler:
   MsgBox Err.Description, vbCritical, _
      "Automation Error"
   Set myExcel = Nothing
   Exit Sub
End Sub