CODEWITHSUNDEEP
vbaexcelsas
ESmyth5988
ESmyth5988

Reputation: 518

VBA - object required error when setting .DisplayAlerts = False

Background:

I am primarily a SAS programmer (a statistical software), but I'm using VBA to make excel play nicely with SAS. I wrote a program that I can execute in SAS using the following code:

*Batch Convert Excel files to CSV files;
%LET xlsfile=C:\xlsfile.csv;
%LET csvfile=C:\csvfile.csv;
%LET worksheet=1;
x 'cd "C:\location_of_vbsfile"';
x "vbsfile.vbs &xlsfile &worksheet &csvfile";

The program opens an excel file, selects the required sheet, replaces commas, carriage returns, and line feeds, then saves the file as a csv file so I can nicely import the file into SAS.

Problem:

I want to to eliminate any message boxes that may be produced. I added an Application.DisplayAlerts = False to the top of the program to prevent message boxes from being displayed. However, I get the following error, so something must be wrong.

Error:      Object required: 'DisplayAlerts'

This is my code:

'1  - Open Excel
Dim oExcel
Dim oBook
Set oExcel = CreateObject("Excel.Application")

'THIS IS WHAT I ADDED
Set oExcel.DisplayAlerts = False

Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
xlSheet = Wscript.Arguments.Item(1)*1

'2  - Select Sheet
oBook.Worksheets(xlSheet).Select

'3  - Find and Replace
oBook.Worksheets(xlSheet).Cells.Replace ",", ""
oBook.Worksheets(xlSheet).Cells.Replace chr(13), ""
oBook.Worksheets(xlSheet).Cells.Replace chr(10), ""

'4  - Save
oBook.SaveAs WScript.Arguments.Item(2), 6
oBook.Close False
Set oExcel.DisplayAlerts = True
oExcel.Quit

Any suggestions?

Upvotes: 2

Views: 2397

Answers (1)

dennythecoder
dennythecoder

Reputation: 772

Don't use "Set" before it. Set is used for actual objects.

Just:

oExcel.DisplayAlerts = True

Upvotes: 3

Related Questions