[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-08
5
Medium Priority
?
239 Views
Last Modified: 2010-05-18

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
0
Comment
Question by:ca1358
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 16632082
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
 

Author Comment

by:ca1358
ID: 16632971
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 16633008
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
 

Author Comment

by:ca1358
ID: 16633084
Thank you!!
Thank you!!
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 16633118
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month17 days, 21 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question