将MS Access表拆分为多个部分，然后使用VBA导出到Excel中 [英] Split MS Access table into parts and export into Excel using VBA

问题描述

我有一个大约有50000条记录的Access表，我需要将其分成3部分，然后使用VBA将这些部分导出到单独的excel文件或工作表中.

I have an Access table of about 50000 records which I require to split into preferably 3 parts and export these parts into individual excel files or sheets using VBA.

我需要这样做，因为这些Excel文件在其他地方使用，其中文件中的最大记录数只能是大约20000条记录.

I require this, as these Excel files are used elsewhere where the maximum number of records in a file can only be about 20000 records.

我玩过docmd.transferspreadsheet方法，但似乎无法拆分它们.

I have played around with the docmd.transferspreadsheet method but can't seem to split them.

任何想法或帮助表示赞赏.

Any ideas or help appreciated.

我正在使用的这张表包含以下几列:零件编号(由各种不同的字符组成)，描述，价格，注释.它没有与记录相关的ID号，例如1到50000.

This table that I am working with consists of columns: Part Number (made of various characters which is unique), description, price,comments. It does not have a ID number say from 1 till 50000 each relating to a record.

推荐答案

尝试一下:

Sub ExportChunks()
Dim rs As Recordset
Dim ssql As String
Dim maxnum As Long
Dim numChunks As Integer

Dim qdef As QueryDef

ssql = "SELECT COUNT(Id) FROM BigTable"
Set rs = CurrentDb.OpenRecordset(ssql)

maxnum = rs.Fields(0).Value  'total number of records

'add 0.5 so you always round up:
numChunks = Round((maxnum / 20000) + 0.5, 0)

On Error Resume Next 'don't break if Chunk_1 not yet in QueryDefs

ssql = "SELECT TOP 20000 * FROM BigTable"
CurrentDb.QueryDefs.Delete "Chunk"
Set qdef = New QueryDef
qdef.SQL = ssql
qdef.Name = "Chunk"
CurrentDb.QueryDefs.Append qdef
CurrentDb.QueryDefs.Refresh
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Chunk_1", "C:\00_Projekte_temp\Chunk_1.xlsx"

For i = 2 To numChunks
    ssql = "SELECT TOP 20000 * FROM BigTable WHERE ID NOT IN (SELECT TOP " & (i - 1) * 20000 & " ID FROM BigTable)"
    Set qdef = CurrentDb.QueryDefs("Chunk")
    qdef.SQL = ssql
    CurrentDb.QueryDefs.Refresh
    Set qdef = CurrentDb.QueryDefs("Chunk_" & i)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdef.Name, "C:\00_Projekte_temp\" & qdef.Name & ".xlsx"
Next i

End Sub

它是做什么的?首先，它计算您需要多少块，然后创建查询，记录前20,000个记录，然后记录下一个，依此类推，最后将每个查询块导出到Excel文件.

What does it do? First it calculates how many chunks you'll need, then creates queries that will take the first 20,000 records, then the next and so forth, and lastly exports each chunked query to an Excel file.

编辑:已修改为onyl创建一个查询，该查询在每次迭代中都会被覆盖并导出到新的Excel文件中.

Amended to onyl create one query that gets overwritten in each iteration and exported to a new Excel file.

这篇关于将MS Access表拆分为多个部分，然后使用VBA导出到Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！