can i use this functions to save an excel file as xls ? 1

[Ernesto_Paez]

Hi Everyone,
i am using these two functions to pass the cursor data and create an excel file but the file created, is "xlsx type" and the person who is running the application still using Ms office 2010, so he is getting this error "OLE mismatch errors" However while running the same application on a machine having Ms office 2016 ,there is not problem excel open as XLSX.
any help is very well appreciated plus also the reason why is this happening ?
I don't know if this happening because of the code below, otherwise please excuse me.
Thanks a lot

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

 

[Scott24x7]

.xlsx came into being in Office 2007, so her version is probably not the issue. If at all possible, I'd suggest keeping it .xlsx as it will be more robust. And if you create it as .xls it may still not work.

That said, why not just use the VFP command COPY TO ?
Example:

COPY TO myexcel.xls (adoDB.Stream) TYPE XLS

You may have to play with the adoDB.Stream by creating it to some cursor first, or name, but this is the general idea.

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Your code is overcomplicated for the case to store xls

You'd create a VFP cursor instead of an ADO.Stream, and COPY TO excel.xls type XLS as Scott says.

And getting a cursor from a database is much simpler as getting an ADO.Stream, I actually assume you already know and did this:

Procedure GetDataAsCursor(tcConnection, tcSQL, tcAlias)
   Local lnHandle, lnError
   lnHandle = SQLStringConnect(tcConnection)
   lnError = SQLExec(lnHandle,tcSQL,tcAlias)
   SQLDisconnect(lnHandle)
EndProc

You'd do error handling, if lnError<0 creating an array of error information via AERROR(), but for sake of simplicity I show it that way, comparable to the code you have also not doing error handling.

Now you for example do:

GetDataAsCursor("Driver={SQL Server};Server=(local);Trusted_Connection=Yes;","Select* From Orders","crsOrders")
Copy TO orders.xls Type XL5

Bye, Olaf.

 

[Ernesto_Paez]

Hi Scott,
Thanks will give a try but what i am looking for is to create an excel file with two sheets, where one sheet, will display data from one cursor and the other sheet should display data from a different cursor, so the copy to in this case won't work, so some kind of automation is needed here, please correct me
Thanks

 

[Scott24x7]

Landfla,
Ah, that's not something you mentioned before (the two-sheet thing).
You could still do that by using separate COPY TO statements, and an "external" Excel file that updates from the two separate sheets.
Don't know what your main aim is, will they be static, will they get updated, will it populate/replace? That will guide what you do with it, but keep it simple is my suggestion.

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

The function you show is called from somewhere with an Excel Range object. That or even one or two levels up is where you'll find the Excel file creation and automation.
Also, what drivers you need for the adodb.connection depends on what connection string you're using, Office 2010 already is XLSX, too, but what's failing most likely is the connection due to usage of drivers of Office 2016 not available to 2010.

I would guess as you make use of OLE (AdoDB means OLE), you're using some variant of Microsoft.ACE.OLEDB.12.0, and may just need to turn down the version number here in the case of Office 2010.

Bye, Olaf.

 

[Ernesto_Paez]

Hi Guys,
well exactly the error i am getting from that machine running win 7 pro 64bit with Ms office 2010 is
"OLE Idispatch exception code 0 from ADODB.Connectionrovider cannot be found. it may not be properly installed.

so i will need to download either the same version 12.0 and install it in that machine with problems or a lower version ?

 

[Olaf Doschke]

Your connection string would be interesting, we already know you get an error connecting.

Office 2010 is an older version of office and comes with older versions of the provider. You can see a list of installed providers to use in your connection string, or look connection strings up for Excel 2010 at

https://www.connectionstrings.com

And if that provider is still missing you surely find a download.

Bye, Olaf.

 

[Ernesto_Paez]

Scott,
all i need, is to copy data from one cursor to a sheet in an Excel file and then copy data from another cursor to the second sheet on the same excel file, once copied, it is just to display the data there and maybe be able to print or sort or whatever, does not need to be populated later, the purposes is to send it there from the exe application to be able to print it or to rearrange later
i am just looking for to do this in case i don't find the correct ADODB.Connectionrovide
Thanks

 

[Ernesto_Paez]

Olaf,
Thanks a lot for the link, still looking for the correct one, but i think if install "VFPOLEDBSetup.msi" this will resolve the issue.
By the way Olaf, i found this searching and i think you did this long time ago, it is a good thing to learn automation as here you explain a few things to do depending of the case, very interesting

* Simple automation with Excel.  Just copy this faq into prg file and run it.

* include an EXCEL header file and reference values by name.
* #INCLUDE C:\MyProject\INCLUDE\xl5en32.h
* If you do not have a header file and need to create one.  Refer to FAQ:
* How to create office header files in VFP FAQ184-2749

* creates random numbers for quarterly data.
* adds some detail records

CREATE CURSOR curCompany (Company C(20), Qtr1 N(10,2), qtr2 N(10,2), qtr3 N(10,2), qtr4 N(10,2))
FOR lni = 1 TO 10
    APPEND BLANK 
    REPLACE curCompany.company WITH SYS(2015)
    REPLACE curCompany.qtr1 WITH 1 + 1000 * RAND( )
    REPLACE curCompany.qtr2 WITH 1 + 1000 * RAND( )
    REPLACE curCompany.qtr3 WITH 1 + 1000 * RAND( )
    REPLACE curCompany.qtr4 WITH 1 + 1000 * RAND( )
ENDFOR
    
    
* Excel: HorizontalAlignment 
* 2 = Left
* 3 = Center
* 4 = Right
    
local oExcel, oSheet
oExcel = CreateObject([Excel.Application])
oExcel.Visible = .T.
oExcel.Workbooks.Add()

oSheet = oExcel.ActiveSheet

lnRow = 0
SELECT curCompany
GO TOP
DO WHILE NOT EOF()
    lnRow = lnRow + 1
    IF lnRow = 1
        oSheet.Cells(lnRow,1).Value = [FoxPro Rocks!]
        
        lnRow = 3
        lnCol = 3
        oSheet.Range([C3]).Select
        oSheet.Cells(lnRow,lnCol).Value = [Qtr 1]
        oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
        
        *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
        oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3
        
        lnCol = lnCol + 1
        oSheet.Range([D3]).Select
        oSheet.Cells(lnRow,lnCol).Value = [Qtr 2]
        oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
        *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
        oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3
        
        lnCol = lnCol + 1
        oSheet.Range([E3]).Select
        oSheet.Cells(lnRow,lnCol).Value = [Qtr 3]
        oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
        *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
        oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3

        lnCol = lnCol + 1
        oSheet.Range([F3]).Select
        oSheet.Cells(lnRow,lnCol).Value = [Qtr 4]
        oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
        *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
        oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3
        
        lnRow = 4
        lnBeginRange = lnRow
    ENDIF
    
    oSheet.Cells(lnRow,1).Value = curCompany.Company 
    oSheet.Cells(lnRow,3).Value = curCompany.qtr1 
    oSheet.Cells(lnRow,4).Value = curCompany.qtr2 
    oSheet.Cells(lnRow,5).Value = curCompany.qtr3 
    oSheet.Cells(lnRow,6).Value = curCompany.qtr4 

    SKIP
ENDDO        

* Create the formula rather than hardcoding total so the user can 
* change the spreadsheet and it will reflect new totals.
* Example:  =SUM(D5:D10)
FOR lni = 1 TO 4
lcFormula = [=SUM(] + CHR(64 + lni) + ALLTRIM(STR(m.lnBeginRange)) + [:] +;
                CHR(64 + 3 + lni) + ALLTRIM(STR(m.lnRow)) + [)]
                

oSheet.Cells(lnRow+1,2+lni).Formula = [&lcFormula]
ENDFOR 




*****************************************************

*****************************************************

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION

* --- Set Excel to only have one worksheet ---
oExcel.SheetsInNewWorkbook = 1

* --- Delete the Default Workbook that has 3 worksheets ---
oExcel.Workbooks.CLOSE

* --- Now Add a new book with only 1 worksheet ---
oExcel.Workbooks.ADD
xlBook = oExcel.ActiveWorkbook.FULLNAME
xlSheet = oExcel.activesheet

* --- Name Worksheet ---
xlSheet.NAME = "Sheet Name"

* --- Make Excel Worksheet Visible To User ---
oExcel.VISIBLE = .T. && Set .F. if you want to print only

   <do whatever>

oExcel.WINDOWS(xlBook).ACTIVATE
xlSheet.RANGE([A2]).SELECT

* --- Save Excel Results ---
oExcel.CutCopyMode = .F. && Clear the clipboard from previous Excel Paste
oExcel.DisplayAlerts = .F.

* --- Save Results ---
xlSheet.SAVEAS(mcExclFName)

* --- Close the Worksheet ---
oExcel.workbooks.CLOSE

* --- Quit Excel ---
oExcel.QUIT
RELEASE oExcel

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION
oExcel.ReferenceStyle = 1  && Ensure Columns in A-B Format instead of 1-2 Format

mcStrtColRow = 'A1'
mcEndColRow = 'AB5'
mcLastCol = 'AZ:'

* --- Time Masquerading As Text Format Cells ---
xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "h:mm:ss"

* --- Standard Text Format Cells ---
xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "@"

* --- Date Format Cells ---
xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "mm/dd/yyyy"

* --- Auto-Fit All Columns ---
xlSheet.COLUMNS("A:" + mcLastCol).EntireColumn.AutoFit

 

[Olaf Doschke]

Well, what would be interesting to see is what you pass in as value of tcConnection in your previous code.

Because that works in conjunction with Office 2016, doesn't it? You just need the analog version for 2010.

The new code: I don't think that's by me, but it shows some other ways of putting data into sheets via much more direct automation of setting single cell values as in [tt]oSheet.Cells(lnRow,lnCol).Value = ...[/tt], it's something you might consider as fallback, but cumbersome to write out and will likely be the slowest way to store your data into an excel sheet.

If you go back other ways, then rather do as Scott already suggested, save two Excel files and then automate excel to load them into two sheets.

You can always record a macro in Excel to see how it's done in VBA and what objects of the Excel object model are involved.

Bye, Olaf.

 

[Ernesto_Paez]

Olaf,
I passed VFPOLEDB and it worked
Thanks a lot to all, but i will try to do the two excel file and then record a macro to put both sheets in one excel file