We help IT Professionals succeed at work.

Converting FoxPro table DBF to Excel in VB

raidt
raidt asked
on
1,205 Views
Last Modified: 2012-05-04
I would like to Convert a DBF Table to Excel with out using automation because some PCs might not have foxpro installed,


my program should do that for MDB files too but i think it would be the same is that right?
Comment
Watch Question

if Foxpro IS NOT installed, you MAY have a problem, as the FoxPro installation is necessary (I believe) in order to have the DBF driver, that is needed to read the DBF files, so it can be imported into Excel.

You can also try the Data/Get External Data Menu button, In Excel, to get the data from the FoxPro table (provided that the FoxPro driver is installed on the PC where the Excel is going to be ).

AW

Author

Commented:
Sorry Can't do with that
TimCotteeHead of Software Services

Commented:
Foxpro is not necessarily required as an installation. If standard ODBC drivers are installed then you can use these. Particularly Windows 2000 and higher as these have versions of MDAC that distribute the Visual Foxpro driver as standard.

Either way you need to do some form of automation to be able to place the results into Excel. You could write a routine that simply extracts the information and writes it to an excel format file but you would need to know both file formats and handle the whole thing yourself. Much easier to use functions that already exist.
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
did you try simply open the dbf from inside Excel?


'example with VB (or you need in VBA?)
' set a reference to microsoft excel objects library

dim xlapp as excel.application
dim wbdbf as excel.workbook


sub test
      set xlapp=new excel.application
      with xlapp
              set wbdbf = .workbooks.open("c:\aplic\nnc\count01.dbf")
              wbdbf.SaveAs filename:="C:\APLIC\NNC\DBF\CTA2\pad2.xls",fileformat:=xlExcel7
      end with
end sub

Author

Commented:
The Programe will be installed on a windows 98 pc

and for the second Richie that will not work if foxpro is not installed on the pc
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
I haven't foxpro installed.

Commented:
Do you at least have the FoxPro driver installed on the PC?  Even if FoxPro is not installed, the driver should be.  Yes, you are correct, that you need not necessarily have the program that manages a database installed on a given pc, but you should at least have the ODBC drivers for them so that they can be imported into Excel.
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
AFAIK, MS Office 2K installs those drivers to open such kind of files.
CERTIFIED EXPERT

Commented:
I thought that Excel can open dbf files. So you could write a little macro to open the dbf file and save as xls.  If you don't get this option, when you installed your visual studio or MS Office you can specify which data drivers to load.

Author

Commented:
Richie_Simonetti Does that mean if i have office 97 it will not work, and if so what can i install so that the automation that you wrote the code for would work. One more thing What about Access is it going to be the same thing?
CERTIFIED EXPERT

Commented:
I think that Richie_Simonetti's code should work if Excel is already loaded on the system.

Author

Commented:
Guys i am really glad i have ppl helping me out. the problem is on my pc every thing is ok even if i uninstall access and foxpro but on my brothers pc and other pcs i get a message saying that you can't open access like that you have to import it using external data on excel, any ideas

that happens on my brothers excel 2k and he actually has access 2k installed other pcs i will test tomorrow at work.
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
Go to your control panel, locate ODBC 32 drivers and see if dbf drivers is listed.

Author

Commented:
Guys, the dbf is ok now but the MDB is the problem.

Author

Commented:
The problem with MDB. I get a message saying excel cannot open access mdb file directly. To import data into excel, use the Get External data command: in Excel  that is on office 2k
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
It is not the same original question but take in mind that Access mdb files are not the same as Fox db files.
To get data from access to excel, you need to work with ADO(Office2K) or DAO (Office97).
ADO:You need to establish a connection, create a recordset with the data that you need (Throught SQL language) and use CopyFromRecordset Method of Range object.
'some like this
dim conn as new adodb.connection
dim rs as adodb.recordset


sub GetDBData()
with conn
         .connectionstring="<your choice here>"
         .open
         if .state=adstateopen then
            set rs=new adodb.recordset
            rs.open "select * from whatevername",conn
            range("A1").copyfromrecordset rs
         end if
end with
end sub
DAO: You need to open the target DB, create a recordset with the data that you need (Throught SQL language) and use CopyFromRecordset Method of Range object.
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
ups,
with DAO:

dim db as dao.database
dim rs as dao.recordset

set db=dbengine.workspaces(0).opendatabase("c:\.....\...\mydata.mdb",false,false)
set rs=db.openrecordset("select * from whatevername")

range("A1").copyfromrecordset rs


REMEMBER: diferent version of excel works diferent with copyfromrecordset method.
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
Hi, how is going?
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Richie_Simonetti,

   I really appriciate your help but the only problem is i will get so many files generated by other companies that i would't konw the table name so i can not use the select statment as you wrote it i know i can prompt the user to enter the table name but they wount really know because they might not have access installed. :(
CERTIFIED EXPERT

Commented:
CERTIFIED EXPERT

Commented:
Here is a sample that creates a linked table to many types of files.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dao360/html/daprosourcetablenamex.asp

So onve you know the file name you can try the many options for that file name until you get a valid link.

The you only need one piece of code that opens the linked table as if it was an MDB to create the exel file.
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
https://www.experts-exchange.com/help/closing.jsp

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER

GPrentice00
Cleanup Volunteer
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

 -->Accept inthedark's comment as Answer

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER

GPrentice00
Cleanup Volunteer

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.