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

[Calhoun99]

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!

 

[MakeItSo]

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:

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.

 

[combo]

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

 

[strongm]

>I have a module in Access

The Access Application object does not implement the DisplayAlerts property

 

[Calhoun99]

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