将更改保存到新文件,而不更改原始文件。回滚或编辑新文件?

huangapple go评论105阅读模式
英文:

Save changes into new file without changing original. Rollback or edit a new file?

问题

我有一个宏,它会将特定颜色的单元格中的公式替换为数值,然后将工作表保存为一个单独的文件。问题是,我不希望这些更改应用到原始文件上。所以,问题是 - 哪种方式更好、更容易?是先保存工作表,然后进行更改,还是在创建新文件后回滚更改?

我不太懂VBA,所以代码可能有点混乱,对此抱歉。

Sub Convertan()
    Dim rng As Range
    Dim formulaCell As Range
    Set rng = ActiveSheet.UsedRange

    '检查范围内的每个有颜色的单元格是否具有公式
    For Each formulaCell In rng
        If formulaCell.HasFormula Then
            If formulaCell.Interior.ColorIndex = 24 Then
                formulaCell.Formula = formulaCell.Value
            End If
        End If
    Next formulaCell

    ActiveSheet.Copy '将活动工作表复制到新工作簿
    ActiveWorkbook.SaveAs Range("B2").Value
End Sub
英文:

I have a macro that is replacing formulas in specific colored cells with values and then saves worksheet as a separate file. The problem - i don't want this changes to be applied to original file. So, the question - which way is better and easier to go? To save worksheet and then make changes, or rollback changes after new file is created?
I'm not very good with VBA, so code can be a little bit messy, sorry for that.

Sub Convertan()
Dim rng As Range
Dim formulaCell As Range
Set rng = ActiveSheet.UsedRange

'Check each colored cell in the range if it has a formula
For Each formulaCell In rng
If formulaCell.HasFormula Then
    If formulaCell.Interior.ColorIndex = 24 Then
    formulaCell.Formula = formulaCell.Value
    
    End If
End If
Next formulaCell

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("B2").Value

End Sub

答案1

得分: 1

导出工作表至新工作簿

  • 在新工作簿中创建工作表的副本,并处理该副本,保持原始工作表不变。
Sub Convertan()

    If ActiveSheet Is Nothing Then Exit Sub ' 没有可见工作簿打开
    If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' 不是工作表

    ActiveSheet.Copy

    Dim wb As Workbook: Set wb = Workbooks(Workbooks.Count)
    Dim ws As Worksheet: Set ws = wb.Worksheets(1)

    Dim urg As Range, cell As Range

    For Each cell In ws.UsedRange.Cells
        If cell.HasFormula Then
            If cell.Interior.ColorIndex = 24 Then
                If urg Is Nothing Then
                    Set urg = cell
                Else
                    Set urg = Union(urg, cell)
                End If
            End If
        End If
    Next cell

    If Not urg Is Nothing Then
        Dim arg As Range
        For Each arg In urg.Areas
            arg.Value = arg.Value
        Next arg
        'urg.Interior.ColorIndex = xlNone ' 更改颜色
    End If

    Application.DisplayAlerts = False ' 无需确认覆盖
        wb.SaveAs ws.Range("B2").Value
    Application.DisplayAlerts = True

    Dim FilePath As String: FilePath = wb.FullName

    wb.Close SaveChanges:=False ' 刚刚保存

    MsgBox "工作表已导出至 " & FilePath & ".", vbInformation

End Sub
英文:

Export a Worksheet to a New Workbook

  • Create a copy of the worksheet in a new workbook and process the copy leaving the original intact.

<!-- language: lang-vb -->

Sub Convertan()
    
    If ActiveSheet Is Nothing Then Exit Sub &#39; no visible workbooks open
    If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub &#39; not a worksheet
    
    ActiveSheet.Copy
    
    Dim wb As Workbook: Set wb = Workbooks(Workbooks.Count)
    Dim ws As Worksheet: Set ws = wb.Worksheets(1)
    
    Dim urg As Range, cell As Range
    
    For Each cell In ws.UsedRange.Cells
        If cell.HasFormula Then
            If cell.Interior.ColorIndex = 24 Then
                If urg Is Nothing Then
                    Set urg = cell
                Else
                    Set urg = Union(urg, cell)
                End If
            End If
        End If
    Next cell
    
    If Not urg Is Nothing Then
        Dim arg As Range
        For Each arg In urg.Areas
            arg.Value = arg.Value
        Next arg
        &#39;urg.Interior.ColorIndex = xlNone &#39; change the color
    End If
        
    Application.DisplayAlerts = False &#39; overwrite without confirmation
        wb.SaveAs ws.Range(&quot;B2&quot;).Value
    Application.DisplayAlerts = True
    
    Dim FilePath As String: FilePath = wb.FullName
    
    wb.Close SaveChanges:=False &#39; just got saved

    MsgBox &quot;Worksheet exported to &quot;&quot;&quot; &amp; FilePath &amp; &quot;&quot;&quot;.&quot;, vbInformation

End Sub

huangapple
  • 本文由 发表于 2023年6月2日 05:52:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/76385956.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定