使用"Application.DisplayAlerts = False"给出“未找到方法或数据成员".编译错误 [英] Using "Application.DisplayAlerts = False gives "Method or Data Member not found" compile error

问题描述

我在Access中有一个模块可以访问Excel电子表格中的格式单元格.

I have a module in Access to format cells in an Excel spreadsheet.

Sub FormatData()

Workbooks.Open FileName:="C:\Users\john.doe\Documents\scripts\apps\allow\Weekly_Cash_Trending"

Workbooks("Weekly_Cash_Trending.xlsx").Activate


Columns("C:C").Select
Selection.NumberFormat = "$#,##0"
Range("A1").Select

ActiveWorkbook.SaveAs FileName:="C:\Users\john.doe\Documents\scripts\apps\allow\Weekly_Cash_Trending"
End Sub

它可以工作，但是会弹出一个弹出窗口，以确认保存文件.

It works, but it gives a pop up to confirm saving the file.

Excel消息

当我更改代码以使用"Application.DisplayAlerts"

When I change the code to use "Application.DisplayAlerts"

Sub FormatData()

Application.DisplayAlerts = False
Workbooks.Open FileName:="C:\Users\john.doe\Documents\scripts\apps\allow\Weekly_Cash_Trending"

Workbooks("Weekly_Cash_Trending.xlsx").Activate


Columns("C:C").Select
Selection.NumberFormat = "$#,##0"
Range("A1").Select

ActiveWorkbook.SaveAs FileName:="C:\Users\john.doe\Documents\scripts\apps\allow\Weekly_Cash_Trending"
Application.DisplayAlerts = True
End Sub

我收到以下错误:找不到方法或数据成员

I get the following error: Method or Data Member not found

找不到方法或数据成员错误

我是VBA编码的新手(显然)，有人可以帮助我解决这个问题吗?谢谢！

I am new to VBA coding (obviously), can anyone help me resolve this? Thanks!

推荐答案

• 您正在Access中对此进行编码，所以我的理解是在这种情况下 Application 是 Access.Application (不是 Excel.Application )-并且 Access.Application 没有 DisplayAlerts 成员(与Excel不同).
• 您似乎正在调用 Workbook.SaveAs 方法来保存工作簿本身.如果需要，请使用 Workbook.Save (这也意味着您不需要切换 DisplayAlerts ).另外， Workbook.Close 具有一个 SaveChanges 参数，您可以向其中传递 True 或 False .
• You are coding this in Access, so my understanding is that Application in this context is Access.Application (not Excel.Application) -- and Access.Application has no DisplayAlerts member (unlike Excel).
• You appear to be calling Workbook.SaveAs method to save over the workbook itself. Use Workbook.Save if needed (it also means you don't need to toggle DisplayAlerts). Alternatively, Workbook.Close has a SaveChanges parameter to which you can pass True or False.

我认为下面的代码(我在Access中编写并测试了)应该可以满足您的要求.

I think the code below (I wrote and tested in Access) should do what you were after.

Option Compare Database
Option Explicit

Sub FormatData()
    With New Excel.Application ' Add reference to Excel Object Model for early binding
        '.Visible = True ' Uncomment if you want to see it happening

        With .Workbooks.Open(FileName:="C:\Users\john.doe\Documents\scripts\apps\allow\Weekly_Cash_Trending")
            .Worksheets(1).Range("C:C").NumberFormat = "$#,##0"
            .Close True ' Passing True here means we want to save changes as we close the workbook
        End With

        .Quit
    End With
End Sub

根据您的代码，我假设您已经在Access for Excel的对象库中添加了一个引用.如果没有，请在Access中打开VB编辑器，然后依次点击 Tools>参考书目>在列表中向下滚动到Microsoft Excel 16.0对象库(或类似的东西)>勾选>确定.

Based on your code, I assume you already added a reference in Access for Excel's object library. If not, in Access, open the VB Editor, then Tools > References > Scroll down in the list to Microsoft Excel 16.0 Object Library (or something like that) > Tick it > OK.

我假设您要更改第一个工作表上单元格的数字格式.如果该工作表具有名称，则最好使用名称来引用它(比起像我一样通过索引来引用).

I assume you want to change the number formatting of the cells on the first worksheet. If that sheet has a name, it's better if you refer to it by name (than by index like I did).

这篇关于使用"Application.DisplayAlerts = False"给出“未找到方法或数据成员".编译错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！