启用宏的Excel文件上的自动保存文件错误 [英] Autosaved file error on Macro Enabled Excel file

问题描述

我正在使用具有许多模块/表格的启用宏的文件(二进制表)，有时当笔记本电脑出现问题并且excel突然关闭时，我的自动保存文件不起作用.

I am using Macro Enabled file (binary sheet) which has many modules/forms and sometimes when something go wrong with my laptop and excel shuts down suddenly, my autosave files doesn't work.

我在每个自动保存的文件上都收到该错误:

I get that error on each autosaved file:

运行时错误'9'

Run-time error '9'

下标超出范围

我的自动保存频率是5分钟，可以保存我的背部，但有趣的是，此文件不适用于该文件.

My autosave frequency is 5 minutes to save my back but that interestingly doesn't work for this file.

我什至无法跟踪错误的位置，因为在该自动保存的文件上唯一打开的是空白页.(这就是为什么其他关于SO的运行时错误9问题没有回答我的问题)的原因是什么?什么是可能的解决方案?

I am even not able to follow where the error is because the only thing is opening on that autosaved file is White blank page. (That's why other Run-time error 9 questions on SO were not answer of my question) What kind of thing would be the reason and what is the possible solution?

更新:Workbook_我在该工作簿中拥有的打开事件

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    ActiveWindow.Visible = False
    SplashUserForm.Show
    Windows(ThisWorkbook.Name).Visible = True
    Application.ScreenUpdating = True
    

   With Sheet5
        .Unprotect Password:=""
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True, UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
    With Sheet16
        .Unprotect Password:=""
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True, UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
 
    
End Sub

这是我的SplashUserForm的内容:

And here what my SplashUserForm has:

Private Sub UserForm_Activate()
    Application.Wait (Now + TimeValue("00:00:01"))
    SplashUserForm.Label1.Caption = "Loading Data..."
    SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:01"))
    SplashUserForm.Label1.Caption = "Creating Forms..."
    SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:01"))
    SplashUserForm.Label1.Caption = "Opening..."
    SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:01"))
    Unload SplashUserForm
End Sub


Private Sub UserForm_Initialize()

HideTitleBar Me
With Me
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With

End Sub

Option Explicit
Option Private Module

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
Public Declare Function GetWindowLong _
                       Lib "user32" Alias "GetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong _
                       Lib "user32" Alias "SetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long, _
                       ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar _
                       Lib "user32" ( _
                       ByVal hWnd As Long) As Long
Public Declare Function FindWindowA _
                       Lib "user32" (ByVal lpClassName As String, _
                       ByVal lpWindowName As String) As Long

Sub HideTitleBar(frm As Object)
    Dim lngWindow As Long
    Dim lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

推荐答案

在与来自许多博客的许多人讨论之后，我最终提出了两种解决方案.(第一个类似错误处理，第二个则完全解决了该问题.)特别感谢YowE3k，jkpieterse和Ryan Wells.

After discussing with many people from many blogs, finally I came up with two solution. (First one is like error handling and the second one exactly solves the issue.) Special thanks to YowE3k,jkpieterse and Ryan Wells.

首先，我想提一提它发生的原因:

First of everything, I would like to mention the reason, why it was happening:

代码失败，因为当Excel恢复文件时，它会添加一些文本到窗口的标题，以便"FileName.xlsx"成为类似于"FileName.xlsx [版本最后由用户保存的版本]"(jkpieterse).

The code fails because when Excel recovers a file, it adds some text to the caption of the window, so that "FileName.xlsx" becomes something like "FileName.xlsx [Version last saved by user]" (jkpieterse).

解决方案:

1)基本错误处理(Ryan Wells)

1) Basic Error Handling (Ryan Wells)

如果我们知道哪一行导致错误，则可以注释掉该行以保护我们的工作簿.因此，如果您注释掉(或简单地删除)，则:

If we know which line is causing error, we can comment out that line to protect our workbook. So if you comment out (or simply delete) the :

ActiveWindow.Visible = False

Windows(ThisWorkbook.Name).Visible = True

行，这将解决问题.

2)原始解决方案(jkpieterse)

2) Original Solution (jkpieterse)

在 ThisWorkbook 对象表中使用下面的例程.

use a below routine in your ThisWorkbook object sheet.

Sub ShowaWindow(sFileName As String)
    Dim oWb as Workbook
    For Each oWb In Workbooks
        If lCase(owb.Name) = lCase(sFileName) Then
            oWb.Windows(1).Visible = True
            Exit For
        End If
    Next
End Sub

然后，在 Workbook_Open 事件中

代替 Windows(ThisWorkbook.Name).Visible = True

使用 ShowaWindow(ThisWorkbook.Name)

然后它将像魅力一样起作用！

Then it will work like a charm!

这篇关于启用宏的Excel文件上的自动保存文件错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！