Dbf to Excel Documentation

[Ted]

Hello Group,

I need to convert some dbf files into Excel worksheets. I've looked
at a number to threads and can see that it can be done using xHarbour,
but I can't find anything about it in the online documentation. Can
anyone where I can find documentation?

Thanks,

Ted

[Joe Wright]

Post by Ted
Hello Group,
I need to convert some dbf files into Excel worksheets. I've looked
at a number to threads and can see that it can be done using xHarbour,
but I can't find anything about it in the online documentation. Can
anyone where I can find documentation?

You might create a .CSV file which can be imported into Excel as a
native format.

[Frandalf]

This post might be inappropriate. Click to display it.

[Ath]

Post by Frandalf

Post by Ted
Hello Group,
I need to convert some dbf files into Excel worksheets. I've looked
at a number to threads and can see that it can be done using xHarbour,
but I can't find anything about it in the online documentation. Can
anyone where I can find documentation?
Thanks,
Ted

Ted,
just changing the extention from .DBF to .XLS will allow Excel to open
it as a worksheet.
But I guess you need more control during the conversion.
You need OLE tricks then and that's beyond me, but it's possible in
Xharbour.
Frank

If you have a one-off conversion need, then just do it by hand, as
mentioned before.

if it's a regular or continues job, the OLE way is the best solution,
IMHO. Follow the lease Enrico has given several times before, he
appears to be an expert (appreciated!) on the subject ;-)
Documentation of Excel methods to be called through OLE is not an
xHarbour issue, at most the OLE instantiation, but the OLE stuff for
Excel are MS docs. If you ave Office installed, the best info can
usually be found in {pf}\Microsoft Office\1043\VBA*.CHM files, or
search MSDN for the details.

HTH
Ath

[Gejza Horvath]

Ted,
on the MSDN here can you find details about the Excel OLE interface:
http://msdn2.microsoft.com/en-us/library/aa294250(office.10).aspx

and another usefull link about the Excel OLE object terminating error:
http://support.microsoft.com/kb/266088.

and here is a sample code from my app written in Xailer:
******************************************************
METHOD MachineList2Excel( oSender, oMenu ) CLASS TForm1
LOCAL oExcel,i,cFileName
LOCAL oWorkBook, oSheet,aRecord,aExport

* ::oDbfZlozenie:SaveState(.T.)
::oDbfZlozenie:GoTop()
/* IF EMPTY(cFileName := ::UlozSubor())
RETURN Nil
ENDIF
*/
oExcel := CREATEOBJECT( "Excel.Application" )
IF ::lCreateNew
oWorkBook := oExcel:WorkBooks:Add( )

ELSE
oExcel:WorkBooks:Open(::oFileOpenDlg:cFullFileName)
oExcel:ActiveWorkbook:Sheets:Add() // Add new sheet
ENDIF
oExcel:ActiveSheet:Name := ALLTRIM(::oDbfZlozenie:Cislopre) // attach a name to
the sheet
oExcel:Visible := .T.

aExport := {}
WITH OBJECT oSheet := oExcel:ActiveSheet()
* Header
:Range(:Cells(1,1),:Cells(1,6)):Value := {"Item no","Machine type",;
"Machine name","Qty","Finished","Serial No"}

IF ::lCreateNew // Need new file
i := 1
ELSE // Add as new line
i := :UsedRange:Rows:Count()
ENDIF

WITH OBJECT ::oDbfZlozenie
DO WHILE !:Eof()
i++
aRecord := {}
AADD(aRecord,:Cislopre)
AADD(aRecord,:Stroj_Typ)
AADD(aRecord,:Stroj_Naz)
AADD(aRecord,:Max_Pocet)
AADD(aRecord,:Ukoncene)
AADD(aRecord,:Ser_Cislo)
oSheet:Range(oSheet:Cells(i,1),oSheet:Cells(i,6)):Value := aRecord

AADD(aExport,aRecord)
// Adding a whole line in one step is much faster
:Skip()
ENDDO

* oSheet:Range("B1"):Resize(i,6):Value := aExport
END WITH
* ::oDbfZlozenie:RestoreState(.T.)
END OBJECT
* oWorkBook:SaveAs(cFileName)
* oWorkBook:Saved := .T.
* oWorkBook:Close(.T.)
* oExcel:UserControl := .F.
* oExcel:Visible := .F.
* oExcel:Quit()
IF !::lCreateNew

ELSE
oSheet := Nil
oWorkBook := Nil
oExcel := Nil
ENDIF
* RUN "KillProcess.exe Excel.exe >NUL"
* ShellExecute(,"open","KillProcess.exe","Excel.exe",,SW_SHOWMINIMIZED)


::lCreateNew := .T.
RETURN Nil
**********************************************


P.S.
I had a problem, becouse after oExcel:Quit() Excel remains in process list. See the link
above, how to avoid this situation.


Gejza Horvath

[Randy Howe]

Post by Ted
I need to convert some dbf files into Excel worksheets. I've looked
at a number to threads and can see that it can be done using xHarbour,
but I can't find anything about it in the online documentation. Can
anyone where I can find documentation?

Ted, the following is a nice little conversion program I wrote entirely in
xHarbour.
If you copy from this post into your editor you may have to fix a couple of
lines
due to line wrapping, but I think the end result will please you.
- Randy Howe, Softcare Software, Inc.

// DBF2EXCL Example by Randy Howe, Softcare Software, Inc.
// The following #Defines were borrowed from Alaska Software's Xbase++
#DEFINE xlNone -4142
#DEFINE xlContinuous 1
#DEFINE xlMedium -4138
#DEFINE xlAutomatic -4105
#DEFINE xlWorkbookNormal -4143
#DEFINE xlLandscape 2
#DEFINE xlPortrait 1
#DEFINE xlBottom -4107
#DEFINE xlLeft -4131
#DEFINE xlRight -4152
#DEFINE xlTop -4160
//XlBordersIndex
#DEFINE xlInsideHorizontal 12
#DEFINE xlInsideVertical 11
#DEFINE xlDiagonalDown 5
#DEFINE xlDiagonalUp 6
#DEFINE xlEdgeBottom 9
#DEFINE xlEdgeLeft 7
#DEFINE xlEdgeRight 10
#DEFINE xlEdgeTop 8


FUNCTION MAIN(cDbfFile,cREPTDESC)

LOCAL nSaveArea := Select()
LOCAL oExcel, oBook, oSheet, lGO_ON := .T.
LOCAL nRow, xarray, CAT, nCounter, farray, fvalue, cThisdir
LOCAL SEEKKEY, aLETTERS, nxCells
PRIVATE aCONFIG

// ALTD(1)
// ALTD()

SETCOLOR('W+/B')
// ('N*/W') // 'W*/W'
CLS

nxCells := 0
IF cDbfFile == NIL
// @ 24,0 Say ""
? "Useage: DBF2EXCL <cDbfFile> <DESCRIPTION>"
QUIT
ELSE
cDbfFile := UPPER(cDbfFile)
? "Building an Excel Spreadsheet from a DBF File."
? "Dbf File: "+cDbfFile
? "Excel File: "+STRTRAN(cDbfFile,".DBF",".XLS")
ENDIF

IF cREPTDESC == NIL
cREPTDESC := cDbfFile
ENDIF

// aCONFIG := GetConfig()
// 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
aLETTERS :=
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ","BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ"}

cThisdir := curdrive()+":\"+curdir()

IF FILE(cDbfFile) .AND. lGO_ON
// Create the "Excel.Application" object
// oExcel := CreateObject("Excel.Application")
TRY
oExcel := GetActiveObject( "Excel.Application" )
CATCH
TRY
oExcel := CreateObject( "Excel.Application" )
CATCH
Alert( "ERROR! Excel not available!")
RETURN
END
END
? "."
? "."

// Avoid message boxes such as "File already exists". Also,
// ensure the Excel application is visible.
oExcel:DisplayAlerts := .F.
oExcel:visible := .F.

// Add a workbook to the Excel application. Query for
// the active sheet (sheet-1) and set up page/paper
// orientation.
oBook := oExcel:workbooks:Add()
oSheet := oBook:ActiveSheet
oSheet:PageSetup:Orientation := xlLandscape

USE (cDbfFile) NEW ALIAS EXCEL

nRow := 5

FOR nCounter := 1 to FCOUNT()
oSheet:Cells(nRow,nCounter):Value := FIELDNAME(nCounter)
oSheet:Cells(nRow,nCounter):font:name := "Arial"
oSheet:Cells(nRow,nCounter):font:size := 12
oSheet:Cells(nRow,nCounter):font:bold := .T.
oSheet:Columns( nCounter ):AutoFit()
DO CASE
CASE VALTYPE(fieldget(nCounter)) = "N"
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):NumberFormat := "0.00"
oSheet:Columns( nCounter ):HorizontalAlignment = xlRight
CASE VALTYPE(fieldget(nCounter)) = "D"
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):NumberFormat := "mm/dd/yyyy;@"
oSheet:Columns( nCounter ):HorizontalAlignment = xlLeft
CASE VALTYPE(fieldget(nCounter)) = "L"
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):NumberFormat := "@"
oSheet:Columns( nCounter ):HorizontalAlignment = xlRight
OTHERWISE
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):NumberFormat := "@"
oSheet:Columns( nCounter ):HorizontalAlignment = xlLeft
ENDCASE
Next nCounter

oSheet:Cells(1,1):Value := "Please Wait"
oSheet:Cells(2,1):Value := "Populating"
oSheet:Cells(3,1):Value := "Cells ..."
oSheet:range("A1:A3"):font:name := "Arial"
oSheet:range("A1:A3"):font:size := 10
oSheet:range("A1:A3"):font:bold := .T.

? "Please Wait for: "+TRANSF(EXCEL->( LASTREC() ),'9999999')+" Total
Records to convert."
? "."

// 01234567890123456789012
@ 10, 0 SAY "Rows: nCells: "

nRow := 6
xarray := {}
SELECT EXCEL
DO WHILE !EOF()
farray := {}
farray := ARRAY(FCOUNT())
FOR nCounter := 1 TO FCOUNT()
// oSheet:Cells(nRow,nCounter):Value := fieldget(nCounter)
// oSheet:Cells(nRow,nCounter):font:name := "Arial"
// oSheet:Cells(nRow,nCounter):font:size := 10
fvalue := fieldget(nCounter)
IF VALTYPE(fvalue) = "L"
fvalue := IIF(fvalue,"YES"," NO")
farray[nCounter] := fvalue
ELSE
IF !EMPTY(fvalue)
farray[nCounter] := fvalue
ELSE
farray[nCounter] := SPACE(1)
ENDIF
ENDIF

nxCells++
@ 10, 6 SAY TRANSF(RECNO(),'999999') COLOR 'N/GR*'
@ 10, 21 SAY TRANSF(nxCells,'9999999') COLOR 'N/GR*'

oSheet:Cells(nRow,nCounter):Value := fValue
NEXT nCounter
// AADD(xarray, farray)
SKIP
nRow++
// oSheet:Cells(nRow,nCounter):Select()
ENDDO

EXCEL->( DBCLOSEAREA() )
@ 12, 0 SAY "Almost Done..."
? "."
? "."

oExcel:visible := .T.

oSheet:Range("A1:B1"):Select()

CAT := "A6:"+aLETTERS[LEN(farray)]+LTRIM(TRANSF(LEN(xarray)+5,'999999'))
// oSheet:range(CAT):value := xarray
// The above method works in Alaska's Xbase++ but not here for some
reason.

oSheet:Cells:Select()
oSheet:range(CAT):font:name := "Arial"
oSheet:range(CAT):font:size := 10
oSheet:Cells:EntireColumn:AutoFit()

oSheet:Range("A1"):Select()


nRow := 1
oSheet:Cells(nRow,1):Value := cREPTDESC
oSheet:Cells(nRow,1):font:name := "Arial"
oSheet:Cells(nRow,1):font:size := 14
oSheet:Cells(nRow,1):font:bold := .T.
oSheet:Cells(nRow,1):font:ColorIndex := 3 // RED

nRow := 2
oSheet:Cells(nRow,1):Value := "DBF to Excel" // change this to anything
you want
oSheet:Cells(nRow,1):font:name := "Arial"
oSheet:Cells(nRow,1):font:size := 14
oSheet:Cells(nRow,1):font:bold := .T.
oSheet:Cells(nRow,1):font:ColorIndex := 3 // RED

nRow := 3
oSheet:Cells(nRow,1):Value := "Date: "+DTOC(DATE())
oSheet:Cells(nRow,1):font:name := "Arial"
oSheet:Cells(nRow,1):font:size := 14
oSheet:Cells(nRow,1):font:bold := .T.
oSheet:Cells(nRow,1):font:ColorIndex := 3 // RED

oSheet:Range("A1:A3"):Select()

oSheet:Cells(1,1):NumberFormat := "General"
oSheet:Cells(1,1):HorizontalAlignment = xlLeft
oSheet:Cells(2,1):NumberFormat := "General"
oSheet:Cells(2,1):HorizontalAlignment = xlLeft
oSheet:Cells(3,1):NumberFormat := "General"
oSheet:Cells(3,1):HorizontalAlignment = xlLeft

CAT := "A1:"+aLETTERS[len(farray)]+"3"
oSheet:Range(CAT):Select()
oSheet:Range(CAT):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(1,1):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(2,1):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(3,1):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(1,2):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(2,2):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(3,2):Interior:ColorIndex := 42 // CYAN

CAT := "A5:"+aLETTERS[len(farray)]+"5"
oSheet:Range(CAT):Select()

oSheet:Range(CAT):Borders(xlDiagonalDown):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlDiagonalUp):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlEdgeLeft):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlEdgeTop):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlEdgeBottom):LineStyle := xlContinuous
oSheet:Range(CAT):Borders(xlEdgeBottom):Weight = xlMedium
oSheet:Range(CAT):Borders(xlEdgeBottom):ColorIndex = xlAutomatic
oSheet:Range(CAT):Borders(xlEdgeRight):LineStyle = xlNone
oSheet:Range(CAT):Borders(xlInsideVertical):LineStyle = xlNone



CAT := "A1:"+aLetters[len(farray)]+LTRIM(TRANSF(LEN(xarray)+5,'999999'))
oSheet:Range(CAT):Select()
oSheet:PageSetup:PrintArea :=
"$A$1:$"+aLetters[len(farray)]+"$"+LTRIM(TRANSF(LEN(xarray)+5,'999999'))
oSheet:PageSetup:PrintTitleRows := "$1:$5"
oSheet:PageSetup:RightHeader := " Page #&P of &N"
oSheet:PageSetup:LeftMargin := 0.50
oSheet:PageSetup:RightMargin := 0.25
oSheet:PageSetup:TopMargin := 0.75
oSheet:PageSetup:BottomMargin := 0.50
oSheet:PageSetup:HeaderMargin := 0.50
oSheet:PageSetup:FooterMargin := 0.25

oSheet:Range("A4"):Select()

cDbfFile := RTRIM(cThisdir)+"\"+STRTRAN(cDbfFile,".DBF",".XLS")

oBook:SaveAs(cDbfFile,xlWorkbookNormal)

// ALTD()


// Quit Excel
// oExcel:Quit()

oExcel:visible := .F.

? "."
@ 20,0 Say ""
? 'Your Excel File was Saved at: '+cDbfFile

INKEY(3)

?

oExcel:visible := .T.

ENDIF

CLOSE DATABASES

RETURN NIL

[Ted]

Post by Randy Howe

Post by Ted
I need to convert some dbf files into Excel worksheets. I've looked
at a number to threads and can see that it can be done using xHarbour,
but I can't find anything about it in the online documentation. Can
anyone where I can find documentation?

Ted, the following is a nice little conversion program I wrote entirely in
xHarbour.
If you copy from this post into your editor you may have to fix a couple of
lines
due to line wrapping, but I think the end result will please you.
- Randy Howe, Softcare Software, Inc.
// DBF2EXCL Example by Randy Howe, Softcare Software, Inc.
// The following #Defines were borrowed from Alaska Software's Xbase++
#DEFINE xlNone -4142
#DEFINE xlContinuous 1
#DEFINE xlMedium -4138
#DEFINE xlAutomatic -4105
#DEFINE xlWorkbookNormal -4143
#DEFINE xlLandscape 2
#DEFINE xlPortrait 1
#DEFINE xlBottom -4107
#DEFINE xlLeft -4131
#DEFINE xlRight -4152
#DEFINE xlTop -4160
//XlBordersIndex
#DEFINE xlInsideHorizontal 12
#DEFINE xlInsideVertical 11
#DEFINE xlDiagonalDown 5
#DEFINE xlDiagonalUp 6
#DEFINE xlEdgeBottom 9
#DEFINE xlEdgeLeft 7
#DEFINE xlEdgeRight 10
#DEFINE xlEdgeTop 8
FUNCTION MAIN(cDbfFile,cREPTDESC)
LOCAL nSaveArea := Select()
LOCAL oExcel, oBook, oSheet, lGO_ON := .T.
LOCAL nRow, xarray, CAT, nCounter, farray, fvalue, cThisdir
LOCAL SEEKKEY, aLETTERS, nxCells
PRIVATE aCONFIG
// ALTD(1)
// ALTD()
SETCOLOR('W+/B')
// ('N*/W') // 'W*/W'
CLS
nxCells := 0
IF cDbfFile == NIL
? "Useage: DBF2EXCL <cDbfFile> <DESCRIPTION>"
QUIT
ELSE
cDbfFile := UPPER(cDbfFile)
? "Building an Excel Spreadsheet from a DBF File."
? "Dbf File: "+cDbfFile
? "Excel File: "+STRTRAN(cDbfFile,".DBF",".XLS")
ENDIF
IF cREPTDESC == NIL
cREPTDESC := cDbfFile
ENDIF
// aCONFIG := GetConfig()
// 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
aLETTERS :=
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ","BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ"}
cThisdir := curdrive()+":\"+curdir()
IF FILE(cDbfFile) .AND. lGO_ON
// Create the "Excel.Application" object
// oExcel := CreateObject("Excel.Application")
TRY
oExcel := GetActiveObject( "Excel.Application" )
CATCH
TRY
oExcel := CreateObject( "Excel.Application" )
CATCH
Alert( "ERROR! Excel not available!")
RETURN
END
END
? "."
? "."
// Avoid message boxes such as "File already exists". Also,
// ensure the Excel application is visible.
oExcel:DisplayAlerts := .F.
oExcel:visible := .F.
// Add a workbook to the Excel application. Query for
// the active sheet (sheet-1) and set up page/paper
// orientation.
oBook := oExcel:workbooks:Add()
oSheet := oBook:ActiveSheet
oSheet:PageSetup:Orientation := xlLandscape
USE (cDbfFile) NEW ALIAS EXCEL
nRow := 5
FOR nCounter := 1 to FCOUNT()
oSheet:Cells(nRow,nCounter):Value := FIELDNAME(nCounter)
oSheet:Cells(nRow,nCounter):font:name := "Arial"
oSheet:Cells(nRow,nCounter):font:size := 12
oSheet:Cells(nRow,nCounter):font:bold := .T.
oSheet:Columns( nCounter ):AutoFit()
DO CASE
CASE VALTYPE(fieldget(nCounter)) = "N"
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):NumberFormat := "0.00"
oSheet:Columns( nCounter ):HorizontalAlignment = xlRight
CASE VALTYPE(fieldget(nCounter)) = "D"
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):HorizontalAlignment = xlLeft
CASE VALTYPE(fieldget(nCounter)) = "L"
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):HorizontalAlignment = xlRight
OTHERWISE
oSheet:Columns( nCounter ):Select()
oSheet:Columns( nCounter ):HorizontalAlignment = xlLeft
ENDCASE
Next nCounter
oSheet:Cells(1,1):Value := "Please Wait"
oSheet:Cells(2,1):Value := "Populating"
oSheet:Cells(3,1):Value := "Cells ..."
oSheet:range("A1:A3"):font:name := "Arial"
oSheet:range("A1:A3"):font:size := 10
oSheet:range("A1:A3"):font:bold := .T.
? "Please Wait for: "+TRANSF(EXCEL->( LASTREC() ),'9999999')+" Total
Records to convert."
? "."
// 01234567890123456789012
@ 10, 0 SAY "Rows: nCells: "
nRow := 6
xarray := {}
SELECT EXCEL
DO WHILE !EOF()
farray := {}
farray := ARRAY(FCOUNT())
FOR nCounter := 1 TO FCOUNT()
// oSheet:Cells(nRow,nCounter):Value := fieldget(nCounter)
// oSheet:Cells(nRow,nCounter):font:name := "Arial"
// oSheet:Cells(nRow,nCounter):font:size := 10
fvalue := fieldget(nCounter)
IF VALTYPE(fvalue) = "L"
fvalue := IIF(fvalue,"YES"," NO")
farray[nCounter] := fvalue
ELSE
IF !EMPTY(fvalue)
farray[nCounter] := fvalue
ELSE
farray[nCounter] := SPACE(1)
ENDIF
ENDIF
nxCells++
@ 10, 6 SAY TRANSF(RECNO(),'999999') COLOR 'N/GR*'
@ 10, 21 SAY TRANSF(nxCells,'9999999') COLOR 'N/GR*'
oSheet:Cells(nRow,nCounter):Value := fValue
NEXT nCounter
// AADD(xarray, farray)
SKIP
nRow++
// oSheet:Cells(nRow,nCounter):Select()
ENDDO
EXCEL->( DBCLOSEAREA() )
@ 12, 0 SAY "Almost Done..."
? "."
? "."
oExcel:visible := .T.
oSheet:Range("A1:B1"):Select()
CAT := "A6:"+aLETTERS[LEN(farray)]+LTRIM(TRANSF(LEN(xarray)+5,'999999'))
// oSheet:range(CAT):value := xarray
// The above method works in Alaska's Xbase++ but not here for some
reason.
oSheet:Cells:Select()
oSheet:range(CAT):font:name := "Arial"
oSheet:range(CAT):font:size := 10
oSheet:Cells:EntireColumn:AutoFit()
oSheet:Range("A1"):Select()
nRow := 1
oSheet:Cells(nRow,1):Value := cREPTDESC
oSheet:Cells(nRow,1):font:name := "Arial"
oSheet:Cells(nRow,1):font:size := 14
oSheet:Cells(nRow,1):font:bold := .T.
oSheet:Cells(nRow,1):font:ColorIndex := 3 // RED
nRow := 2
oSheet:Cells(nRow,1):Value := "DBF to Excel" // change this to anything
you want
oSheet:Cells(nRow,1):font:name := "Arial"
oSheet:Cells(nRow,1):font:size := 14
oSheet:Cells(nRow,1):font:bold := .T.
oSheet:Cells(nRow,1):font:ColorIndex := 3 // RED
nRow := 3
oSheet:Cells(nRow,1):Value := "Date: "+DTOC(DATE())
oSheet:Cells(nRow,1):font:name := "Arial"
oSheet:Cells(nRow,1):font:size := 14
oSheet:Cells(nRow,1):font:bold := .T.
oSheet:Cells(nRow,1):font:ColorIndex := 3 // RED
oSheet:Range("A1:A3"):Select()
oSheet:Cells(1,1):NumberFormat := "General"
oSheet:Cells(1,1):HorizontalAlignment = xlLeft
oSheet:Cells(2,1):NumberFormat := "General"
oSheet:Cells(2,1):HorizontalAlignment = xlLeft
oSheet:Cells(3,1):NumberFormat := "General"
oSheet:Cells(3,1):HorizontalAlignment = xlLeft
CAT := "A1:"+aLETTERS[len(farray)]+"3"
oSheet:Range(CAT):Select()
oSheet:Range(CAT):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(1,1):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(2,1):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(3,1):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(1,2):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(2,2):Interior:ColorIndex := 42 // CYAN
// oSheet:Cells(3,2):Interior:ColorIndex := 42 // CYAN
CAT := "A5:"+aLETTERS[len(farray)]+"5"
oSheet:Range(CAT):Select()
oSheet:Range(CAT):Borders(xlDiagonalDown):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlDiagonalUp):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlEdgeLeft):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlEdgeTop):LineStyle := xlNone
oSheet:Range(CAT):Borders(xlEdgeBottom):LineStyle := xlContinuous
oSheet:Range(CAT):Borders(xlEdgeBottom):Weight = xlMedium
oSheet:Range(CAT):Borders(xlEdgeBottom):ColorIndex = xlAutomatic
oSheet:Range(CAT):Borders(xlEdgeRight):LineStyle = xlNone
oSheet:Range(CAT):Borders(xlInsideVertical):LineStyle = xlNone
CAT := "A1:"+aLetters[len(farray)]+LTRIM(TRANSF(LEN(xarray)+5,'999999'))
oSheet:Range(CAT):Select()
oSheet:PageSetup:PrintArea :=
"$A$1:$"+aLetters[len(farray)]+"$"+LTRIM(TRANSF(LEN(xarray)+5,'999999'))
oSheet:PageSetup:PrintTitleRows := "$1:$5"
oSheet:PageSetup:RightHeader := " Page #&P of &N"
oSheet:PageSetup:LeftMargin := 0.50
oSheet:PageSetup:RightMargin := 0.25
oSheet:PageSetup:TopMargin := 0.75
oSheet:PageSetup:BottomMargin := 0.50
oSheet:PageSetup:HeaderMargin := 0.50
oSheet:PageSetup:FooterMargin := 0.25
oSheet:Range("A4"):Select()
cDbfFile := RTRIM(cThisdir)+"\"+STRTRAN(cDbfFile,".DBF",".XLS")
oBook:SaveAs(cDbfFile,xlWorkbookNormal)
// ALTD()
// Quit Excel
// oExcel:Quit()
oExcel:visible ...
read more »

I can't thank everyone for being so helpful. Gejza and Randy, thank
you so much for sharing your code with me. I have a lot of
information to analyze. Give me a few days to digest it all.
xHarbour rocks. I am very pleased with it.

Thanks again,

Ted

[Ron Pinkas]

Dear Ted,

In the future please try to avoid excessive quoting.

TIA,

Ron

[Mel Smith]

Post by Randy Howe
Ted, the following is a nice little conversion program I wrote entirely in
xHarbour.
If you copy from this post into your editor you may have to fix a couple
of lines
due to line wrapping, but I think the end result will please you.

Randy:

I downloaded and placed your dbf2Excl.prg program in my xHarbour\tests\
sub-dir and built it. After a lor of line wrap corrections, I built it
quickly and without any further errors.

However, when *running it*, it crashed with the message:

Error! Excel Not Available

I guess that your program assumes that the builder of the .xls. file
*must have* Excel installed ???

However, I do not have the full Excel package installed -- only the free
Excel *Viewer* on my machines in Arizona. Only some of my users actually
have the full Excel package installed.

Thanks.

-Mel Smith

[N:dlzc D:aol T:com (dlzc)]

Post by Mel Smith

Post by Randy Howe
Ted, the following is a nice little conversion program I
wrote entirely in xHarbour. If you copy from this post
into your editor you may have to fix a couple of lines
due to line wrapping, but I think the end result will
please you.

I downloaded and placed your dbf2Excl.prg program
in my xHarbour\tests\ sub-dir and built it. After a [lot]
of line wrap corrections, I built it quickly and without
any further errors.
However, when *running it*, it crashed with the
Error! Excel Not Available
I guess that your program assumes that the builder
of the .xls. file *must have* Excel installed ???

Yes. It calls Excel via OLE.

Post by Mel Smith
However, I do not have the full Excel package
installed -- only the free Excel *Viewer* on my
machines in Arizona. Only some of my users actually have the
full Excel package installed.

Then nothing can help you make .xls files. Do you / they have
*any* office package available, such as Open Office?

David A. Smith

[aardvark]

Post by N:dlzc D:aol T:com (dlzc)

Post by Mel Smith
However, when *running it*, it crashed with the
Error! Excel Not Available
I guess that your program assumes that the builder
of the .xls. file *must have* Excel installed ???

Yes. It calls Excel via OLE.

Post by Mel Smith
However, I do not have the full Excel package
installed -- only the free Excel *Viewer* on my
machines in Arizona. Only some of my users actually have the
full Excel package installed.

Then nothing can help you make .xls files. Do you / they have
*any* office package available, such as Open Office?
David A. Smith

In place of of Excel OLE you can install OWC11.EXE which is available
for free from the Microsoft website. It is recommended in place of
Excel OLE when running as a unattended process, or if multiple instances
are to run at the same time.

It usage is practically identical to Excel OLE, although it actually
produces an XML file. Provided you give it the .XLS extension Excel
opens exactly as it would a 'normal' spreadsheet file.

//--------------------------------------------------------------------
// Create a New Workbook.
TRY
oWorkBook := CreateObject( "OWC11.Spreadsheet" )
CATCH
cError := "OWC11 not available. [" + Ole2TxtError()+ "]"
END
// -------------------------------------------------------------------

Regards
"aardvark"

[Mel Smith]

David and Aardvark replied

In place of of Excel OLE you can install OWC11.EXE which is available for
free from the Microsoft website. It is recommended in place of Excel OLE
when running as a unattended process, or if multiple instances are to run
at the same time.
It usage is practically identical to Excel OLE, although it actually
produces an XML file. Provided you give it the .XLS extension Excel opens
exactly as it would a 'normal' spreadsheet file.
//--------------------------------------------------------------------
// Create a New Workbook.
TRY
oWorkBook := CreateObject( "OWC11.Spreadsheet" )
CATCH
cError := "OWC11 not available. [" + Ole2TxtError()+ "]"
END
// -------------------------------------------------------------------

Aardvark and David:

*If* one uses HMG as a windows GUI, then you can use one of the methods
in the Super Browser (TSBrowse) contirbuted by Janusz Pora and updated by
Grigory Filatov.

These methods are ExcelOLE() and Excel2()

I had forgotten about these methods :(( but appreciate the OWC11.EXE
hint by Aardvark as another method in case I can't get TSBrowser's method
working properly).


-Mel Smith