theXLwiz

UPDATE: 64-bit Office add-in

2012-01-11T12:44:00.000-08:00

The 64-bit version of the SAVEDBF add-in is not yet ready...:-(

Unfortunately it is a bigger undertake than we originally estimated. Replacing/recompiling the standard VBA code is relatively straightforward. However, we replaced/rewrote the entire dBase database driver from scratch in the 2011 May release as Microsoft's ACE/OLE driver was way too buggy and unreliable. That part is using a lot of non-standard and more sophisticated VBA coding that is a bit more challenging to recompile/re-write for 64-bit.

We are getting there but there will be a few months of rigorous testing. Our software has been virtually bug free since its release and would like to keep it that way!...:-)

Thanks for your patience!

PS, Microsoft themselves advise people against installing the 64-bit version of office (even on a Win7 64 bit machine) as most developers need to re-write their 32-bit apps and unless they do very basic standard VBA coding, there are things to replace/rewrite (Office 2010 VBA is called VBA 7 with different syntax, and of course we need to write one add-in that works in both environments for now).

More on this can be found at http://technet.microsoft.com/en-us/library/ee681792.aspx

Gyula

Journey - Just beginning!

2011-06-14T22:07:00.000-07:00

Over the last 2 years, quite a few people commented on the impressive qualities of the SaveDBF Excel add-in and its reliability.

I did a quick summary of my ever-growing customer list and I was surprised at the variety of industries and geographical diversity of the people who are using my software. The SaveDBF Excel add-in is used on every continent and over 80 countries.

My esteemed customer list includes researchers studying glaciers in Antarctica, students and professors from literally all universities in Europe and North America, Fortune 500 companies with multi-billion dollars of revenue and many government agencies in the US. I have non-profit organizations such as the Nature Conservancy Group, small business owners in South America, organizations supporting mine-recovery efforts in Afghanistan, some of the largest utility and forestry companies in the world, l and the list goes on...

If you are interested in the software and would like to evaluate it for your company or if you have a project in mind, please email me. 100% satisfaction guaranteed!

I have yet to find an Excel problem that I could not solve. Challenge me...:-)

Quick Note: 64-bit versus 32-bit Microsoft Excel 2010

2011-06-02T07:09:00.000-07:00

The SaveDBF Excel add-in is a 32-bit add-in that will not run on a 64-bit version of Office under Windows 7.

There is no real advantage of installing the 64-bit version unless you are manipulating 2-GB+ files.

How can you tell if your version of Office 2010 will work with the add-in?

If you have an XLSTART directory under C:\Program Files (x86)\Microsoft Office\Office14\ then you have the 32-bit version installed.
If you have an XLSTART directory under C:\Program Files\Microsoft Office\Office14\ then you have the 64-bit version installed.

The merits of each system are described at http://technet.microsoft.com/en-us/library/ee681792.aspx

I will be rolling out a 64-bit edition of the add-in likely by the end of the year.

SaveDBF Excel addin - New Version is Now Available!

2011-03-28T21:25:00.000-07:00

In addition to bug fixes, here are the highlights of the new added features:

Tested with the latest Excel 2010 on Windows XP/Vista and Win 7 (also with Excel 2007).
Replaced the Microsoft database driver with my own that creates native binary DBF files. MS is slowly phasing out the DBF capabilities in its driver and this step had to be made sooner or later. No relying on MS database features anymore!
Added the option to over-write the original DBF file (a backup is created).
Added the option to auto-fix column names in an Excel file (this is limited to removing leading and trailing spaces automatically and replacing illegal characters with an underscore).
Added the option to convert an Excel file to ALL TEXT fields based on the width of the columns. This is a great feature for labeling programs.
There are no longer any issues with dashes/spaces etc in the file name or worksheet tab name! Any names are allowed.
Excel files that contain error values are now converted and the error values are automatically replaced with a blank.
Hidden columns are identified.
Even more improved error handling and many more!

Many tireless weekends of development and testing went into this version so I hope you will like it!

Thank you for your patience!

Quick Note: SaveDBF add-in and Excel 2010

2010-08-03T19:02:00.000-07:00

Some potential users asked me if the SaveDBF add-in is compatible with Excel 2010, the latest Excel version. I will be installing Excel 2010 in September on my machine and will thoroughly test it. However, several users reported that the add-in works with no problems at all in Excel 2010 on Windows 7. If you are one of those users with Excel 2010, please comment on your experience, things that might help other users decide to make the switch. I started losing count on the Excel versions people now are using all over the world...
UPDATE: a couple of users reported problems with Chinese characters on Excel 2010. Likely a code page issue that can be solved with the right database driver. Will investigate when I lay my hands on Excel 2010 in a few weeks...

Major milestone - 500 users!

2010-02-02T21:16:00.000-08:00

Just for the bragging rights...;-)

In a short 5 months time I surpassed 500 registered users! I have users from literally every continent (yeah, even from Antarctica) and from over 80 countries! I have users from some very large corporations to university students, from electrical engineers to GIS analysts!

Pretty neat!

Just a small sample of testimonials:

"I work with the ArcView GIS software where I routinely edit the attribute DBF tables of the shape files in Excel. I used to do this in Excel 2003 but I had to use a lot of workaround in Excel 2007 to make it work. I love that the SaveDBF add-in does not overwrite the original file and it is especially helpful that the add-in warns me if the attribute table does not satisfy the DBF standards. This was a big problem for me in Excel 2003. All in all the SaveDBF program is a marvelous tool that is a must-have for all GIS analysts if they have Office 2007 on their system."

Thomas (Hungary)

"Your Save DBF add-in literally saved my GIS course this autumn at <>. I teach GIS Applications for Earth and Environmental Scientists. Much of the course involves creating geo data bases of various map attributes (mineral assays, geologic structural measurements, precipitation values, geochemical analyses, etc.) then plotting and or contouring this data them in the ArcGIS-ArcMap platform. In the past we were able to directly convert Excel 2003 data files into the DBF format required by ArcMap. Our upgraded computers now carry Excel 2007 which no longer has that conversion functionality. Your add-in program was easy to install and works beautifully. Its error check function also helps students easily find and fix simple errors such as spaces in column headings or too many characters. I appreciate your generosity in sharing the program with us."

John (United States)

"Without the SaveDBF add-in, I would have had to reinvent a lot of IT processes to get around the elimination of saving to dbf in Excel 2007. The add-in was easy to install and the users have found it very easy to use."

Karen (United States)

"Saving data to DBF format is a must have when working with GIS systems such as ArcGIS. SaveDBF is an easy to use application that nicely overcomes new limitations in Excel when writing to DBFs."

Steven (Australia)

"SaveDBF played a critical role in my converting Excel 2007 files to dbase format for use in ArcView 9.0. Without SaveDBF, I was at a dead-end, with no option but to spend another 1K (which I don't have) for upgrading to Arcview 9.3, which reads Excel files directly. So SaveDBF saved me big bucks" It's a great product; easy to install and works flawlessly. Gyula has done a tremendous job in developing this product."

Anthony (Canada)

"I was very pleased with how quickly and easily I was able to get a fully working version of the software. This add-in will make life a lot easier with all of the GIS work I do!"

Adam (United States)

DOWNLOAD the latest version from HERE!.

SaveDBF Excel Add-in future plans

2009-11-19T17:52:00.000-08:00

Dear Fellow DBF Users,

The latest upgrade of the SaveDBF Excel 2007 add-in (used to be called XLSX2DBF) can be now downloaded from HERE!.

The changes include fixes related to minor bugs in Microsoft's OLEDB engine and some improved field type identification for users who start out with an Excel file.

Future plans:

I plan one more upgrade in Jan/Feb 2010. This will be a major upgrade with lots of planned improvements as listed below:

- Add a Preference window where users can set to:

Overwrite the original DBF file (and create a date-stamped backup file in the same folder).

Mimic Microsoft's Excel 2003 behaviour (i.e. automatically replace spaces in field names with an underscore, accept field names longer than 10 characters)

- Add a Data Conversion window where the user can change the field type and size from what the add-in determined from the data. This is useful for people who start with an Excel file with data that do not necessarily reflect the maximum field size for a character field, for example. The add-in will guess the maximum size from the data, but the user can over-ride it in the new window. Or you can simply click OK and the add-in will behave exactly as it does now.

- Batch processing capabilities (i.e. calling the macro from a third party program (e.g. Python or Perl or C++ etc) and mass-process many files without user interaction.

- Improved speed for very large DBF files and a progress bar with % complete reported.

If you have other improvement ideas this is the time to email them to me at gygulyas - at - yahoo - dot - ca!

Thank you very much for your continued support!

Long live the DBF!

Yeah nice, but can your add-in deal with very large DBF files?

2009-10-15T12:02:00.001-07:00

Of course it can. Mind you the maximum is 1,048,575 records...which is the limitation of the Excel 2007 worksheet.

Check out the YouTube video below.

Quite a few people are using the add-in with engineering applications (like SCADA) and with geographic information systems (ArcInfo, ArcMap etc.).

I already have plans for an even more advanced version specially for engineers. Planned features will likely include:

ability to overwrite the original dbf file (of course a date stamped backup file would be created to ensure no loss of data)
ability to over-ride the add-in defined field sizes. The add-in is pretty good at defining the field types and sizes from the data, however engineering systems might require a pre-defined field size. So the add-in will make suggestions, a popup shows with the suggestions, user can over-ride or just click ok.
batch processing capabilities
even more improved error-handling capabilities

So what d'ya say? What's your fancy? Suggestions are welcome!!!

You can download the add-in on the right in the sidebar.

YouTube Demo 2: Excel file ---> DBF File

2009-10-01T15:37:00.000-07:00

I recorded a short video on how to manipulate an Excel file using the SaveDBF Excel 2007 add-in and uploaded it to YouTube (access it here).

YouTube Demo 1: How to manipulate a DBF file?

2009-10-01T15:18:00.000-07:00

I recorded a short video on how to manipulate a native DBF file using the SaveDBF Excel 2007 add-in and uploaded it to YouTube (access it here). More will follow...

YouTube: SaveDBF Installation Instructions

2009-10-01T09:53:00.000-07:00

I recorded a short video on how to install the SaveDBF Excel 2007 add-in and uploaded it to YouTube (access it here). More will follow...

Excel 2003 - as a dbf editor

2009-09-24T15:06:00.000-07:00

Some people asked my why they should switch to Excel 2007 for dbf file editing with the add-in instead of just 'sticking it out' with Excel 2003...

Some considerations:

- Excel 2003 can only handle 65535 records. Excel 2007 works with 1 million records in a worksheet.

- Excel 2003 is very bad at retaining variable types and variable precisions so your dbf file structure changes (lots of time bloats). The SaveDBF add-in will preserve the original field types and size will only change if your edit results in a longer field. I.e., you edit your data and enter 11 characters in a text field that was only 10 long.

- Excel 2003 does not warn the user about violations to field naming standards imposed by dBase. I.e., a field name is longer than 10 characters it will get truncated (which could create a duplicate field, a common issue with GIS). The addin warns the user about violations visually highlighting the problem fields and adds cell comments with suggestions to fix them.

- the add-in does a much better job at creating field types and sizes when the user actually starts from a native Excel file. Microsoft only looks at the first few records to decide on the field type.

AND...

- Eventually you will have to switch...:-)

I invested significant time and effort into developing this software.

PLEASE SUPPORT ME by donating on the right sidebar of this blog!

DOWNLOAD the new version from here OR here.

Please read the installation instructions in the PDF file!

Update: SaveDBF add-in

2009-09-10T23:41:00.000-07:00

I have added a number of new features and fixed some minor bugs in the SaveDBF add-in:

- Some users experienced a "ghost link". Now it is removed.

- Added a Save As dialog box to give the user the ability to select their own file name and location (it still gives you a default name, and NO you CANNOT overwrite your source file).

- Better error handling, including the check of field sizes against the dBase limitations.

- Added suggestions when the User violates the dBase field naming standards.

- The add-in extends the original field size of the DBF only if the user edits an existing integer or character field and increases its length.

I will release likely the last version for the year in early October so come back and visit!

Thanks for all the suggestion!

I invested significant time and effort into developing this software.

PLEASE SUPPORT ME by donating on the right sidebar of this blog!

DOWNLOAD the new version from here OR here.

Please read the installation instructions in the PDF file!

UPDATE: Save DBF in Excel 2007

2009-09-01T13:47:00.000-07:00

I have upgraded the Excel 2007 add-in to address a few requests:

1. Now you can add/insert new fields, create calculated fields in addition to adding new records or editing existing records in your native DBF file!

2. If you start with an Excel file the software now have enhanced capabilities to determine the field types (better than Microsoft's own in earlier Excel versions).

3. The add-in checks DBase field naming conventions and also identifies duplicate fields. All problem field names are visually identified with a cell comment!

4. If you start out with a brand new file and forget to save it, the add-in will ask before the conversion.

5. Large files are supported. I edited files over 500,000 records with no problem.

KNOWN ISSUES:

- Microsoft Excel 2007 has a bug related to file names used in worksheet functions...If you experience any issues (i.e. clicking SaveDBF does not seem to work), try renaming your file to a short and sweet name and try again. An example on the nature of the problem is described here. (in case you REALLY wanted to know...)
Update: Do not use spaces or dashes (-) in the file name or in the worksheet name you are trying to convert! Better replace them with underscores (_).

I invested significant time and effort into developing this software.

PLEASE SUPPORT ME by donating on the right sidebar of this blog!

DOWNLOAD the new version from here.

Please read the installation instructions in the PDF file!

Save DBF Files in Excel 2007

2009-07-01T10:57:00.000-07:00

Microsoft has decided not to support dBase files in Excel 2007. Many GIS users and especially people in South America where FoxPro is still widely used were left without an easy-to-use data editing tool.

XLSX2DBF is an Excel 2007 add-in that helps convert and/or save a native DBF file that was edited with Excel 2007.

Two common scenarios are supported:

A) Open a native DBF file in Excel 2007 – Make changes – Save as a native DBF file with the changes.

B) Open/create an Excel file that looks like a database (headers/columns/rows) – Save as a native DBF file.

NOTES:

• The second scenario requires the header (field names) to meet dBase field naming standards (max. 10 characters, no spaces etc.).

• The native dBase file will not ‘bloat’ in size and all field types/sizes will be the same as the original.

• Excel 2007 still reads/opens DBF files; however it will not save them as DBF. You can associate the dbf extension with Excel and then you can open the dbf file in Excel by simply double-clicking on it (how to set the default program for a file type: http://www.youtube.com/watch?v=O7ateZy-DTE).

• Excel 2007 requires a slightly different process for add-in installation. See how at http://peltiertech.com/WordPress/installing-an-add-in-in-excel-2007/.

• Binary, MEMO, OLE fields are not (yet) supported.

• The original dbf file is never overwritten. Another dbf file with the same name and ‘_new’ suffix is created in the same folder. The original index files (NDX, MDX, CDX, IDX etc.) and memo files (DBT, FPT), if any are not copied with the ‘_new’ suffix. This is done to protect the integrity of the original data.

INSTALLATION INSTRUCTIONS:

1. Unzip xlsx2dbf.zip into a temporary directory.

2. Copy xlsx2dbf.xlam to one of the trusted locations (try C:\Program Files\Microsoft Office\Office12\XLSTART\)

3. Install the Excel 2007 add-in. See how at http://peltiertech.com/WordPress/installing-an-add-in-in-excel-2007/.

After successful installation, the add-in appears as XLSX2DBF on the right side of the menu. Clicking it will show a ribbon as presented below:

KNOWN ISSUES:

If the add-in does not execute after installation (i.e., upon opening Excel 2007, the macro is disabled despite being in a trusted location), you will need to install the Office 2007 Service Pack 2.

If you create some data 'from scratch' in a new Excel workbook, first you have to save it as an Excel file (any name will do if it is a temporary file). Once the file is saved, you can click the XLSX2DBF button on the ribbon and the active sheet will be saved as a DBF file. Unnamed workbooks are not 'known' to Excel until they are saved (even if they contain data you entered).

DOWNLOAD the add-in from the September 1, 2009 post (UPDATED version).

SUPPORT: If I helped you out, please donate any small amount by clicking on the DONATE button on the sidebar of this blog.

Excel UserForm Tricks

2006-11-13T20:09:00.000-08:00

UserForms are great tools to add some “spice” to your Excel applications; however they seem rather limited and dull when compared to other “professional” programs written in C++ and other advanced programming languages.

Guess what! Visual Basic for Applications combined with the power of standard Windows API (Application Programming Interface) calls can pretty much achieve anything that your heart desires when it comes to UserForms.

Windows API calls are simply references to standard library functions that are available to anyone programming under the Windows environment. An excellent place to look at the myriads of utilities that can be developed using Windows API calls is at http://www.xcelfiles.com/APIIndex.html developed by Ivan Moala.

There are plenty of places on the Internet that explain how to call standard Windows API from Excel; one of the best ones can be found at http://www.informit.com/articles/article.asp?p=366892&rl=1 .

Stephen Bullen developed a Form Class in Excel that makes it very easy to modify your UserForm without having to understand the intricacies of Windows API calls. His site has a lot of goodies at http://www.oaltd.co.uk/Excel/Default.htm .

To hide the Close button of your UserForm becomes as simple as:

myForm.ShowCloseBtn = False

or to hide the Caption

myForm.ShowCaption = False

I added to this class the ability to change the transparency (opacity) of the UserForm and also to make the UserForm “draggable” even if the caption of the form is not shown.

Setting the opacity of the UserForm to semi-transparent is as simple as:

myForm.Opacity = 180

Opacity can be set from 0 (invisible) to 255 (normal). However, setting it to zero might create some problems…;-) I suggest a range of 80-255 would be plenty to experiment with.

Enabling the user to drag the form using the left mouse button, even if the caption is hidden is also a simple line of

myForm.SetDraggable

Making the form draggable is actually quite ingenious (and therefore has nothing to do with me!). It provides us VBA programmers a glimpse of how Windows works and it is worth explaining BRIEFLY.

Windows works based on messages it gets from a queue. Windows looks at the message queue and processes those messages one after the other. Well, luckily for us, we can send messages to this queue using Windows own SendMessage function. In fact, we can hijack the process and inject our own messages (hooks) into the queue. Note to aspiring hackers: this is not as easy to do as it sounds, especially if you want to send hooks to the queue for a window that your original process does not “own”.

But for us, trying to manipulate an Excel window (i.e., UserForm) from within Excel VBA, this is very doable.

So when we make the UserForm draggable we essentially do the following:

When the user presses down the left mouse button:

Release the mouse and make it available for normal mouse input (in other words, ignore the fact that the left mouse button is clicked)

This is achieved by using the ReleaseCapture function of the user32.dll available to all Windows users.

Send Windows a message that says: “Hey, the left mouse button is down on the userform’s caption!”

This is achieved by calling the SendMessage function of the user32.dll available to all Windows users. Of course you are tricking Windows to think that it is happening, when it is not. Pretty neat, huh?

Well, this is it.

Download

Why don’t you have a look at the code and the demo UserForm here?

What's Next?

My next project will be to add the SetShape method to the class so you can create transparent UserForms that can take any shape (oval, polygon, star, etc.). Yes, even a donut with a big hole in the middle! It probably does not come as a surprise that those all use Windows API calls. as well.

Finally...it got under way.

2006-10-23T17:14:00.000-07:00

I have been planning on setting up this blog for quite some time, but there was always "one more" project that got in the way. My plan is to release a few short postings on advanced Excel techniques that are either not well known or hard to find on the net.

I will be releasing "how to" postings on Excel Userforms that will include techniques on how to set transparency, remove caption and also on how to create skinable forms (the kind that can take on any shape, not just the boring rectangle) using standard API calls available in Windows.

I will also discuss how to embed web services into Excel (much easier to do in Excel 2003!), how to embed Google Maps and other services.

So now this is under way! Phew!