×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

(OP)
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 (obviously not what you want when trying to automate something).


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



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

RE: Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

A few things to consider:
a) You are accessing an Excel workbook from Access without creating or "catching" an instance of Excel application first.
b) You are using objects like Range, Selection, or Application without specifying that you actually want to address Excel.Application or Excel.Range
c) You are using Selection for an operation that does not require selection.
d) Selecting a range directly before closing does not do anything
e) You are using "Save As" even though you are not saving in a different path or with a different name.

Try this:

CODE --> VBA

Sub FormatData()
Dim wb As Excel.Workbook, xl As Excel.Application
Dim wasRunning As Boolean

wasRunning = True

Set xl = GetObject(, "Excel.Application")
If xl Is Nothing Then
    Set xl = New Excel.Application
    wasRunning = False
End If

Set wb = Excel.Workbooks.Open(FileName:="C:\Users\john.doe\Documents\scripts\apps\allow\Weekly_Cash_Trending")
wb.Worksheets(1).Columns("C:C").NumberFormat = "$#,##0"
wb.Save
wb.Close

If Not wasRunning Then xl.Quit

End Sub 

Make sure to set a reference to Microsoft Excel.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

Quote (Calhoun99)

It works, but it gives a pop up to confirm saving the file
It works, because:
1) you have open excel,
2) access vba project has reference to excel library,
3) there is no conflict between used types of excel objects (as Workbooks, Range, Columns) and types in higher referenced libraries.
When you call Application, access vba assumes you mean access application, so the error. Verify the sheet you plan to format, your code works with active sheet (that can be random and depends on the selection on saving), MakeItSo assumes first.

combo

RE: Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

>I have a module in Access

The Access Application object does not implement the DisplayAlerts property

RE: Using “Application.DisplayAlerts = False gives ”Method or Data Member not found" compile error

(OP)
Thank you MakeItSo for the explanation and the code. It worked perfectly!
You too combo, thank you for your comments!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close