ANmarS - Portfolio - ConvAN_v07

Excel/VBA expert since Sep 1997 (19 years 7 months)

ConvAN_v07    Added: 8/28/2015 | Created: 8/28/2015   



My first ever macro in LibreOffice Calc, enjoyed the past few days playing with LibreOffice Basic (as in Excel VBA) to create this macro.

This too will convert dbf files (named Database.dbf and can be changed in 'Settings' sheet) found in any folder (or subfolders up to three levels down) into CSV placed next to it with UTF-8 preserved.

It should be working, but do not expect a fully smooth process since this is my first ever macro in LibreOffice Calc



I needed quickly to read dbf files, but Excel does not read them correctly, when you open dbf, when you import, when you connect to them using ODBC, no matter how, the encoding is lost and intenational chars (using UTF-8) is lost ending you with broken files.
So, I asked my best firned, Bing (or Google in some cases) about that, and it turns that this is an issue, you-have-to-pay-to-get-an-app-to-do-that type of issue.
However, I know that other Office applications, the free ones like LibreOffice or OpenOffice, can easily import those dbf files (Open dbf in LO or OO will also brake them) and you can see encoding correctly.
So, I decided it is time for me to jump into those two guys.
After two days of try-and-error and a lot of searching online, I built my first ever macro in LibreOffice.
So this is an ODS file with macro that will convert all DBF files matching certain name into CSV next to them preserving the UTF-8.
Then, I finally got a new macro in Excel to read those CSVs and combine them to finish my final product.
Anyhows, this was new to me, and I had to go through several alternative ways and start from square number one almost 5 times in order to get this going, Obviously, LibreOffice Basic (The name for Visual basic in LibreOffice) needs a lot of work and documentation to do in order to start competing with Microsoft.

I tried to automate that process by calling from outside LibreOffice, but that was a fail within my allowed time

Anyways, you will need LibreOffice to run this tool
I prefer you follow these steps:
1 - Install LibreOffice for free from LibreOffice.org (Should also work with OpenOffice.org, but not tested there)
2- After installation, run Calc
3- In that Calc window, change Macros Security Level to “Medium”, got to Tools > Options > Security > Macros Security > Medium (screenshot found below and also found in zip)
4- Close Calc
5- Open ConvAN tool and click ‘Enable macros’

Paste the folder in "By City" sheet green cell, then click on the tool name to launch the macro.

As always, the code is free to be shared and used.


Download (166.3 KB)

1048 downloads | Last updated: 8/29/2015


Screen shots (8)