Is there a way to keep microsoft excel app close on visual foxpro?

Question

I have problems with the Excel appand the program I tryin' to put together sends me a message that a document is been used.

In a form on Visual Foxpro 8, I collect some info from the tables of a db and then I assemble a worksheet with this elements. Lately i've been testin' this part of the program but it shows me the message after it's done assemble the worksheet that the document is still been used, and on the task manager I see that the app is still running even that in the procedure there's a line telling that the Excel sheet is released.

with thisform
   lcDir = "C:\Documents\PRGT\test.xls"

   ExcelSheet=createobject("Excel.application")
   ExcelSheet.APPLICATION.workbooks.ADD
   ExcelSheet.APPLICATION.activesheet.cells(1,1).value = "info1"
   ExcelSheet.APPLICATION.activesheet.cells(1,2).value = "info2"
   ExcelSheet.APPLICATION.activesheet.cells(1,3).value = "info3"
   ExcelSheet.APPLICATION.activesheet.cells(1,4).value = "info4"

   local lnRow
   store 0 to lnRow

   select cursorName
   go top
   do while !eof()
      ExcelSheet.Columns("A").Select
      ExcelSheet.Selection.value = cursorName.a
      ExcelSheet.Columns("B").Select
      ExcelSheet.Selection.value = cursorName.b
      ExcelSheet.Columns("C").Select
      ExcelSheet.Selection.value = cursorName.c
      ExcelSheet.Columns("D").Select
      ExcelSheet.Selection.value = cursorName.d
      lnRow = lnRow+1
      selelect cursorName
      skip
   enddo

   ExcelSheet.APPLICATION.activeworkbook.saveas(lcDir)
   ExcelSheet.APPLICATION.visible = .f.
   release ExcelSheet

  /*
  * some instruction to close the app
  */
endwith

Well, i found in a Visual forum that the next instruction work ExcelSheet.quit(.f.) and I tried that line and when I was testing the program it couldn't do the procedure of assemble the excel and savin' it.

Any ideas I can try will be welcome and also it would help me to learn better about this

Answer 1

At first no need to put your code in with thisform and endwith block as here you are not specifying properties of a form object.

The Excel file name extension prefered to be "xlsx" not "xls" if you are using a new version of Excel.

It's better to define lcDir as local as you did with the lnRow so you need to add local lcDir before store 0 to lnRow.

Regarding the loop through records it's better and easier to replace :

select cursorName
go top
do while !eof()
.
.
.
  select cursorName
  skip
enddo

By

select cursorName
scan
.
.
.
endscan

To export the records data to the Excel workbook rows, use the same method you used to export the header of the columns but change the row number for each record using the lnRow variable you already defined, but initiate it's value by 2 at first rather than 0 by replacing store 0 to lnRow by store 2 to lnRow

And replace :

  ExcelSheet.Columns("A").Select
  ExcelSheet.Selection.value = cursorName.a
  ExcelSheet.Columns("B").Select
  ExcelSheet.Selection.value = cursorName.b
  ExcelSheet.Columns("C").Select
  ExcelSheet.Selection.value = cursorName.c
  ExcelSheet.Columns("D").Select
  ExcelSheet.Selection.value = cursorName.d

By :

 ExcelSheet.APPLICATION.activesheet.cells(lnRow ,1).value = cursorName.a
 ExcelSheet.APPLICATION.activesheet.cells(lnRow ,2).value = cursorName.b
 ExcelSheet.APPLICATION.activesheet.cells(lnRow ,3).value = cursorName.c
 ExcelSheet.APPLICATION.activesheet.cells(lnRow ,4).value = cursorName.d

After saving the workbook you need only to quit the Excel as you stated by using ExcelSheet.quit and no need to make the Excel hidden, so no need for ExcelSheet.APPLICATION.visible = .f.

So the complete code will look like:

local lcDir
lcDir = "C:\Documents\PRGT\test.xlsx"

ExcelSheet=createobject("Excel.application")
ExcelSheet.APPLICATION.workbooks.ADD()
ExcelSheet.APPLICATION.activesheet.cells(1,1).value = "info1"
ExcelSheet.APPLICATION.activesheet.cells(1,2).value = "info2"
ExcelSheet.APPLICATION.activesheet.cells(1,3).value = "info3"
ExcelSheet.APPLICATION.activesheet.cells(1,4).value = "info4"

local lnRow
store 2 to lnRow

select cursorName
scan
   ExcelSheet.APPLICATION.activesheet.cells(lnRow ,1).value = cursorName.a
   ExcelSheet.APPLICATION.activesheet.cells(lnRow ,2).value = cursorName.b
   ExcelSheet.APPLICATION.activesheet.cells(lnRow ,3).value = cursorName.c
   ExcelSheet.APPLICATION.activesheet.cells(lnRow ,4).value = cursorName.d
   lnRow = lnRow+1
endscan  

ExcelSheet.APPLICATION.activeworkbook.saveas(lcDir)
ExcelSheet.quit()
release ExcelSheet