passing access recordset values to excel and using them
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: passing access recordset values to excel and using them

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Manchester, England
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi

    for example, I have in Access a record with field names 'ID' 'Section' and 'frequency'
    The ID is 001
    The Section is Human Resources
    The frequency is quarterly

    How do I?

    i) As the section is Human Resources - when I click a button it opens the excel document called 'Human_action_points.xls'

    ii) create a new worksheet called Human_001 (i.e. the section + ID)

    iii) copy the recordset values to the newly created worksheet

    iv) as the frequency is quarterly - select the already created worksheet 'quarterly' - and copy the table that is there and paste it onto out newly created worksheet

    iv) not allow the user to skip through other worksheets

    v) after saving, anytime the user opens access and they click the 'button' on record 001 it opens the exact worksheet Human_001

    I know its alot, but any help much appreciated
    Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2003
    Location
    United Kingdom
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extractin data

    Have you used MS Query before, would that help?

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Here's some code that will allow you to see how to modify/download data from Access To Excel.

    Sub UpdateRecordsQuery()
    Dim Db As database
    Dim Qd As QueryDef
    Dim Rs As Recordset
    Dim qdParmQD As QueryDef
    Dim SQL As String
    Dim i As Integer

    ' Set your database object. You may need to change the path to match
    ' where Microsoft Office is installed.
    Set Db = _
    workspaces(0).OpenDatabase("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")

    ' 1. Create a PARAMETERS clause string.
    SQL = "PARAMETERS [CountryWanted] TEXT; "

    ' 2. Create a SELECT statement.
    SQL = SQL & "SELECT DISTINCTROW * " & _
    " FROM Customers" & _
    " WHERE (Customers.Country =[CountryWanted]) "

    ' 3. Create a named QueryDef object with your SQL statement.
    Set Qd = Db.CreateQueryDef("Find Customers", SQL)

    ' 4. Set the QueryDef object parameters.
    Set qdParmQD = Db.querydefs("Find Customers")
    qdParmQD("CountryWanted") = "Germany"

    ' 5. Execute the QueryDef.
    Set Rs = qdParmQD.OpenRecordset()

    ' 6. Issue a MoveLast followed by a MoveFirst
    Rs.MoveLast
    Rs.MoveFirst

    ' 7. Set up a loop that will modify each record in the recordset.
    For i = 1 To Rs.RecordCount
    Rs.Edit
    Rs("Region") = "Europe"
    Rs.Update
    Rs.MoveNext
    Next i

    ' At this point, the database has been modified. The rest of this
    ' code displays the data on a worksheet. This is not necessary to
    ' complete the operation.

    ' 8. Collect field names.
    For i = 0 To Rs.Fields.Count - 1
    Sheets("Sheet1").Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next i
    Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), _
    Sheets("Sheet1").Cells(1, Rs.Fields.Count)).Font.Bold = True

    ' 9. Issue a MoveFirst to move to the beginning of the recordset.
    Rs.MoveFirst

    ' 10. Use CopyFromRecordset to move the data onto the worksheet
    Sheets("Sheet1").Range("A2").CopyFromRecordset Rs

    ' 11. Select the sheet that data was written to and autofit the
    ' column widths.
    Sheets("Sheet1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit

    ' Select cell A1.
    Range("A1").Select

    ' 12. Clean up and delete the QueryDef that was just created. This
    ' removes it from the database. Then close the objects.
    Db.querydefs.Delete "Find Customers"
    Qd.Close
    Rs.Close
    Db.Close

    End Sub


    You may also want to check out this info from MicroSoft :http://support.microsoft.com/default.../excel/dao.asp
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com