具有VBA的大文件大小复制范围 [英] Large File Size Copy Ranges with VBA

问题描述

我有一个常规，通过生成对旧表格的单元格引用来复制表格。我的问题是，当我运行这个例程在三张工作簿大小为280KB它气球高达80MB。如果我复制和粘贴工作表值以删除公式，工作簿将保持相同的大小。如果我删除工作表并保存，则返回到原始大小。有没有一些我失踪的方式VBA处理内存导致这个问题？我已经尝试在例程结束时将所有变量设置为无。

I have a routine that duplicates a sheet by generating cell references to the old sheet. My problem is for that when I run this routine on a three sheets in a workbook sized 280KB it balloons up to 80MB. If I copy and paste the sheet values to remove the formulas, the workbook stays the same size. If I delete the sheets and save then it returns to it's original size. Is there something I'm missing in the way VBA handles memory that is causing this issue? I've already tried setting all of the variables to nothing at the end of the routine.

Public Sub CopySheetFormulas(inputSheet As Worksheet, outputSheet As Worksheet)
Dim rowCounter As Long
Dim columnCounter As Long
Dim maxRow As Long
Dim maxColumn As Long

maxColumn = inputSheet.Cells.Find(What:="*", After:=inputSheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

Debug.Print "Max Column: " & maxColumn

maxRow = inputSheet.Cells.Find(What:="*", After:=inputSheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

Debug.Print "Max Row: " & maxRow

inputSheet.Range("A1").Resize(maxRow, maxColumn).Copy

With outputSheet
    .Activate
    .Range("A1").Select
    .Paste Link:=True
End With

End Sub

推荐答案

XLSX文件将单元格内容存储为字符串。但是，他们自己不会很好的清理干净。所以如果你删除某些东西，它并不总是减少文件的大小。

XLSX files store the cell contents as a string. But they don't clean up after themselves very good. So if you delete something, it doesn't always reduce the file size.

如果你只是复制表格，它不应该变得如同生成链接一样大所有的细胞。这是因为每个链接都存储为一个单独的字符串。

If you merely copy the sheet it shouldn't become as big as if you generate links to all the cells. Thats because each link is stored as a separate string.

http：//www.ozgrid。 com / Excel / ExcelProblems.htm

很可能原始工作表有一些修改可以到达最后一行。然后当你将它复制成链接时，它会变得疯狂，因为它正在生成数百万个链接。检查maxColumns和maxRows的值。

It's very possible that the original sheet has some modification that reaches to the very last row. Then when you copy it as links it goes crazy because it is generating millions of links. Check the value of maxColumns and maxRows.

这篇关于具有VBA的大文件大小复制范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！