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


> So far my only realistic solution is to - I'm ruling out anything that would require me to drastically change how we output the data initially:
>
> 1. Relax the Settings in the Trust Center to allow full access to v2 Excel files.
> 2. COPY TO ... TYPE XLS (but could do XL5 if I decide it'll be an improvement and potentially reduce the need for step #1)
> 3. Instantiate Excel as loExcel
> 4. Open the v2 (or v5) file
> 5. Do whatever manipulation is required
> 6. loExcel.Application.DisplayAlerts = .F.
> 7. loExcel.ActiveWorkbook.SaveAs(&newfile2) - IMPORTANTLY newfile2 is a file path variable with xlsx as the extension, not xls and removing the file type parameter will tell Excel to save the file in it's default format. This isn't great there are still plenty of other caveats and things to do like deleting the XLS file so there aren't two copies.
> 8. loExcel.Application.DisplayAlerts = .T.
> 9. loExcel.Quit() or loExcel.Visible = .T.
>
> That should get me through this problem but that's a hell of a lot of work to change all the points of automation in this application (hundreds - and not really a change I can apply with Code References or GoFish's help). If only Excel 2013 knew it's arse from it's elbow and could see that the File Block settings aren't blocking any file types (Whoops! little bit of frustration crept in there).
>
> Anyone got any ideas?

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

ENTIRE THREAD

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