Excel VBA删除公式并另存为v2

先生Excelicius

在下面的代码中,我试图进行调整,以便我遍历选项卡上的所有文件,并且如果范围具有将其转换为值的公式。但仅适用于特定选项卡。在关闭并保存文件之前,我想将其保存为当前名称并在末尾添加“ _v2”。有人可以帮忙吗?

    Sub LoopAllExcelFilesInFold2()
    'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    Dim rng As Range
    'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
   .Title = "Select A Target Folder"
   .AllowMultiSelect = False
   If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xls*"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)

'Ensure Workbook has opened before moving on to next line of code
DoEvents
On Error Resume Next

Sheets("Cubes Act'20").Select
For Each rng In ActiveSheet.UsedRange
If rng.HasFormula Then
rng.Formula = rng.Value
End If
Next rng








'Save and Close Workbook
'wb.Close SaveChanges:=True
wb.Close ActiveWorkbook.SaveCopyAs("filename" & "_v2")

'Ensure Workbook has closed before moving on to next line of code
DoEvents

'Get next file name
myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"

ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
托尼·达利摩

我相信该功能NextFileName()可以满足您的需求。

我的文件上几乎总是有版本号,因此完成文件“ AAA V27.xxx”的更新后,我经常需要找到新名称“ AAA V28.xxx”。我将功能保留NextFileName()在PERSONAL.XLSB中,因此可以从任何工作簿中调用它。

NextFileName()这里的版本不是我的最新版本。一年前,我遇到了根据日期处理文件版本的需求,并创建了一个NextFileName()处理此类文件名的新版本我找到了旧版本,并在找不到版本号的情况下对其进行了增强以添加“ V2”。

您可以TestNextFileName()根据需要检查我的例行程序。

Option Explicit
Sub TestNextFileName()

    Debug.Print ("AAA 5.abc     -> " & NextFileName("AAA 5.abc"))
    Debug.Print ("BBB V9.abc    -> " & NextFileName("BBB V9.abc"))
    Debug.Print ("CCC V05.abc   -> " & NextFileName("CCC V05.abc"))
    Debug.Print ("DDD V1.99.abc -> " & NextFileName("DDD V1.99.abc"))
    Debug.Print ("EEE.abc       -> " & NextFileName("EEE.abc"))

End Sub
Public Function NextFileName(ByVal CrntFileName As String) As String

  ' * CrntFileName should be of the format "zzzzyy.xxx" where
  '     yy represents a string of one or more decimal digits
  '     the final z is anything but a decimal digit.
  '     xxx represents the extension
  ' * If CrntFileName is of this format, the routine returns "zzzzww.xxx" where
  '     ww is one more than yy.
  ' * If CrntFileName is not of this format. the routine returns "zzzz V2.xxx".
  ' * Examples:
  '     AAA 5.abc      -> AAA 6.abc
  '     BBB V9.abc     -> BBB V10.abc
  '     CCC V05.abc    -> CCC V06.abc
  '     DDD V1.99.abc  -> DDD V1.100.abc
  '     EEE.abc        -> EEE V2.abc
  ' 26Jul11  Coded and tested under VB 2010.
  ' 22Nov11  Amended for VBA
  ' 14Jan19  Became obsolete when version that allowed for other options coded.
  ' 16Apr20  Resurrected in response to SO question.  Amended to add V2 if no
  '          version number found

  Dim Extn As String
  Dim Name As String
  Dim Pos As Integer
  Dim Version As String

  ' Split CrntFileName into name and extension
  Pos = InStrRev(CrntFileName, ".")
  If Pos = 0 Then
    Name = CrntFileName
    Extn = ""
  Else
    Name = Mid(CrntFileName, 1, Pos - 1)
    Extn = Mid(CrntFileName, Pos)       ' Includes dot
  End If

  Pos = Len(Name)
  Do While True
    If IsNumeric(Mid(Name, Pos, 1)) Then
      Pos = Pos - 1
    Else
      Pos = Pos + 1
      Exit Do
    End If
    Loop
  ' If Pos > Len(Name), there are no trailing digits
  ' if Pos <=Len(Name), Pos identifies the first or only trailing digit.

  If Pos > Len(Name) Then
    NextFileName = Name & " V2" & Extn
  Else
    Version = Mid(Name, Pos) + 1      ' Next version number
    ' Add leading zeros if necessary to pad to original length
    Do While Len(Version) < Len(Mid(Name, Pos))
      Version = "0" & Version
    Loop
    NextFileName = Mid(Name, 1, Pos - 1) & Version & Extn
  End If

End Function

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

VBA-Excel-另存为并删除原始工作簿

Excel另存为框

Excel VBA,另存为.txt时保持格式

Excel VBA UserForm打印屏幕另存为pdf

VBA Excel将副本另存为txt文件

使用excel VBA在IE中单击“另存为”

VBA从Access将Excel文件另存为.xls

Excel VBA另存为制表符分隔文件

使用VBA在Access中将Excel文件另存为文本

将熊猫另存为Excel文件时,如何使用熊猫插入Excel公式?

Excel工作簿另存为.pdf

另存为不接受包含“。”的字符串。在Excel VBA中

Excel VBA打开Word模板,填充,然后另存为.docx文件

如何使用Excel VBA打开.csv并将其另存为.xlsx

VBA Excel基于图纸颜色另存为PDF-根据标签颜色创建图纸阵列

VBA Excel将新文件另存为活动文件的日期

Excel VBA另存为不带分隔符的文本文件

Excel VBA 在默认文件夹中将文件另存为 Word 文档

如何另存为excel和vba项目以备将来使用?

我可以用Excel中的VBA保存功能替换“保存”或“另存为”按钮吗?

如何在Excel VBA中将每个工作表的首页另存为PDF

Excel VBA - 使用通配符或部分匹配打开的工作簿无法另存为副本

Excel VBA另存为名称“MONTH”转换为数字(两位数)

Excel VBA 宏循环“另存为”会增加文件大小?

Excel VBA - 使用特定工作表中的内容另存为文件

如何使用 Excel VBA 将另存为 HTML 电子邮件?

如何在excel VBA中仅在“另存为”而不是在正常的“保存”上运行宏

将excel另存为pdf,将文件名另存为变量值

将 Excel 直接下载到驱动器 (C:\)(不需要另存为选项)VBA Excel