ExcelToDBF

[bpd2000]

I am using ExcelToDBF
You can open DBF file in Excel and save in DBF format after changes done
ExcelToDBF is an open source Add-in for Excel 2007 written in VB.NET enabling Excel users to accomplish two kinds of operations: saving an active Worksheet to DBF/DBF IV format and changing a DBF file using Excel.
Visit
http://exceltodbf.sourceforge.net/

Screenshot 1
The Add-in modifies the Ribbon interface of Excel 2007 by adding a new button "DBF IV" in the office menu among the different formats included in the "Save As" dropdown. This is displayed in the image below, which was captured from an Italian version of Office 2007.


Screenshot 2
By pressing the new button "DBF IV" created in the Office menu in the "Save As" dropdown, a windows message will appear to ask the user if he wants to save to DBF.


Screenshot 3
If the user wants to save an active sheet to DBF, a windows dialog box will appear to allow the user to select name and path


Screenshot 4
In saving a worksheet to DBF format, the user is asked to set names of fields, types of fields (char or numeric), number of chars for char fields and number of digits after decimal point for numeric fields. A possible structure of the database is suggested on the basis of the data found in the active worksheet. Anyway, the user is free to change the values.


Screenshot 5
The add-in changes the behaviour of the button "Save" in the office menu to allow users to save changes made to a DBF file opened by Excel.

[trmpluym]

BPD,

Office 2007 is not supported anymore. Even the support for Office 2010 is abandoned this month.

But LibreOffice still supports exporting to DBF files. Did you try ?

Theo

[bpd2000]

BPD,

Office 2007 is not supported anymore. Even the support for Office 2010 is abandoned this month.

But LibreOffice still supports exporting to DBF files. Did you try ?

Theo

I am using Office 2010 [Excel], already installed ExcelToDBF Add-in, I am facing no-problem
It will visible when .dbf file is opened in Excel
1. First install Add-in
2. After successful installation right click .dbf file -> Open With -> Select Excel [un tick always open this app to open .dbf file]
3. Now you can see Add-in option will be visible

[trmpluym]

Hi PPD,

Great to hear you managed to get it working in Office 2010. But Microsoft will end the support for Office 2010 in October this year:

https://docs.microsoft.com/en-us/deploy ... rt-roadmap

LibreOffice is free and also does the job. Even if you upgrade to Office 2019 you still can use it separate on the same pc (I use the portable version). LibreOffice natively supports exporting to DBF files (no plugin needed).

But off course there is no problem using Office 2010 and the ExcelToDBF if it suites your needs

Theo

[AUGE_OHR]

hi,

here is a Demo using COM to access XLS into Array and display it in a GRID

Excel2DBF.jpg (15.13 KiB) Viewed 7376 times

! Note : need Excel installed

hb_DBFEXCEL.zip

(1.26 MiB) Downloaded 361 times

it is only Array yet and next Step is to transfer into DBF / SQL
but GRID show that Array is correct while Array direct from Excel are "Column" instead of "Row" under harbour / HMG

i will make a new Thread when finish DBF Field Name, Type, Len Selection to get a proper DBF / SQL Table.

[trmpluym]

Jimmy,

Nice !

Are you willing to share the source of hb_DBFEXCEL.zip (so all of us can learn from it) ?

Theo

[AUGE_OHR]

Are you willing to share the source of hb_DBFEXCEL.zip (so all of us can learn from it) ?

Yes ... but it is not finish yet.

here is the Part how to transfer Excel Sheet to Array

Code: Select all

PROCEDURE OpenXls( cXls )
LOCAL oExcel, oWorkBook, oSheet, oRange, oError, bSaveError, i, numRows, numColumns, cEnde
LOCAL aExcel     := {}

   IF ( oExcel := win_oleCreateObject( "Excel.Application" ) ) == NIL
      MsgInfo( 'Error: Microsoft Excel is not installed, ', win_OleErrorText() )
      RETURN
   ENDIF

   MainForm.Button_Open.hide()

   // save Errorblock
   bSaveError := ERRORBLOCK()

   // set new Errorblock
   ERRORBLOCK( { | e | BREAK( e ) } )
   BEGIN SEQUENCE
      // not visible
      oExcel:Application:visible := .F.

      // disable warnings
      oExcel:Application:DisplayAlerts := .F.

      // open XLS-Table in Workbook
      oExcel:Application:workbooks:Open( cXls )

      // Make the first one active
      oWorkBook := oExcel:activeWorkBook
      oSheet := oExcel:Application:Worksheets( 1 ) :activate()

      // Speed things up by creating an object containing the cells
      oSheet := oExcel:Worksheets( 1 ) :cells

      // size of Sheet
      numRows := oWorkBook:workSheets( 1 ) :usedRange:Rows:Count
      numColumns := oWorkBook:workSheets( 1 ) :usedRange:Columns:Count

      // create Array with same size
      FOR i := 1 TO numRows
         AADD( aExcel, ARRAY( numColumns ) )
      NEXT
      cEnde := ZAHL2CHR( numColumns )

      // read hole into Array
      aExcel := oSheet:range( "A1:" + cEnde + LTRIM( STR( numRows ) ) ) :value
      // quit Excel
      oExcel:Quit()

      // Array to DBF / SQL (under Construction)
      Data2Dbf( aExcel, cXls )

   RECOVER USING oError
      // restore Errorblock
      ERRORBLOCK( bSaveError )

   END SEQUENCE
   // restore Errorblock
   ERRORBLOCK( bSaveError )

   MainForm.Button_Open.show()

RETURN

[bpd2000]

Jimmy
Refer example at C:\MiniGUI\SAMPLES\BASIC\GRID_8

Untitled.png (10.43 KiB) Viewed 7290 times

You will find Export Grid to Dbf and
Export Dbf to Excel

The main benefit of ExcelToDBF Add-is not limited to export in Dbf
What I am enjoying is to open Dbf file in Excel do all the work like Excel file i.e. add/delete column/Row, add data using formula etc.
I can not found such Easiness in any other software

[AUGE_OHR]

hi,

thx for your link to MiniGUI Sample.
but it does work "other" Way from DBF or GRID to Excel

i want to read a Excel Sheet and transfer Data to DBF / SQL
that is what Demo Sample is doing.

---

next Step is "easy" if a xBase Programmer have create XLS but if a User ...
we know that FILED Name can only have 10 Sign and " " Space is not allowed

so i'm working on a Structure Modul which get Result from "FindHeaderLine()"
for more Information e.g. LEN i sort Column Type "C" to get "longest" Sting to fit into FIELD

---

a xBase Programmer "expect" Header for Column in 1st ROW ...
but User can put some line before e.g. Name, Adresse etc.
so just must have the Choise "where" to start with "Data" to transfer from Array

---

now i got to that point that i have create Modul with GRID but i can't "ON SAVE" it
i can EDIT and change it but "ON SAVE" is not fired and ARRAY is not change.

GRID_ONSAVE_ARRAY.JPG (115.29 KiB) Viewed 7271 times

i saw working Sample but they use DBF not ARRAY.

so how to use This.EditBuffer when change something in GRID