A different take on exporting to Excel

[brigmar]

I've tried all sorts of methods to export DBF files to Excel, to work with large datasets and include memo fields, and I've recently worked on another way of exporting: Using Excel 2007's own 'Import Data' functionality.

So the process is :
1. Create DBF file
2. Automate Excel to Import that DBF

*-----------------------------------------------------------------------------------------
cFullName = "Q:\directory\export.dbf" && DBF File to 'export' to excel
*-----------------------------------------------------------------------------------------
cDataSource = JUSTPATH( m.cFullName )
cFileStem   = JUSTSTEM( m.cFullName )
lcTimeStamp = RIGHT(DTOC(DATE(),1),4)+LEFT(DTOC(DATE(),1),4)
cSaveAs     = ADDBS( m.cDataSource ) + "Data Extract "+m.lcTimeStamp
lcConnStr   = TEXTMERGE([OLEDB;Provider=VFPOLEDB.1;Data Source="<<m.cDataSource>>";Mode=Share Deny None;Extended Properties="";] ;
            + [User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;] ;
            + [DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;] ;
            + [ANSI=True;REPROCESS=5] )
odcFile     = "C:\temp.odc"
odcName     = JUSTSTEM(m.odcFile)
*-----------------------------------------------------------------------------------------
oXl = CREATEOBJECT("Excel.Application.12") && Multiple copies of Excel on workstation. 
WITH oXl
  .DefaultSaveFormat = 50 && xlExcel12 : prevents 65536 max if excel configured to create .xls by default
  oWb = .WorkBooks.Add 
  oSh = .ActiveSheet
  WITH osh
    .Name = "Data"
    WITH .ListObjects.Add(0, m.lcConnStr,,, oSh.Range("$A$1")).QueryTable
      .CommandType            = 3 && xlCmdTable
      .CommandText            = TEXTMERGE([`<<m.cFileStem>>`])
      .RowNumbers             = .F.
      .FillAdjacentFormulas   = .F.
      .PreserveFormatting     = .T.
      .RefreshOnFileOpen      = .F.
      .BackgroundQuery        = .T.
      .RefreshStyle           = 1 && xlInsertDeleteCells
      .SavePassword           = .F.
      .SaveData               = .T.
      .AdjustColumnWidth      = .T.
      .RefreshPeriod          = 0
      .PreserveColumnInfo     = .T.
      .SourceConnectionFile   = m.odcFile
      .ListObject.DisplayName = m.odcName
      .Refresh(0) && BackgroundQuery:=False 
    ENDWITH
    .ListObjects(m.odcName).Unlink
  ENDWITH
  .DisplayAlerts = .F.
  oWb.SaveAs( m.cSaveAs, 50 ) && Client wanted .xlsb, change to suit
  .DisplayAlerts = .T.
  .Quit
ENDWITH
*-----------------------------------------------------------------------------------------

(Record Macro is my friend)

This produces a formatted sheet with autofilters on all fields, and rows with alternating background colours.

Has anybody else come to end up using this functionality? What problems have you encountered ?

My main issue was that the listobject itself prevented me from further formatting, but my workaround was creating a second worksheet, copying the data section from the first sheet (rows 2-x), using .PasteSpecial() to copy only the values to the new sheet, and then removing the first sheet.

 

[Olaf Doschke]

That has a new turn for me, using .ListObjects. Using the VFPOLEDB.1 OLE DB Provider is not a new idea, though. That was shown by Cetin Basoz and got part of foxyclasses, if I interpret my google searches of "dbf2excel Cetin Basoz" correctly.

Bye, Olaf.