VBA-解决问题的解决方案Excel保存临时文件 [英] vba - workaround for issue excel saving temp files

问题描述

保存特定工作簿时，Excel会创建一个临时文件而不是保存数据(不显示错误或警告消息).症状大致与此帖子中描述的相同: microsoft-excel返回生成2GB临时文件后未保存的错误文档我尝试了几种解决方案，但决定实施一种解决方法，因为另存为"工作正常.

When saving a specific workbook, Excel creates a temp file instead of saving the data (without displaying an error or warning message). The symptoms are roughly the same as described in this post: microsoft-excel-returns-the-error-document-not-saved-after-generating-a-2gb-temp-file I tried several solutions, but decided to implement a work-around as ‘save as’ is working ok.

下面的代码基于文件名以值结尾(例如myFile V1.xlsm)执行另存为"，每次保存工作簿时，宏都会添加一个增量字符(从a到z).(例如myFile V1a.xlsm).

The code below performs the ‘save-as’, based on having filenames ending with a value (e.g. myFile V1.xlsm), the macro will add an incremental character (a to z) each time the workbook is saved. (e.g. myFile V1a.xlsm).

该宏在标准模块中可以正常工作，但是当移动到"thisWorkbook"时，它会导致Excel停止响应".我通过将其保留在标准模块中并将键组合"control-s"分配给宏来解决"此问题.仍然很想知道是否可以在"thisWorkbook"中使用它.

The macro works fine in a standard module, but it causes Excel to "stop responding" when moved to ‘thisWorkbook’. I ‘solved’ this by keeping it in the standard module and assigning key combination ‘control-s’ to the macro. Still interested to know if it can be made to work in the ‘thisWorkbook’.

此解决方法的缺点是每次增量保存都会阻塞最近文件"列表.从最近的文件历史记录中删除以前的文件名将是很好的选择，但这似乎无法通过VBA来完成.( VBA-如何从excel 2007的最近文档列表中删除文件?).有什么建议吗?

Drawback of this workaround is that each incremental save clogs up the ‘recent file’ list. It would be nice to remove the previous file name from the recent file history, but this seems not possible to do via VBA. (VBA - How do I remove a file from the recent documents list in excel 2007?). Any suggestions?

Windows 10，Excel 2016(版本16.0.6868.2060)

Windows 10, Excel 2016 (version 16.0.6868.2060)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim newFilename As String
Dim oldFilename As String

oldFilename = ActiveWorkbook.Name
newFilename = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)

If IsNumeric(Right(newFilename, 1)) = True Then

    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path + "\" + newFilename & "a.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Else
    If Right(newFilename, 1) = "z" Then
        MsgBox "'z' reached, please save as new version"
        Exit Sub
    End If

    newFilename = Left(newFilename, Len(newFilename) - 1) & Chr(Asc(Right(newFilename, 1)) + 1)
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path + "\" + newFilename & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End If

'potential code to remove oldFilename from 'Recent File' list

End Sub

推荐答案

我在Excel 2010中测试了此Sub，它对我有用.我删除文件后立即中断循环，因为我认为索引可能与循环不一致.一种更完善的变体可能会遍历最近的文件列表，并创建要删除的索引集合，然后向后遍历该集合并依次删除每个条目.

I tested this Sub in Excel 2010 and it works for me. I immediately break the loop after deleting the file as I assume the indexing may get out of alignment with the loop. A more refined variant might loop through the recent file list and create a collection of indices to delete, then iterate backward over that collection and delete each entry in turn.

Public Sub RemoveRecentFile(strFileName As String)

    Dim collRecentFiles As Excel.RecentFiles
    Dim objRecentFile As Excel.RecentFile
    Dim intRecentFileCount As Integer
    Dim intCounter As Integer

    Set collRecentFiles = Application.RecentFiles
    intRecentFileCount = collRecentFiles.Count

    For intCounter = 1 To intRecentFileCount
        Set objRecentFile = collRecentFiles(intCounter)
        If objRecentFile.Name = strFileName Then
            objRecentFile.Delete
            Exit For
        End If
    Next intCounter

End Sub

这篇关于VBA-解决问题的解决方案Excel保存临时文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！