0

An application (a SCADA program) has an event that trigers every day and run a script that writes an Excel file (one for each day). The file could contain multiple sheets.

On Windows 7, with Office 2007 installed, I can write, but I can't save and neither quit the Excel.Application.

Dim objExcel as Object
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Add  'I think I shouldn't do this, but if not it doesn't work

Set sheet = objExcel.ActiveWorkBook.Worksheets.Add
'writing to the actual sheet...

objExcel.ActiveWorkBook.SaveAs path$
objExcel.Workbooks.Close
objExcel.Quit

If I run the script manually (from the script editor):

  • it saves
  • the Excel process still runing
  • When I open the Excel file (not from script), this has 2 workbooks, the actual and the last one from the last execution.

If I try trigger the event:

  • error occurs on the line when it saves
user692942
  • 16,398
  • 7
  • 76
  • 175
Anatoli V.
  • 11
  • 1
  • 4
  • 1
    What is the error message? – bdn02 Jan 26 '16 at 09:05
  • System.IO.FileNotFoundException: Could not load file or assembly 'file:///C:\Wi – Anatoli V. Jan 26 '16 at 09:16
  • "The Basic Control Engine combines the power of the CIMPLICITY event handler with a Visual Basic™ compliant language, allowing you to script and program applications and routines from the simple to the complex." whatever... – Anatoli V. Jan 26 '16 at 11:04
  • I'm asking which would be the steps to: create & write a new excel, with multiple sheets, save it and close it. – Anatoli V. Jan 26 '16 at 11:07
  • @AnsgarWiechers It's VBA, from [the manual](http://platforma.astor.com.pl/files/getfile/id/4671) - *"The Program Editor utilizes an embedded Basic language that is syntactically compatible with **Microsoft’s Visual Basic for Applications™**. This language provides the rich Basic command set, in addition to CIMPLICITY software specific extensions."* – user692942 Jan 26 '16 at 14:49
  • @Lankymart Thanks for the clarification. – Ansgar Wiechers Jan 26 '16 at 15:35
  • @AnsgarWiechers Weird the error they posted is .Net though, must use VBA as a sub-system, either that or the codebase has changed. – user692942 Jan 26 '16 at 15:59

1 Answers1

1

Solved. 

Dim objExcel as Object
Dim sheet as Object
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Add

Set sheet = objExcel.ActiveWorkBook.Worksheets.Add
'writing to the actual sheet...

path$ = "path/must/use/slash/insteed/of/backslash"

objExcel.ActiveWorkBook.SaveAs path$
objExcel.ActiveWorkBook.Close
objExcel.Quit  
Set objExcel = Nothing

So the problem was the path$ and I forgot to put Set objExcel = Nothing. Thats all.

Anatoli V.
  • 11
  • 1
  • 4