Error Code 800A03EC in VBS when pasting formula into Excel

31 views Asked by At

I have a template XLSX which I drag onto a VBS script. The VBS script gets the names of reviewers from one tab ("Reviewers List"), creates new tabs per reviewer name, and then writes consolidation formulas (which reference cells in those new tabs) into cells of another tab ("Review Template"). I get error code 800A03EC when writing those formulas (lines with objTemplate.Range("XX").Formula).

The thing that mystifies me is that if I insert a single quote to the beginning of the formulas and paste that I get no error, and then go into the output file and remove the quote, the formula works fine.

Option Explicit

Dim objFileSystemObject, strFile
Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")
strFile = objFileSystemObject.GetAbsolutePathName(Wscript.Arguments.Item(0))

Dim objExcel, objWorkbook
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(strFile)
objExcel.Visible = True

Dim objReviewers, objTemplate, objExport
Set objReviewers = objWorkbook.Sheets("Reviewers List")
Set objTemplate = objWorkbook.Sheets("Review Template")

' #########################################################################
' Get array of reviewer names so we can create their individual sheets and 
' write the consolidation formulas later.
Dim intIndex, intLastIndex, intRow
Dim arrNames(16)
intIndex = 0
For intRow = 2 To 17
    If objReviewers.Cells(intRow, 2).Value <> "" Then
        arrNames(intIndex) = objReviewers.Cells(intRow, 2).Value
        intIndex = intIndex + 1
    End If
Next
intLastIndex = intIndex - 1

' #########################################################################
' Copy the template into new sheets named for each reviewer, and while 
' we're at it, generate the consolidated results formula
Dim objNewSheet, strFormula1, strFormula2, strFormula3, n
strFormula1 = ""
strFormula2 = ""
strFormula3 = ""
n = ""
For intIndex = 0 To intLastIndex
    Call objTemplate.Copy(, objTemplate)
    Set objNewSheet = objWorkbook.Sheets("Review Template (2)")
    objNewSheet.Name = arrNames(intIndex)
    n = "'" & arrNames(intIndex) & "'"
    strFormula1 = strFormula1 & n & "!D2=""no"";"
    strFormula2 = strFormula2 & n & "!D2=""yes"";"
    strFormula3 = strFormula3 & "IF(" & n & "!Q2="""";"""";""" & arrNames(intIndex) & ": "" & " & n & "!Q2);"
    strFormula3 = strFormula3 & "IF(" & n & "!Q2="""";"""";CHAR(10));"
Next

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile & "___output.xlsx"

strFormula1 = Left(strFormula1, Len(strFormula1) - 1)
strFormula2 = Left(strFormula2, Len(strFormula2) - 1)
strFormula3 = Left(strFormula3, Len(strFormula3) - 1)
objTemplate.Range("D2").Formula = "=IF(OR(" & strFormula1 & ");""no"";IF(OR(" & strFormula2 & ");""yes"";""abs""))"
objTemplate.Range("Q2").Formula = "=CONCAT(" & strFormula3 & ")"

' #########################################################################
' Clean up
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close False
objExcel.DisplayAlerts = True
objExcel.Quit
0

There are 0 answers