Hi All,

Is there any way to get all tables (and all fields in each table) into
excel automatically without having to manually enter all table names
and field names into SELECT queries?

Ideally, one sheet per table with the sheets named with the table
names, but anything close would be good.

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

It is possible but would require some pretty intense coding which I don't
have time to work out right now. But the key would be using ADO to connect
to the database and then you can use the OpenSchema method to read the names
of the tables. You could iterate through these in a loop, append a worksheet
to your workbook, and then use standard ADO recordset methods to retrieve the
data from each table (SELECT * FROM TABLENAME should be sufficient). You
could get column headers, if desired, from the Field.Name property (iterating
through the fields).

Sorry I can't do all the details, but for info on reading the database
schema see this:
http://msdn.microsoft.com/library/de...openschema.asp

If you need info on ADO methods in general:
http://msdn.microsoft.com/library/de...troduction.asp
--
- K Dales


"Alan" wrote:


Hi All,

Is there any way to get all tables (and all fields in each table) into
excel automatically without having to manually enter all table names
and field names into SELECT queries?

Ideally, one sheet per table with the sheets named with the table
names, but anything close would be good.

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

Hi Alan,

I am not sure what version was used to create the files, but I can
access the tables manually as follows:

There is an ODBC connection of type 'Visual Foxpro Database (DBC file)
so you are spot on there.

As you mention it does appear that this is just a link to the actual
tables which are in separate DBF files (I think).

The actual tables come in 3 parts: the DBF file is the table itself, the FPT
file (optional) contains the Memo field data, and the CDX (optional)
contains the indexes.

If I were doing this in Visual FoxPro (VFP9 is the latest version)
it would be really simple. :-)

I looks like I am!

Does that mean there is an easy to achieve what I need to do or do you
mean I would need to have VFP9 installed (which I don't)?

From a program written in VFP and run in the VFP IDE or as an executable the
code would look like this:

*-- Get an array of the DBFs in the directory
*-- Column 1 has file names
ADir(ArrayOfFiles, "*.dbf")

*-- Create the Excel Workbook
oExcel = CreateObject("Excel.Application")
oExcel.Visible = .T. && For testing
oExcel.Workbooks.Add()

*-- Iterate through column 1 of the array
*-- More than one way to do this but here's one
For nCount = 1 To Alen(ArrayOfFiles)
*-- If this element is in column 1
If ASubscript(ArrayOfFiles, nCount, 2) = 1
With oExcel
.Worksheets.Add()
cFileName = Alltrim(ArrayOfFiles(nCount))
Use (cFileName) Shared && Use opens a table
_Vfp.DataToClip(,,3) && Creates tab-delimited text
.ActiveSheet.Name = cFileName
.ActiveSheet.Cells(1, 1).Activate
.ActiveCell.PasteSpecial()
EndWith
EndIf
EndFor

oExcel.SaveAs("MyWorkbook")
oExcel.Quit()
oExcel = .NULL.
Clear All && Release all variables and close all tables

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden