Converting FoxPro table DBF to Excel in VB

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

Sorry Can't do with that

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.

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

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

I haven't foxpro installed.

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.

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

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.

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?

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

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.

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

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

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

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.

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.

Hi, how is going?

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. :(

You don't need access to crerate a link you can doit like this:

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

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