Excel VBA to Access DB

[jstevens]

I would like to transpose the fields from a table so that the outcome is a vertical list of field names in an Excel Sheet.

I can connect to an Access database successfully and using this snippet of code I can produce a horizontal list of field names.

Code: Select all

Select *
From tblMyTable
Where 1 =0

Your suggestions are appreciated,
John

[HansV]

What you post is just a SQL statement, it's not clear what code you use to produce the list of field names. I assume that you open a recordset on the SQL statement.
The VBA help for the CopyRecordset method of the Range object has the following example to place the field names in a horizontal list:

Code: Select all

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next

To create a vertical list, you'd change this to

Code: Select all

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(iCols + 1, 1).Value = rs.Fields(iCols).Name
Next

If you need more help, please provide some details.