Powershell and Excel formulas

I have a script that exports raw data from a SQL DB and creates an excel file (with formatting). One of my tabs I have formulas that will show if a user has connected to the database in 30, 60, 90 days. The formula I used does not produce the correct data. SO, changed the formula. Up until now, I have simply copied the formula into the excel file after the scrips creates it. Well, it is time to modify the formula in the script. The script I am using (for simplicity sake, I will limit it to just the 30 days)

=IF(ISNA(VLOOKUP(A2,‘30Days’!A$2:‘30Days’!A$694,1,FALSE)),“No”,“Yes”)

I simply copy the formula below over the above formula in the excel file and it returns the data I need.

=IF(ISNUMBER(A2),IF(ISNA(VLOOKUP(A2,‘30Days’!A$2:‘30Days’!A$694,1,FALSE)),“No”,“Yes”),IF(COUNTIF(‘30Days’!A$2:A$676,““&A2&””),“Yes”,“No”)).

Now mind you the formula in the script has variables to account for rows counts . Below I will give you the powershell script for the current formula, as well as the formula I have tried to modify along with the error.

Current script snippet

$30DayVlookup = "=IF(ISNA(VLOOKUP(A2,30Days!A`$2:30Days!A`$$rows30,1,FALSE)),""No"",""Yes"")"

Modified script snippet that is failing with included error

$30DayVlookup = "=IF(ISNUMBER(A2),IF(ISNA(VLOOKUP(A2,30Days!A`$2:30Days!A`$$rows30,1,FALSE)),""No"",""Yes""),IF(COUNTIF('30Days'!A`$2:30Days!A`$$rows30,"*"&A2&"*"),""Yes"",""No""))"

The following is the Error I recieve. Line 260 is the modified script snippet. Any help will be appreciated.

Cannot convert value “&A2&” to type “System.Int32”. Error: “Input string was not in a correct format.”
At C:\Scripts\VDIUSage\VDIUsage_Report.ps1:260 char:1

  • $30DayVlookup = "=IF(ISNUMBER(A2),IF(ISNA(VLOOKUP(A2,30Days!A`$2:30Da …
  •   + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvalidCastFromStringToInteger

Without having any experience in working with Excel files from PowerShell … did you try to wrap the complete formula in single quotes instead of double quotes? This way it shouldn’t be necessary to escape the double quotes or dollar signs you have inside your formula.