我怎么能打开每一个excel工作簿来列出columns.autofit属性？ [英] how can I have every excel workbook I open to have columns.autofit property?

问题描述

我想编写一个宏或调整Excel属性，以便每当我打开一个新的excel工作簿时，所有值都将自动调整到列。

I would like to write a macro or adjust Excel properties, so that whenever I open a new excel workbook all values are autofited to the columns.

我尝试使用以下代码，如果我保存特定工作簿的代码工作，但是如果我将代码保存在PERSONAL工作簿中，代码会产生错误。 对象_Global的方法列失败

I tried using the following code, which works if I save the code for a specific workbook, however if I save the code in the PERSONAL workbook, the code produces an error. "Method 'Columns' of object '_Global' failed"

Sub auto_open()
   Columns().AutoFit
End Sub

推荐答案

创建一个简单的加载项来处理我想拦截的应用程序事件。在auto_open（）中不起作用的原因是因为您在实例化之前尝试使用Columns对象。更好地使用SheetActivate事件。这也避免了打开20页的工作簿的可能性，并且必须等待所有这些工作到AutoFit。您只需 活动工作表，对吧？

The way I do this is to create a simple add-in that handles Application events I want to intercept. The reason that it doesn't work in the auto_open() is because you are trying to work with the Columns object before it gets instantiated. Much better to use the SheetActivate event. This also avoids the possibility of opening a Workbook with 20 pages and having to wait for all of them to AutoFit. You only see the active sheet, right?

这个概念是抓住一个引用WithEvents给应用程序，设置处理程序到您关心的任何事件。要做到这一点，你必须把代码放到一个类中。我打电话给我的AppHolder。

The concept is to grab a reference WithEvents to the application and set up handlers to whatever events you care about. To do this, you'll have to put the code into a class. I called mine "AppHolder".

类代码：

Option Explicit
Private WithEvents app As Application

Private Sub Class_Initialize()

    Set app = Application

End Sub

Private Sub app_SheetActivate(ByVal Sh As Object)

    Sh.Columns().AutoFit

End Sub

Private Sub app_WorkbookActivate(ByVal Wb As Workbook)

    Wb.ActiveSheet.Columns().AutoFit

End Sub

Private Sub app_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

    Sh.Columns().AutoFit

End Sub

然后，在ThisWorkbook模块中创建一个类的实例并将其设置在auto_open（或者Workbook_Open（视情况而定））中：

Then, create an instance of your class in and set it in auto_open (or Workbook_Open as the case may be) in the ThisWorkbook module:

Option Explicit
Private hook As AppHolder

Private Sub Workbook_Open()

    Set hook = New AppHolder

End Sub

将其作为Excel加载项文件（.xlam）保存在默认位置 - 应该在Users [You] \AppData\Roaming\Microsoft\AddIns中。关闭Excel并重新打开它，然后转到Developer ...加载项并启用它。所有的都是这样的。

Save it as an Excel add-in file (.xlam) in the default location - should be in Users[You]\AppData\Roaming\Microsoft\AddIns. Close Excel and re-open it, then go to Developer...Add-Ins and enable it. All there is to it.

编辑：几乎忘记了 - 这并不涵盖所有提交工作表的情况。您还需要WorkbookActivate和WorkbookNewSheet ...

Almost forgot - that doesn't cover all situations in which you'll be presented with a Worksheet. You need WorkbookActivate and WorkbookNewSheet too...

这篇关于我怎么能打开每一个excel工作簿来列出columns.autofit属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！