0

I need to open an Excel-file, add a formular to the lastRow+1 in col "G" and then save it again. I tried:

Dim fso, Workbook, Sheet, lastRow, file

file="MyFile.xls"
Set fso = CreateObject("Scripting.FileSystemObject")
Set EXCEL = CreateObject("Excel.Application")
Set Workbook = EXCEL.Workbooks.Open(file)
Set Sheet = EXCEL.Application.ActiveSheet
lastRow = Sheet.Cells(Sheet.Rows.Count, 7).End(xlUp).Row -> "Unknown Runtime-error"
Range("G" + CStr(lastRow + 1)).Select
ActiveCell.Formula = "=SUM(G2:G" + CStr(lastRow) + ")"
Selection.Font.Bold = True      

Workbook.SaveAs file, "56"
Workbook.close

EXCEL.quit

The code to set the formular works greate inside an VBA-Excel-Macro, but simply doesn't when using VBS: I just get an "unknown runtime error in line 8 char 3, code 800A03EC" - which doesn't really help much to figure out what might be wrong.

Any ideas how to make this work in VBS with Excel 2013?

Werner
  • 1,695
  • 3
  • 21
  • 42
  • You can't use Excel VBA specific enumeration constants in a .VBS script because those same enumeration constants aren't defined in any VBS library. This just means you need to use the actual numeric value instead. So in this case, instead of `.End(xlUp).Row` it needs to be `.End(-4162).Row` – tigeravatar May 06 '19 at 17:45
  • You'll also be required to use full object qualification, so you can't just use `Range` by itself, you'll have to qualify it with `Sheet.Range..` and don't use `.Select` or `ActiveCell` or `Selection`. See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – tigeravatar May 06 '19 at 17:48
  • Thank you! I managed to set the formular now, but how do I set the cells in a Range to bold without select them? – Werner May 06 '19 at 18:59
  • `Range("G" + CStr(lastRow + 1)).Font.Bold = True` – tigeravatar May 06 '19 at 19:13

0 Answers0