passing access recordset values to excel and using them

richey

New Member
Joined
Feb 17, 2002
Messages
7
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.aspx?scid=/support/excel/dao.asp
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top