passing access recordset values to excel and using them

[richey]

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

[David_UK]

Have you used MS Query before, would that help?

[Nimrod]

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