Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Paul Gibson
  Where is Paul Gibson?
 United Kingdom
 Paul Gibson
 To: Cetin Basoz
  Where is Cetin Basoz?
 Cetin Basoz
Subject: RE: Excel Trust Center Incorrect Error
Thread ID: 373876 Message ID: 373901 # Views: 36 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Microsoft Office Automation
Date: Thursday, March 28, 2013 6:00:47 PM         

> Paul,
> I know that you wouldn't like this but why don't you simply start with getting the data into excel directly using VFPOLEDB recordset and simply saving in its default format. Even with 64 bits Excel this worked in 2010 and I believe it would with 2013 too (but I don't have it to test - I need to soon for our applications too). Here is a varied version of the famous VFP2Excel:
> PS: Again note that this one is usable with Office 64 bits and with VFP cursors (which could be saved to temp tables with or without a DBC or XML and still could be opened as a stream to pass - most of the time the cursors already come from existing tables, simply passing the SQL works).
#Define xlWorkbookNormal                                  -4143
> lcFileName = Forcepath( 'ExcelSaveAsTest.xlsx', Sys(2023) )
> Local oExcel
> oExcel = Createobject("Excel.Application")
> With m.oExcel
>   .DisplayAlerts = .F.
>   .Workbooks.Add
>   .Visible = .T.
>   * Send the data - copy to replacement
>   VFP2ExcelVariation(_samples+"Data", "select * from customer", .ActiveWorkBook.ActiveSheet.Range('A1'))
>   .ActiveWorkBook.ActiveSheet.Name = "MyVFPData"
>   .ActiveWorkBook.SaveAs(m.lcFileName,xlWorkbookNormal)
> Endwith
> Function VFP2ExcelVariation(tcDataSource, tcSQL, toRange, tcHeaders)
>   Local loStream As AdoDb.Stream,  loRS As AdoDb.Recordset,ix
>   loStream = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+m.tcDataSource, m.tcSQL)
>   loRS = CreateObject('Adodb.Recordset')
>   m.loRS.Open( loStream )
>   * 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


> Cetin Basoz
> .Net has got better.Think about moving - check my blog:
> My Blog

> MongoDb Certified Developer
> MongoDb Certified DBA

> Support Wikipedia

Thanks for that Cetin, I appreciate the code sample to demo a better way of exporting VFP data to Excel.

I'm sure you can appreciate why I'm not going to be able to apply that solution easily and why for the moment I might just have to file your sample for future development. In writing this application coders started exporting data to Excel by using COPY TO ... TYPE XLS in 1999 so for 13/14 years different coders have been doing that all over the application. It would be nice if back then someone had come up with a standard routine for exporting the data to Excel instead of re-writing it over and over again then I could change one routine rather than what I am faced with which is trying to fix 300+ calls to Excel automation using COPY TO with the TYPE XLS clause. Most of those 300+ will be subtly different meaning I can't just change them to exporting the data a completely different way when all I actually need to be able to do is to get Excel to be able to do what it's been doing successfully for many many years i.e. save the data.

The scientist in me (well, the logically thinking person in me) is being driven mad by the error message that contradicts what the software shows and that's what I'd like to get to the bottom of.

For future exports to Excel I will definitely consider your way of doing it as a reasonable alternative.



Excel Trust Center Incorrect Error Posted by Paul Gibson @ 3/28/2013 12:46:09 PM
RE: Excel Trust Center Incorrect Error Posted by Cetin Basoz @ 3/28/2013 2:41:52 PM
RE: Excel Trust Center Incorrect Error Posted by Paul Gibson @ 3/28/2013 6:00:47 PM
RE: Excel Trust Center Incorrect Error Posted by Anders Altberg @ 3/28/2013 7:58:00 PM
RE: Excel Trust Center Incorrect Error Posted by Paul Gibson @ 3/29/2013 10:02:56 AM