Get column name and data type from closed workbook
in [Programming]
|
Prev: Array Limit
Next: move item in multiselect listbox
From: CG Rosen on 2 May 2010 02:25 Good day Group, Found the code below from this group that helps me to retrieve the Sheet names from a closed Workbook. Is it possible to get the name of the columns (fields) from a Worksheet in a closed Workbook in the same way? Is it posssible to get the format (data types) of each column? As new to this I�am grateful for some hints. Brgds CG Rosen ---------------------------------------------------------------------------------- 'Sub GetSheetNames() Dim cnn As ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim lRow As Long Dim szBookName As String Dim szConnect As String Dim szTableName As String szBookName = "C:\Users\test1\db_test1.xls" szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sConString & ";" & _ "Extended Properties=Excel 8.0;" Set cnn = New ADODB.Connection cnn.Open szConnect Set cat = New ADOX.Catalog Set cat.ActiveConnection = cnn Sheets("data").Range("B1:B100").ClearContents lRow = 1 For Each tbl In cat.Tables szTableName = tbl.Name ''' Worksheet names always end in the "$" character. If Right$(szTableName, 1) = "$" Then Sheets("data").Cells(lRow, 2).Value = _ Left$(szTableName, Len(szTableName) - 1) lRow = lRow + 1 End If Next tbl cnn.Close Set cat = Nothing Set cnn = Nothing End sub
From: Tim Williams on 2 May 2010 02:46 Have you tried here ? http://msdn.microsoft.com/en-us/library/ms677529%28VS.85%29.aspx Tim "CG Rosen" <carlgran.rosen(a)telia.com> wrote in message news:D1168E90-F0FE-4876-AB25-9EFA88CF4093(a)microsoft.com... > > > Good day Group, > > Found the code below from this group that helps me to retrieve the Sheet > names from > a closed Workbook. Is it possible to get the name of the columns (fields) > from > a Worksheet in a closed Workbook in the same way? Is it posssible to get > the format (data types) of each column? As new to this I�am grateful for > some > hints. > > Brgds > > CG Rosen > > ---------------------------------------------------------------------------------- > > 'Sub GetSheetNames() > > Dim cnn As ADODB.Connection > Dim cat As ADOX.Catalog > Dim tbl As ADOX.Table > Dim lRow As Long > Dim szBookName As String > Dim szConnect As String > Dim szTableName As String > > szBookName = "C:\Users\test1\db_test1.xls" > > szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ > "Data Source=" & sConString & ";" & _ > "Extended Properties=Excel 8.0;" > > Set cnn = New ADODB.Connection > cnn.Open szConnect > Set cat = New ADOX.Catalog > Set cat.ActiveConnection = cnn > > Sheets("data").Range("B1:B100").ClearContents > > lRow = 1 > For Each tbl In cat.Tables > szTableName = tbl.Name > ''' Worksheet names always end in the "$" character. > If Right$(szTableName, 1) = "$" Then > Sheets("data").Cells(lRow, 2).Value = _ > Left$(szTableName, Len(szTableName) - 1) > lRow = lRow + 1 > End If > Next tbl > > > cnn.Close > Set cat = Nothing > Set cnn = Nothing > > End sub >
From: joel on 2 May 2010 07:31 The column headers are in Row 1 of each table. Using ADO method indexing starts at zero so you are lokking at row 0. For Each tbl In cat.Tables szTableName = tbl.Name ''' Worksheet names always end in the "$" character. If Right$(szTableName, 1) = "$" Then Sheets("data").Cells(lRow, "B").Value = _ Left$(szTableName, Len(szTableName) - 1) Set HeaderRow = tbl.Rows(0) ColCount = 3 for each col in HeaderRow.Cells Sheets("data").Cells(lRow, ColCount).Value = col ColCount = ColCount + 1 next col lRow = lRow + 1 End If Next tbl -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=200033 http://www.thecodecage.com/forumz
From: Tim Williams on 2 May 2010 14:32 See modified version of your code below. You'll need to look up the column type enum values here: http://www.w3schools.com/ADO/ado_datatypes.asp Tim '********************************************** Sub GetSheetNamesAndColumnTypes() Dim cnn As ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim c As ADOX.Column Dim lRow As Long Dim szBookName As String Dim szConnect As String Dim szTableName As String szBookName = ThisWorkbook.Path & "\db_test1.xls" szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & szBookName & ";" & _ "Extended Properties=Excel 8.0;" Set cnn = New ADODB.Connection cnn.Open szConnect Set cat = New ADOX.Catalog Set cat.ActiveConnection = cnn Sheet1.Range("B1:B100").ClearContents lRow = 1 For Each tbl In cat.Tables szTableName = tbl.Name ''' Worksheet names always end in the "$" character. If Right$(szTableName, 1) = "$" Then Sheet1.Cells(lRow, 2).Value = _ Left$(szTableName, Len(szTableName) - 1) For Each c In tbl.Columns Sheet1.Cells(lRow, 3).Value = c.Name Sheet1.Cells(lRow, 4).Value = c.Type lRow = lRow + 1 Next c lRow = lRow + 1 End If Next tbl cnn.Close Set cat = Nothing Set cnn = Nothing End Sub '*********************************************** "Tim Williams" <timjwilliams(a)comcast.net> wrote in message news:%232ZK6Kc6KHA.3880(a)TK2MSFTNGP04.phx.gbl... > Have you tried here ? > > http://msdn.microsoft.com/en-us/library/ms677529%28VS.85%29.aspx > > Tim > > "CG Rosen" <carlgran.rosen(a)telia.com> wrote in message > news:D1168E90-F0FE-4876-AB25-9EFA88CF4093(a)microsoft.com... >> >> >> Good day Group, >> >> Found the code below from this group that helps me to retrieve the Sheet >> names from >> a closed Workbook. Is it possible to get the name of the columns (fields) >> from >> a Worksheet in a closed Workbook in the same way? Is it posssible to get >> the format (data types) of each column? As new to this I�am grateful for >> some >> hints. >> >> Brgds >> >> CG Rosen >> >> ---------------------------------------------------------------------------------- >> >> 'Sub GetSheetNames() >> >> Dim cnn As ADODB.Connection >> Dim cat As ADOX.Catalog >> Dim tbl As ADOX.Table >> Dim lRow As Long >> Dim szBookName As String >> Dim szConnect As String >> Dim szTableName As String >> >> szBookName = "C:\Users\test1\db_test1.xls" >> >> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ >> "Data Source=" & sConString & ";" & _ >> "Extended Properties=Excel 8.0;" >> >> Set cnn = New ADODB.Connection >> cnn.Open szConnect >> Set cat = New ADOX.Catalog >> Set cat.ActiveConnection = cnn >> >> Sheets("data").Range("B1:B100").ClearContents >> >> lRow = 1 >> For Each tbl In cat.Tables >> szTableName = tbl.Name >> ''' Worksheet names always end in the "$" character. >> If Right$(szTableName, 1) = "$" Then >> Sheets("data").Cells(lRow, 2).Value = _ >> Left$(szTableName, Len(szTableName) - 1) >> lRow = lRow + 1 >> End If >> Next tbl >> >> >> cnn.Close >> Set cat = Nothing >> Set cnn = Nothing >> >> End sub >> > >
From: CG Rosen on 2 May 2010 23:24
Many thanks Tim, works perfect! brgds CG Rosen "Tim Williams" <timjwilliams(a)comcast.net> skrev i meddelandet news:uJv1oVi6KHA.6052(a)TK2MSFTNGP02.phx.gbl... > See modified version of your code below. > You'll need to look up the column type enum values here: > http://www.w3schools.com/ADO/ado_datatypes.asp > > Tim > > '********************************************** > Sub GetSheetNamesAndColumnTypes() > > Dim cnn As ADODB.Connection > Dim cat As ADOX.Catalog > Dim tbl As ADOX.Table > Dim c As ADOX.Column > Dim lRow As Long > Dim szBookName As String > Dim szConnect As String > Dim szTableName As String > > szBookName = ThisWorkbook.Path & "\db_test1.xls" > > szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ > "Data Source=" & szBookName & ";" & _ > "Extended Properties=Excel 8.0;" > > Set cnn = New ADODB.Connection > cnn.Open szConnect > Set cat = New ADOX.Catalog > Set cat.ActiveConnection = cnn > > Sheet1.Range("B1:B100").ClearContents > > lRow = 1 > For Each tbl In cat.Tables > szTableName = tbl.Name > ''' Worksheet names always end in the "$" character. > If Right$(szTableName, 1) = "$" Then > Sheet1.Cells(lRow, 2).Value = _ > Left$(szTableName, Len(szTableName) - 1) > For Each c In tbl.Columns > Sheet1.Cells(lRow, 3).Value = c.Name > Sheet1.Cells(lRow, 4).Value = c.Type > lRow = lRow + 1 > Next c > lRow = lRow + 1 > End If > Next tbl > > cnn.Close > Set cat = Nothing > Set cnn = Nothing > > End Sub > '*********************************************** > > > > > "Tim Williams" <timjwilliams(a)comcast.net> wrote in message > news:%232ZK6Kc6KHA.3880(a)TK2MSFTNGP04.phx.gbl... >> Have you tried here ? >> >> http://msdn.microsoft.com/en-us/library/ms677529%28VS.85%29.aspx >> >> Tim >> >> "CG Rosen" <carlgran.rosen(a)telia.com> wrote in message >> news:D1168E90-F0FE-4876-AB25-9EFA88CF4093(a)microsoft.com... >>> >>> >>> Good day Group, >>> >>> Found the code below from this group that helps me to retrieve the Sheet >>> names from >>> a closed Workbook. Is it possible to get the name of the columns >>> (fields) from >>> a Worksheet in a closed Workbook in the same way? Is it posssible to get >>> the format (data types) of each column? As new to this I�am grateful for >>> some >>> hints. >>> >>> Brgds >>> >>> CG Rosen >>> >>> ---------------------------------------------------------------------------------- >>> >>> 'Sub GetSheetNames() >>> >>> Dim cnn As ADODB.Connection >>> Dim cat As ADOX.Catalog >>> Dim tbl As ADOX.Table >>> Dim lRow As Long >>> Dim szBookName As String >>> Dim szConnect As String >>> Dim szTableName As String >>> >>> szBookName = "C:\Users\test1\db_test1.xls" >>> >>> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ >>> "Data Source=" & sConString & ";" & _ >>> "Extended Properties=Excel 8.0;" >>> >>> Set cnn = New ADODB.Connection >>> cnn.Open szConnect >>> Set cat = New ADOX.Catalog >>> Set cat.ActiveConnection = cnn >>> >>> Sheets("data").Range("B1:B100").ClearContents >>> >>> lRow = 1 >>> For Each tbl In cat.Tables >>> szTableName = tbl.Name >>> ''' Worksheet names always end in the "$" character. >>> If Right$(szTableName, 1) = "$" Then >>> Sheets("data").Cells(lRow, 2).Value = _ >>> Left$(szTableName, Len(szTableName) - 1) >>> lRow = lRow + 1 >>> End If >>> Next tbl >>> >>> >>> cnn.Close >>> Set cat = Nothing >>> Set cnn = Nothing >>> >>> End sub >>> >> >> > > > |