时间戳

首页 > 解决方案 > 保存外部工作簿后删除名称管理器中的所有输入

vba - 保存外部工作簿后删除名称管理器中的所有输入

问题描述

我目前正在制作一个创建目录的宏，然后将其以不同的语言保存为外部文件。每当我使用下面的 VBA 脚本保存文件时，文件仍然很大（2MB+），但每当我打开文件并删除名称管理器中的所有引用（这些似乎也被复制）时，文件只有 30/40 KB。

是否有 VBA 公式可以自动删除名称管理器中的公式（仅在外部副本中，不在原始文件中！）？

Sub NIP_Version()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks("Opbouw catalogus.xlsm").Activate

filenaam = ActiveWorkbook.Path & "\" & "Excel prijslijst" & "\" & Sheets("Catalogus").Range("A1").Text & " " & Sheets("Catalogus").Range("G2").Text

'Quotation blad copy
Sheets("Catalogus").Select

'paste as values

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Dim LastRowNIP As Long
With ActiveSheet
LastRowNIP = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

Set example = Range("A5:G" & LastRowNIP)

example.Value = example.FormulaR1C1

Columns("F").EntireColumn.AutoFit

'Save
Application.ScreenUpdating = True
Sheets("Catalogus").Range("A1").Select
ActiveSheet.Copy
ActiveWorkbook.Sheets("Catalogus").SaveAs Filename:=filenaam, FileFormat:=51

Application.DisplayAlerts = True

ActiveWorkbook.Close SaveChanges:=False

标签： vbasave

解决方案

Sub NIP_Version()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks("Opbouw catalogus.xlsm").Activate

filenaam = ActiveWorkbook.Path & "\" & "Excel prijslijst" & "\" & Sheets("Catalogus").Range("A1").Text & " " & Sheets("Catalogus").Range("G2").Text

'Quotation blad Copy
Sheets("Catalogus").Select

'Paste as value

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Dim LastRowNIP As Long
With ActiveSheet
LastRowNIP = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

Set example = Range("A5:G" & LastRowNIP)

example.Value = example.FormulaR1C1

Columns("F").EntireColumn.AutoFit

'Save
Application.ScreenUpdating = True
Sheets("Catalogus").Range("A1").Select
ActiveSheet.Copy
ActiveWorkbook.Sheets("Catalogus").SaveAs Filename:=filenaam, FileFormat:=51

Sub remove_names()

Dim xName As Name

    For Each xName In Application.ThisWorkbook.Names

            xName.Delete

    Next xName

End Sub

Application.DisplayAlerts = True

ActiveWorkbook.Close SaveChanges:=False

Workbooks("Prijslijst maken.xlsm").Activate

推荐阅读