INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

COM and Automation

create table and field listing of database tables in excel by danceman
Posted: 30 Jul 02

This creates an excel file with a work sheet labled with the table name.  each sheet list the field name with the data type and format.

close databases
open database data\pcm
lnf = 0
lnt = 0
sloc = ''

obj = createobject('excel.application')
** set excel to only have one worksheet
obj.Application.SheetsInNewWorkbook = 1
** delete the work book that has three worksheets
obj.Application.Workbooks.close
now add a new book with only one worksheet
obj.Application.Workbooks.Add

** uncoment this line to watch it work.
*obj.Application.visible = .t.

** fox fuction to get table list from open database
lnt = ADBOBJECTS(gaTables, "TABLE")

** loop through list of tables
for i = 1 to lnt
    use gaTables(i)
    lnf = afields(gaFields,gaTables(i))

** avoid adding a worksheet on the first time around
    if i != 1
        obj.Application.Workbooks(1).worksheets.Add
    endif
    obj.Application.Workbooks(1).Worksheets(1).activate
    obj.Application.Workbooks(1).Worksheets(1).name = gaTables(i)
    use
    obj.Application.Workbooks(1).Worksheets(1).cells(1,1).Value = 'Name'
    obj.Application.Workbooks(1).Worksheets(1).cells(1,2).Value = 'Char Type'
    obj.Application.Workbooks(1).Worksheets(1).cells(1,3).Value = 'Width'
    obj.Application.Workbooks(1).Worksheets(1).cells(1,4).Value = 'Dec. Pos'
    for j = 1 to lnf
        obj.Application.Workbooks(1).Worksheets(1).cells(j+1,1).Value = gaFields(j,1)
        obj.Application.Workbooks(1).Worksheets(1).cells(j+1,2).Value = gaFields(j,2)
        obj.Application.Workbooks(1).Worksheets(1).cells(j+1,3).Value = gaFields(j,3)
        obj.Application.Workbooks(1).Worksheets(1).cells(j+1,4).Value = gaFields(j,4)
    next
next

close databases

obj.Application.Workbooks(1).SaveAs('c:\vpcmatrix\pcm.xls')
obj.Application.quit
release obj

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close