首页 文章

将MS Access表拆分为多个部分并使用VBA导出到Excel

提问于
浏览 次
4

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

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

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

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

编辑：我正在使用的这个表由列组成：部件号（由各种字符组成，是唯一的），描述，价格，注释 . 它没有ID号，从1到50000每个与记录有关 .

vba excel-vba ms-access access-vba excel

3 回答

  • 2

    试试这个：

    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文件 .

    Edit: 修改为onyl创建一个在每次迭代中被覆盖并导出到新Excel文件的查询 .

    回复于
  • 3

    由于您可能在表上有唯一的数字ID，请创建这三个查询并逐个导出：

    Select * From YourTable Where Id Mod 3 = 0

 Select * From YourTable Where Id Mod 3 = 1

 Select * From YourTable Where Id Mod 3 = 2

    Option: Add virtual row number:

    创建ans ave这样的查询：

    SELECT RowCounter([ProductKey],False) AS Id, *
FROM YourTable
WHERE (RowCounter([ProductKey],False) <> RowCounter("",True));

    使用下面的功能 . 然后调整三个查询以使用新查询 .

    Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long

' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String

  On Error GoTo Err_RowCounter

  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If

  RowCounter = col(strKey)

Exit_RowCounter:
  Exit Function

Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function
    回复于
  • 3

    如果您有一个标准，您可以分配数据，通过查询，查询集或参数化查询完成此分区，您可以使用查询的SQL属性和VBA Replace() 函数替换代码 .

    如果没有条件，请在临时表中创建一个：

    • 将表中的所有数据插入临时表，添加一个布尔字段 Exported ，默认值 False ;

    • 创建查询 MyQuery with a specific order （可能在主键上），以便您拥有一个包含您要使用的所有数据的对象：

    SELECT TOP 20000 * FROM TempTable WHERE Exported = FALSE ORDER BY [Part Number]

    • 将 MyQuery 中的数据导出到Excel;

    • 将 MyQuery 中的记录设置为 TRUE ：

    UPDATE MyQuery SET Exported = TRUE

    在 MyQuery 为空之前，然后删除 TempTable 或它的内容 .

    回复于

相关问题