Add the field names from the database when this loads in Excel


This is in Excel
This code works but I want to add the field names from the database when this loads.  

Any help would be greatly be appreciated.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub OpenDB()
 Dim db As DAO.Database
 Dim ws As DAO.WorkSpace
 Dim rst As DAO.Recordset
 Dim i As Integer
 
 Cells.Clear
 MsgBox "Update TradeLimit"
 
 
 Set ws = DBEngine.WorkSpaces(0)
 Set db = ws.OpenDatabase _
 ("\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb", _
 False, False, "MS Access;PWD=mandatory")
 
 ''''''''''''''''''''''''''''''''''''''''''
 
 Set rst = db.OpenRecordset("Maintenance Table", dbOpenDynaset)
    i = 2
    With rst
        .OpenRecordset
   
 
 ''''''''''''''''''''''''''''''''''''''''''  
     'Add Field Names
     
   
 ''''''''''''''''''''''''''''''''''''''''''  
        If Not .BOF Then .MoveFirst
        Do While Not .EOF
            Cells(i, 1).Value = .Fields(0)  'CompanyNumber in First column
            Cells(i, 2).Value = .Fields(1)  'CompanyName in 0 column
            Cells(i, 3).Value = .Fields(18)  'TradeLimit Available
            Cells(i, 4).Value = .Fields(11)  'Volume 1
            Cells(i, 5).Value = .Fields(12)  'Volume 2
            Cells(i, 6).Value = .Fields(13)  'Volume3
            Cells(i, 7).Value = .Fields(14)  'Volume 4
           
            .MoveNext
            i = i + 1
        Loop
    End With

rst.Close
 db.Close


    Range("A2:C400").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal



End Sub
ca1358Asked:
Who is Participating?
 
leonstrykerCommented:
With rst
        .OpenRecordset
         '/ Add Field Names
         Cells(1, 1).Value = .Fields(0).Name
         Cells(1, 2).Value = .Fields(1).Name
         Cells(1, 3).Value = .Fields(18).Name
         Cells(1, 4).Value = .Fields(11).Name
         Cells(1, 5).Value = .Fields(12).Name
         Cells(1, 6).Value = .Fields(13).Name
         Cells(1, 7).Value = .Fields(14).Name
         If Not .BOF Then .MoveFirst
         Do While Not .EOF
            Cells(i, 1).Value = .Fields(0)  'CompanyNumber in First column
            Cells(i, 2).Value = .Fields(1)  'CompanyName in 0 column
            Cells(i, 3).Value = .Fields(18)  'TradeLimit Available
            Cells(i, 4).Value = .Fields(11)  'Volume 1
            Cells(i, 5).Value = .Fields(12)  'Volume 2
            Cells(i, 6).Value = .Fields(13)  'Volume3
            Cells(i, 7).Value = .Fields(14)  'Volume 4
            .MoveNext
            i = i + 1
        Loop
    End With
0
 
leonstrykerCommented:
This would give yo uthe name of the column:

rst.Fields(0).Name

BTW, this:

Range("A2:C400").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending

should work better than this:

Range("A2:C400").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

Leon
0
 
ca1358Author Commented:
I not sure where to put this,

I added the "rst.Fields(0).Name" (see following) but I get an error "compile error, invalid use of property"

Sorry, I learning as I go, thank you for you help!!

Sub OpenDB()
 Dim db As DAO.Database
 Dim ws As DAO.WorkSpace
 Dim rst As DAO.Recordset
 Dim i As Integer
 
 Cells.Clear
 MsgBox "Update TradeLimit"
 
 
 Set ws = DBEngine.WorkSpaces(0)
 Set db = ws.OpenDatabase _
 ("\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb", _
 False, False, "MS Access;PWD=mandatory")
 
 ''''''''''''''''''''''''''''''''''''''''''
 
 Set rst = db.OpenRecordset("Maintenance Table", dbOpenDynaset)
    i = 2
    With rst
        .OpenRecordset
     
         If Not .BOF Then .MoveFirst
        Do While Not .EOF
            Cells(i, 1).Value = .Fields(0)  'CompanyNumber in First column
            Cells(i, 2).Value = .Fields(1)  'CompanyName in 0 column
            Cells(i, 3).Value = .Fields(18)  'TradeLimit Available
            Cells(i, 4).Value = .Fields(11)  'Volume 1
            Cells(i, 5).Value = .Fields(12)  'Volume 2
            Cells(i, 6).Value = .Fields(13)  'Volume3
            Cells(i, 7).Value = .Fields(14)  'Volume 4
           
            .MoveNext
            i = i + 1
        Loop
''''''''''''''''''''''''''''''''''''''''''  
     'Add Field Names
     
    rst.Fields(0).Name

''''''''''''''''''''''''''''''''''''''''''  
    End With


rst.Close
 db.Close

Range("A2:C400").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending
0
 
ca1358Author Commented:
Thank you!!
Thank you!!
0
 
leonstrykerCommented:
You are welcome. Thanks for the grade. BTW, if your query returns fields in the same order as you want them to appear on the spreadsheet, then you can take advantage of the CopyFromRecordset method instead of the looping which you are doing now.  Example:

     With rst
        .OpenRecordset
         '/ Add Field Names
         Cells(1, 1).Value = .Fields(0).Name
         Cells(1, 2).Value = .Fields(1).Name
         Cells(1, 3).Value = .Fields(18).Name
         Cells(1, 4).Value = .Fields(11).Name
         Cells(1, 5).Value = .Fields(12).Name
         Cells(1, 6).Value = .Fields(13).Name
         Cells(1, 7).Value = .Fields(14).Name
         If Not .BOF Then .MoveFirst
    End With
    Cells(2, 1).CopyFromRecordset rst

You should also consider using ADO instead of DAO.

Leon

 

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.