10 Replies - 3006 Views - Last Post: 11 June 2013 - 06:01 AM Rate Topic: -----

#1 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 12:02 PM

I have a worksheet in Excel set up like a data table, the first row being Column Headers and all other rows individual records. I need to transfer these records into a .DBF file for use in dBase.

Does anyone know of a way I could do this? I found a few ideas from Google such as transferring the Excel records to Access and then something about a deprecated feature that would allow Access to export records to .DBF... but that sounds a bit over the top.

Any help appreciated. I've never done anything with dBase files. Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Export Excel Worksheet to .DBF (dBase) File

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,681
  • Joined: 12-December 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 12:25 PM

This guy has a Save DBF add-in for Excel. (The option to save as .DBF was removed from Excel 2007, I believe.) I don't know if it has an API that you can use in VBA.

Otherwise it seems your options are to export the file to .csv and use the (manual) importing process within your DBF-admin application (its control panel), or, as you mentioned, to create code that will export the data firstly to Access, and then from Access to .DBF.

As you say, this option seems over the top (although possible).

A third option is to change or update the database that you use :)

Quote

The current version, dBASE PLUS 8, was announced[3] on March 19, 2013. The product not only supports the existing BDE connectivity, but it also adds support for ADO and ODBC either through the new ADO approach or with the existing BDE connections.


But.. I don't know much about .DBF files - apart from it digging up a name from the past: Ashton-Tate :whatsthat:

This post has been edited by andrewsw: 04 June 2013 - 12:28 PM

Was This Post Helpful? 0
  • +
  • -

#3 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 01:15 PM

I also came across that Add-in, was hoping their might be yet another way though... :/

If it was up to me, at a bare MINIMUM we would be using Access for this "Access" type Application instead of Excel!

Can't say I've ever heard of Ashton-Tate but will do some Googling. I seem to be coming up with a pesky little error trying to set the formulas for cells when creating a new record. For example:

Range("A8:F8").Formula = "=IFERROR(VLOOKUP(J5,Feedname!1:1048576,2,FALSE),"")"


gives me:

Quote

Run-time error '1004':

Application-defined or object-defined error


Can you spot what I'm doing wrong?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,681
  • Joined: 12-December 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 01:24 PM

Range("A8:F8").Formula = "=IFERROR(VLOOKUP(J5,Feedname!1:1048576,2,FALSE),"""")"

but don't specify the entire rows :helpsmilie: :nervous:
Was This Post Helpful? 0
  • +
  • -

#5 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 01:28 PM

View Postandrewsw, on 04 June 2013 - 08:24 PM, said:

Range("A8:F8").Formula = "=IFERROR(VLOOKUP(J5,Feedname!1:1048576,2,FALSE),"""")"

but don't specify the entire rows :helpsmilie: :nervous:


That worked! but what do you mean by:

Quote

but don't specify the entire rows :helpsmilie: :nervous:


?... :mellow:
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,681
  • Joined: 12-December 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 01:44 PM

1:1048576

You are specifying the ENTIRE worksheet in the VLOOKUP function. This should be reduced to the specific table range, not including the header-row. (Surely you knew that you were identifying entire rows?)

The revision worked because you were quoting a single quotation symbol ("") but you need two ("""").

This post has been edited by andrewsw: 04 June 2013 - 01:45 PM

Was This Post Helpful? 0
  • +
  • -

#7 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 01:53 PM

I had no clue I was specifying the whole table range. :dontgetit: The functions were put in place by whoever the non-developer was that was pulled in to create the workbook. Other than the part specifying the Cell (such as J5 in one of the examples) I don't know right off what any of the parameters signify.

The formulas were originally directly in the cells but the methods I added were overwriting the formulas, making me set the formulas in code instead.

I stated from the start I know very little when it comes to Excel, not even basic non-developer use :sweatdrop:
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,681
  • Joined: 12-December 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 04 June 2013 - 01:57 PM

I think you should take the time to learn the basics of Excel, at least up to creating some simple formulas.

After that, I recommend my Excel VBA Tutorial.

Andy.

This post has been edited by andrewsw: 04 June 2013 - 01:59 PM

Was This Post Helpful? 0
  • +
  • -

#9 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 05 June 2013 - 06:27 AM

View Postandrewsw, on 04 June 2013 - 08:57 PM, said:

I think you should take the time to learn the basics of Excel, at least up to creating some simple formulas.

After that, I recommend my Excel VBA Tutorial.

Andy.


I had a Basics course before, but that was about 3 years ago in Tech College where it never became necessary to use Excel.

Thanks for the help, and will check out your Tutorial!

-A.L.
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 10 June 2013 - 05:31 PM

Back to the original question. You can save the excel as a csv file and then go find any one of the numerous free online utilities that allow you to convert csv to dbf. Just so you know, dbf files are compatible with any other "xBase" format such as Clipper and Foxpro. You should find that any utility that allows conversion to Clipper or Foxpro is readable by whatever is needing the dbf format.

Another possibility to consider is whether your target application can use ADO. If so, there are JET connectionstrings that will allow you to save as DBF.
Was This Post Helpful? 0
  • +
  • -

#11 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Export Excel Worksheet to .DBF (dBase) File

Posted 11 June 2013 - 06:01 AM

View PostBobRodes, on 11 June 2013 - 12:31 AM, said:

Back to the original question. You can save the excel as a csv file and then go find any one of the numerous free online utilities that allow you to convert csv to dbf. Just so you know, dbf files are compatible with any other "xBase" format such as Clipper and Foxpro. You should find that any utility that allows conversion to Clipper or Foxpro is readable by whatever is needing the dbf format.

Another possibility to consider is whether your target application can use ADO. If so, there are JET connectionstrings that will allow you to save as DBF.


Thanks for the reply Bob. mentioned something about using a csv file awhile back, but at the time I had zero experience with .csv or .dbf files. (Getting somewhat acquainted with dBase files now, but still don't know .csv)

As to Clipper & Foxpro, I've never heard of those either.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1