LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1884
Members Online:
2
Guests Online:
92

Total Post History
Posts:
81461
Topics:
18719

7-Day Post History
New Posts:
4
New Topics:
1
Active Topics:
2

Our newest member
manails232x

Other

FAQ
All times are UTC [ DST ]

Excel: List the column (field) names, sheet or named range

Please wait...

Excel: List the column (field) names, sheet or named range

Postby Remou on Fri Jul 31, 2009 11:30 am

This works for me in Excel using a saved workbook, it lists the column (field) names.
  1.    Sub ListFieldADO()
  2.        
  3.         strFile = Workbooks(1).FullName
  4.         strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
  5.             & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  6.        
  7.        
  8.         Set cn = CreateObject("ADODB.Connection")
  9.        
  10.         cn.Open strCon
  11.        
  12.         ''You can also use the name of a named range
  13.         Set rs = cn.OpenSchema(adSchemaColumns, _
  14.             Array(Empty, Empty, "Sheet1$"))
  15.         While Not rs.EOF
  16.             Debug.Print "     " & rs!Column_Name
  17.             rs.MoveNext
  18.         Wend
  19.    
  20.         rs.Close
  21.         Set rs = Nothing
  22.     End Sub
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated