[AccessD] [dba-OT] Transfer field names to excel,

[Stuart McLachlan]

On 12 Oct 2017 at 14:43, Rocky Smolin wrote:

> Martin:
> 
> Lot of good tools there but didn't see one that would get the field
> names into an Excel sheet.  I'll keep looking, though.
> 
> 

Just knocked this up for you.  You can open the resultant  Tab delimited text file in Excel.

Function GetTableData() As Long
Dim Tbls() As String
Dim t As TableDef
Dim f As Field
Dim x As Long
Dim y As Long
Dim tblCount As Long
Dim maxfields As Long
'Get required array dimension
For Each t In CurrentDb.TableDefs
    If Left$(t.Name, 4) <> "mSys" Then
        tblCount = tblCount + 1
        y = t.Fields.Count
        If y > maxfields Then maxfields = y
    End If
Next
ReDim Tbls(1 To tblCount, 0 To maxfields)
'get table and field names into array
For Each t In CurrentDb.TableDefs
    If Left$(t.Name, 4) <> "mSys" Then
         x = x + 1
         y = 0
         Tbls(x, 0) = t.Name
        For Each f In t.Fields
            y = y + 1
            Tbls(x, y) = f.Name
        Next
    End If
Next
'Export to tab delimited file
Open CurrentProject.Path & "\TableFieldNames.txt" For Output As #1
For y = 0 To maxfields
    For x = 1 To tblCount - 1
        Print #1, Tbls(x, y) & Chr$(9);
    Next
    Print #1, Tbls(tblCount, y)
Next
Close #1
MsgBox "Done"
    
End Function