在 excel 2007 中导致错误。如何在不兼容模式下添加工作簿

发布于 2024-12-03 00:47:20 字数 861 浏览 0 评论 0原文

事实证明，发生错误是因为我制作的新工作簿 workbook.add 是在与 excel 2003 的兼容模式下制作的，其限制为 65536 行。我尝试粘贴的范围的行数比这更多。如何添加处于兼容模式的工作簿？

For c = 1 To Round(z / x + 0.5)
Rows("9:" & x).Select
Selection.Copy
Workbooks.Add
Range("A9").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Selection.Delete
Rows("1:8").Copy
ActiveWindow.ActivatePrevious 
Range("A1").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("E3") = c 
    ActiveWorkbook.SaveAs Filename:=FileLocation2 & "\" & g & "-" & c _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
ThisWorkbook.Save
ActiveWindow.Close
Next c

原文

turns out the error is occuring ebcause the new work book i made workbook.add is made in compatability mode with excel 2003 which has a limit of 65536 rows. and the range im trying to paste has more rows than that. how do i add a workbook that is nto in compatability mode??

For c = 1 To Round(z / x + 0.5)
Rows("9:" & x).Select
Selection.Copy
Workbooks.Add
Range("A9").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Selection.Delete
Rows("1:8").Copy
ActiveWindow.ActivatePrevious 
Range("A1").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("E3") = c 
    ActiveWorkbook.SaveAs Filename:=FileLocation2 & "\" & g & "-" & c _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
ThisWorkbook.Save
ActiveWindow.Close
Next c

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

唱一曲作罢 2024-12-10 00:47:20

您是否尝试在粘贴之前不选择每个单元格？（在 vba 中使用之前，您不需要选择每个单元格） - 如果您只想复制值，则不需要复制/粘贴（速度非常非常慢）

Dim ws as Worksheet
Set ws = ActiveSheet
For c = 1 To Round(z / x + 0.5)
    Workbooks.Add
    ActiveSheet.Range("A9").Value = ws.Rows("9:" & x).Value  'you shouldn't have to use ActiveSheet but i'll keep it to show the difference between ws and ActiveSheet
    ActiveWindow.ActivateNext  'this is dangerous because you can't be sure which window will be activated next
    Selection.Delete           'this is dangerous too. Can't you tell which row is this instead of using Selection? Is this macro user triggered?
    Rows("1:8").Copy
    ActiveWindow.ActivatePrevious 'same comment as above
    Range("A1").Paste
    Range("E3").Value = c
    ActiveWorkbook.SaveAs Filename:=FileLocation2 & "\" & g & "-" & c _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ThisWorkbook.Save
    ActiveWindow.Close
Next c

Did you try without selecting each cell before pasting? (you don't need to select every cell before using it in vba) - and you don't need to copy / paste if you only want to copy the value (it is very very slow)

Dim ws as Worksheet
Set ws = ActiveSheet
For c = 1 To Round(z / x + 0.5)
    Workbooks.Add
    ActiveSheet.Range("A9").Value = ws.Rows("9:" & x).Value  'you shouldn't have to use ActiveSheet but i'll keep it to show the difference between ws and ActiveSheet
    ActiveWindow.ActivateNext  'this is dangerous because you can't be sure which window will be activated next
    Selection.Delete           'this is dangerous too. Can't you tell which row is this instead of using Selection? Is this macro user triggered?
    Rows("1:8").Copy
    ActiveWindow.ActivatePrevious 'same comment as above
    Range("A1").Paste
    Range("E3").Value = c
    ActiveWorkbook.SaveAs Filename:=FileLocation2 & "\" & g & "-" & c _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ThisWorkbook.Save
    ActiveWindow.Close
Next c

回复收藏 0 原文