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

Answer 2

I couldn't understand some parts of your code, like:

lnRow = lnRow + 1 && not used at all

and selecting the Columns("A"). Why would you select the column? Wouldn't it be a Cell?

Though the way you are doing it is not suggested at all, to make it work correctly you could write as:

#Define xlWorkbookNormal                                  -4143
LOCAL lcDir
lcDir = "C:\Documents\PRGT\test.xls"

Local excelsheet
excelsheet=Createobject("Excel.application")
With excelsheet
    .DisplayAlerts = .F.
    .workbooks.Add()
    With .ActiveWorkBook.ActiveSheet
        .cells(1,1).Value = "info1"
        .cells(1,2).Value = "info2"
        .cells(1,3).Value = "info3"
        .cells(1,4).Value = "info4"

        Select cursorName
        Scan
            .cells(Recno()+1,1).Value = cursorName.a
            .cells(Recno()+1,2).Value = cursorName.b
            .cells(Recno()+1,3).Value = cursorName.c
            .cells(Recno()+1,4).Value = cursorName.d
        Endscan
    Endwith
    .ActiveWorkBook.SaveAs(m.lcDir,xlWorkbookNormal)
    .ActiveWorkBook.Saved = .T.
    .Quit()
Endwith

In your code, you were setting excelSheet (which is the same as excelSheet.Application) .Visible to .F. and releasing ExcelSheet.

Setting it to Visible = .F. and releasing only the variable doesn't help, Excel instance would be left behind hidden with a lock on the document you created. You shouldn't set it to Visible=.F. but simply call Quit() method. Then also using your ExcelSheet variable local would release it by default, going out of scope.

After having said that, proper way of copying data to excel is not like this and this would be the slowest way to do that (don't try with lots of rows and columns data).

Instead you could send the data to excel using OLEDB and just use CopyFromRecordset method (using one of the variations of VFP2Excel routine that I have posted many times to many forums). ie: This sample sends 2 cursors to excel, to apply it to yourself all you need is to create your cursor(s) and set the laInfo[] accordingly to pass to "SaveCursorsToExcel":

* Our sample cursor(s)
Select Cust_Id, Company, Contact, Country From (_samples + 'data\Customer') Into Cursor crsCustomers nofilter
Select Order_Id, Cust_Id, Order_Date, Shipped_on From (_samples + 'data\Orders') Into Cursor crsOrders nofilter

Local Array laInfo[2, 3]
laInfo[1,1] = 'crsCustomers'
laInfo[1,2] = 'Customer Id,Company,Contact Name,Country' && Headers
laInfo[1,3] = 'Customers' && Sheet name

laInfo[2,1] = 'crsOrders'
laInfo[2,2] = 'Order Id, Customer Id, Order Date, Shipped On'
laInfo[2,3] = 'Orders'


Local lcSaveFilename
lcSaveFilename = "c:\temp\SampleExcelFile.xlsx"

SaveCursorsToExcel(@laInfo,  m.lcSaveFilename)



Procedure SaveCursorsToExcel(taInfo, tcSaveAs)
    External Array taInfo
    
    *** We need real tables on disk to get them via VFPOLEDB
    *** Assuming that there may be LFN in cursor data we create a temp DBC too

    Local lcDbc, ix
    lcDbc = Forcepath( Forceext( Sys(2015), 'dbc'), Sys(2023))

    ** Create the temp dbc
    Create Database (m.lcDbc)
    ** and set it as the default database
    Set Database To (m.lcDbc)
    ** and create tables from cursors as part of this new dbc


    *** We could send many cursors to Excel at one go.
    *** taInfo passes the information of cursornames to send and their headers
    Local Array laTableNames[ALEN(taInfo,1)]
    For ix = 1 To Alen(laTableNames)
        laTableNames[m.ix] = Forcepath( Forceext(Sys(2015), 'dbf'), Sys(2023))

        Select * From (taInfo[m.ix,1]) Into Table (m.laTableNames[m.ix]) Database (m.lcDbc)

        Use In (Select(Juststem(m.laTableNames[m.ix])))
    Endfor
    Close Database

    ** Ready for sending the data to excel
    ** We also assume that the Excel on this machine could be a 64 bit version
    ** thus we don't do a direct VFPOLEDB transfer but wrap it in a ADODB.Stream
    ** We could as well use an ADODB.RecordSet

    Local Array laStream[ALEN(taInfo,1)]
    Local ix
    For ix = 1 To Alen(taInfo,1)
        laStream[m.ix] = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+m.lcDbc, Textmerge("select * from ('<< m.laTableNames[m.ix] >>')"))
    Endfor

    *** Now that we have the data in streams, we can get rid of the temp database and tables
    Local lcSafety
    lcSafety = Set("Safety")
    Set Safety Off
    Delete Database (m.lcDbc) Deletetables
    Set Safety &lcSafety


    *** Main Excel automation part now
    oExcel = Createobject("Excel.Application")
    With oExcel
        .DisplayAlerts = .F.
        .Workbooks.Add
        *  .Visible = .T.
        With .ActiveWorkBook
            For ix = 1 To Alen(taInfo,1)
                If .sheets.Count < m.ix
                    .sheets.Add(,.sheets(.sheets.Count)) && Add new sheet
                Endif
                .WorkSheets(m.ix).Name = taInfo[m.ix,3]

                * Send the data - copy to replacement
                VFP2ExcelVariation(m.laStream[m.ix], .WorkSheets[m.ix].Range("A1"), taInfo[m.ix,2])

                .WorkSheets(m.ix).Columns.AutoFit()

            Endfor
            .WorkSheets(1).Activate
        Endwith
        
        .ActiveWorkBook.SaveAs(m.tcSaveAs)
        .ActiveWorkBook.Saved = .T.
        .Quit()
    Endwith
ENDPROC


Function VFP2ExcelVariation(toStream, toRange, tcHeaders)
    Local loRS As AdoDb.Recordset,ix
    loRS = Createobject('Adodb.Recordset')
    m.loRS.Open( m.toStream )
    * Use first row for headers
    Local Array aHeader[1]
    m.toRange.Offset(1,0).CopyFromRecordSet( m.loRS )  && Copy data starting from headerrow + 1
    For ix=1 To Iif( !Empty(m.tcHeaders), ;
            ALINES(aHeader, m.tcHeaders,1,','), ;
            m.loRS.Fields.Count )
        m.toRange.Offset(0,m.ix-1).Value = ;
            Iif( !Empty(m.tcHeaders), ;
            aHeader[m.ix], ;
            Proper(m.loRS.Fields(m.ix-1).Name) )
        m.toRange.Offset(0,m.ix-1).Font.Bold = .T.
    Endfor
    m.loRS.Close()
Endfunc

Procedure GetDataAsAdoStream(tcConnection, tcSQL)
    Local loStream As 'AdoDb.Stream', ;
        loConn As 'AdoDb.Connection', ;
        loRS As 'AdoDb.Recordset'
    loStream = Createobject('AdoDb.Stream')
    loConn = Createobject("Adodb.connection")
    loConn.ConnectionString = m.tcConnection
    m.loConn.Open()
    loRS = loConn.Execute(m.tcSQL)
    m.loRS.Save( loStream )
    m.loRS.Close
    m.loConn.Close
    Return m.loStream
Endproc