Mixing XLS and DBF tables

In lieu of some latest trends in my FoxPro development I want to know how do you sensible mix Excel .xls tables, workbooks and projects with FoxPro .dbf tables and databases.

I have followed Marcia's earlier suggestion to use convert .dbf into .xls for graphs and now I want to integrate my .xls into existing databases.

I tried to attach an .xls table to my databases but only could do in the browser as free table. This was to now avail, however, since I could not open it in FoxPro anyway.

Another question, somewhat related:

I used a wizard to define the connection string to a FoxPro Database. How can I do it if I need to define a connection string to an .xls table What is the equivalent for a database in Excel: a workbook Is there a corresponding wizard to use for connections

Thanks.

Visual FoxPro2

Here you go :

MCONNSTR="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\FOX\TEST.xls;DefaultDir=c:\FOX;"

nConn = sqlstringconnect(MCONNSTR)
sqlexec(nconn, "select * from [sheet1$]", "CRSXLS")

SELECT CRSXLS
BROW

Real Simple.
Dave

As an addition to what Dave gave, you can also use cursoradapter with OLEDB connection (Jet.4.0 driver).

If I understand what you're asking, I don't recommend. I think you're saying that your code is creating an Excel workbook based on data in your database and then you want to add that workbook to your database. VFP databases contain VFP tables, and don't contain Excel workbooks. You can store a workbook in a field of a table, but that results in a static object.

You have a few options here:

1) Create the workbook each time you need it. This is the best choice if you need the workbook to show the current data.
2) Save the workbook as a file and store the filename in the relevant table. This is the best choice if you need to keep a historical record of the workbooks and create a new one when there's new data.
3) Create the workbook so it links to the VFP data and can be updated with newer data by re-opening it.

Tamar

Women always understand the man's confusion better than any given man:) It is exactly what I've been looking for! I still have a few questions if you do not mind.

I understand that when I use a COPY TO command an .xls TABLE is created, not a workbook. In the Excel browser/wizard if I go there I start a workbook (e.g. book1) and I can download a table that has been created by FoxPro with COPY TO command, so I will see that table in the workbook. Then I save the book1 and the resulting two files have extensions .xls and .mdb. Where is the workbook Is .mdb a sort of a database for Excel

Then if I want to add another .xls table to the workbook book1 how shall I go about it

I would like your to elaborate on your point (3). How can I link a workbook to VFP data What are the specific steps for it

I am asking all these stupid questions because I want to find out how other people manage large number of .xls files. If I start on this path there is a chance that the number of such files will multiply (I am not sure about it yet-maybe they all will be temporary and could be quickly destroyed)

Many thanks to you as well as to Dave and Cetin for their informative answers.

There's no such thing as an .xls table. The XLS extension indicates a workbook. MDB however, is an Access database, so something you're doing is converting to Access.

When you use COPY TO TYPE XL5 (which is always better than XLS, but does give you an XLS extension), you are creating an Excel workbook.

If you need to export data from multiple pages into different worksheets within a workbook, you'll need to use Automation. Again, you have some choices, but the fastest way is probably to COPY TO each table/cursor to a separate Excel file, and then use Automation of Excel to combine them into a single workbook. I haven't done this recently, but I think you have to open each workbook separately and then use copy and paste to move the sheets from one to the other. Let me know if you need the beginner's version of automating Excel.

As for linking to VFP data, again, you should be able to do this with Automation. I don't know offhand how it's done, but the best way to get started such a task is to record a macro in Excel as you perform it manually and see what objects and methods it uses, then look those up. To set up such links in Excel, check out the Import Data item on the Data menu.

Tamar

Thank you very much, Tamar. It is exactly the type of information I've been looking for: not so much in detail, but an illumination in principle, that it could be done and people do it.

I use

TYPE XLS

with COPY TO but am going to switch to XL5.

Thanks.