Disabling excel alerts using VB Script

Just set .DisplayAlerts to false for the Excel Application object before opening the file. Or make sure the XLSX is created correctly ;-).

Hi,

I did the .Displayalerts option to false, however this doesn't seem to work. Can i add a line of code which sends an email in case an excel file with unreadable content is found?

Here's what i have written:

Const DestinationFolder = "C:\Users\aditya.kumar.vaish\Downloads\Trial_Runs\Matcher Input\"
Dim dicErrors1 : Set dicErrors1 = CreateObject("Scripting.Dictionary")
Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder(DestinationFolder).Files
  
If LCase(fso.GetExtensionName(f)) = "xlsx" Then
On Error Resume Next
Set wb = app.Workbooks.Open(f.Path)
If Err.Number <> 0 Then
dicErrors1.Add dicErrors1.count, fso.GetBaseName(wb.Name) & ".xlsx" 
Else 
newname = fso.BuildPath(wb.Path, fso.GetBaseName(wb.Name) & ".xls")
wb.SaveAs newname, -4143
    wb.Close True
f.Delete True
  End if
End If
Next

for y = 0 to dicErrors1.count - 1
  mailMessage1 = mailMessage1 & vbNewLine & dicErrors1.Item(y)
Next

If mailMessage1 <> "" Then
Mytim = Now
ToAddress = "aditya.kumar.vaish@abc.com"
MessageSubject = "Unread Information for list ran on " &Mytim
MessageBody = "Input files were not loaded due to unreadable contents." & vbNewLine & " Error number is " & Err.Number & "Error is " & Err.Description
Set ol = WScript.CreateObject("Outlook.Application")
Set ns = ol.getNamespace("MAPI")
Set newMail = ol.CreateItem(olMailItem)
newMail.Subject = MessageSubject
newMail.Body = MessageBody1 & vbNewLine & vbNewLine & "Thanks," & vbNewLine & "Automatcher" & vbCrLf & MyTime1
newMail.RecipIents.Add(ToAddress)
newMail.Send
End If

Select allOpen in new window

Thanks,
Aditya

Since that is a "fatal" error, I don't think you can skip that, or check for the error to occur and take action.

Hi,

Ok, thanks for the info.

Thanks,
Aditya

Hi, in your Open call, specify the 15th parameter for CorruptLoad.
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open.aspx

This should allow you to specify xlRepairFile. Add Const xlRepairFile = 1 to your code.  Checking the error code as you already are hopefully gives you what you need.

Rob.

Hi Rob,

Thanks for the suggestion. Here's what i wrote:

Const xlRepairFile = 1
Const DestinationFolder = "C:\Users\aditya.kumar.vaish\Downloads\Trial_Runs\Matcher Input\"
Set app = CreateObject("Excel.Application")
app.Visible = true
app.DisplayAlerts = true
Set fso = CreateObject("Scripting.FileSystemObject")
For Each f In fso.GetFolder(DestinationFolder).Files  
If LCase(fso.GetExtensionName(f)) = "xlsx" Then
Set wb = app.Workbooks.Open(f.Path, CorruptLoad = xlRepairFile)
newname = fso.BuildPath(wb.Path, fso.GetBaseName(wb.Name) & ".xls")
wb.SaveAs newname, -4143
    wb.Close True
f.Delete True
  End if
Next

Select allOpen in new window

However, it doesn't seem to work and I get the error: "Unable to get the Open Property of the Workbooks."

Could you advise if I am wrong somewhere?

Thanks,
Aditya

Since the automation methods don't support the argument name when specifying parameters, you must present them in the exact order for the function.  Try
Set wb = app.workbooks.open(f.path,,,,,,,,,,,,,,xlRepairFile)

Rob.

Hi Rob,

I tried this, however, i am still getting the same error: "Unable to get the Open Property of the Workbooks."

Aditya

What operating system and Office version are you running on? I will test it out.

Hi, this has worked for me....

Const xlRepairFile = 1
Const DestinationFolder = "C:\Users\aditya.kumar.vaish\Downloads\Trial_Runs\Matcher Input\"
Set app = CreateObject("Excel.Application")
app.Visible = true
app.DisplayAlerts = true
Set fso = CreateObject("Scripting.FileSystemObject")
For Each f In fso.GetFolder(DestinationFolder).Files  
	If LCase(fso.GetExtensionName(f)) = "xlsx" Then
		Set wb = app.Workbooks.Open(f.Path, , , , , , , , , , , , , , xlRepairFile)
		newname = fso.BuildPath(wb.Path, fso.GetBaseName(wb.Name) & ".xls")
		'WScript.Echo "Saving as " & newname
		wb.SaveAs newname, -4143
		wb.Close True
		f.Delete True
	End if
Next
app.Quit

Select allOpen in new window

Rob.

Hi Rob,

I tested it but it didn't work. I have shared the link to workbook that i have. Could you please see this and check if it works?

<LINK_REMOVED - RobSampson>

P.S. This data is confidential, please use it for testing only and destroy this copy later.

Regards,
Aditya

OK, so I finally found out that it worked on my Win7 x86 machine, but not my Win7 x64 machine.  It is related to the DCOM settings being incorrect, or missing.  I was reading here:
http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/11/12/microsoft-excel-does-not-appear-in-dcom-configuration-snap-in.aspx

and ran
C:\WINDOWS\SysWOW64>mmc comexp.msc /32

from an elevated command prompt, and then I also ran
excel.exe -REGSERVER

from the directory where Excel.exe is.

This didn't immediately solve the problem for me yet, but I did at least see the DCOM entry when I ran
C:\Windows\SysWOW64\dcomcnfg.exe
and looked under
Computers --> My Computer --> DCOM Config --> Microsoft Excel Application
although I noticed the Location is not present and the "Run application on this computer" box is greyed out.  This suggests it is still not registered properly, so I will be doing a repair soon, but I don't have time right now.

If that still doesn't work for you, perform a Repair installation of Microsoft Office, and try the script again.

Regards,

Rob.

Rob, just a side note - I tried on Vista x86 with a good XLSX and PowerShell, and the Open error as stated above appeared as soon as I provided the Format parameter (with "Nothing" resp. $null). I often use PowerShell to access Excel, so this is new to me.

I just tested this again against a brand new, empty XLSX file, and it worked fine.  Aditya, can you test whether this works for you with a normal XLSX file?  I will do further testing with your corrupt file.

Rob.

Hi Rob,

Thanks so much!! It's working now. I am glad that you were able to crack it. Awesome job!

Hi Qlemo,

Thanks for taking a shot at my query.

Regards,
Aditya

Ok great. Thanks for the grade.

Rob.