I have an Excel spreadsheet template that I want to have populated with information from MS Access. I want the data to come from the tables and queries. Any examples of how to do this or sites to visit which explain in detail???
Well, I want this to be an automatic process for the user.
If they are in Access and the select a button that I have to export to Excel.. that is what I need to know, how to do that step.. Right now, if I export to Excel it just puts the data in table format in Excel...
I want to have it open Excel and place selected data on selected cells in the Excel Spreadsheet...
You are probably looking for something like this.....
Create a table (tbltest) with the fields (field1,field2,field3) and put this code under a button
Obs MS excel object lib. must be selected under tools-references
To format the excel sheet as it should be... you can learn about how to by
Go to excel - record a macro - do stuff - end record
click alt+11 and go to module... there is the vba code for what you just did..
Just step to the code and modify as you see fit....
Dim objXL As Object
Dim objActiveWkb As Object
Dim db As DAO.Database 'Might have to remove the DAO.
Dim rs As DAO.Recordset 'Might have to remove the DAO.
Dim Row As Integer
Row = 3 'This is a counter for calculations
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objActiveWkb = objXL.Application.ActiveWorkBook
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)
'Heading for meter info
.Worksheets(1).Cells(2, 3) = "Heading1"
.Worksheets(1).Cells(2, 4) = "Heading2"
.Worksheets(1).Cells(2, 5) = "Heading3"
'lookup values on meter
'Deletes the 2 other sheets
.Application.DisplayAlerts = False
.Application.DisplayAlerts = True
'Names the sheet and sett some page propertie values
.Worksheets(1).PageSetup.Orientation = xlLandscape
.Worksheets(1).PageSetup.RightFooter = "&D"
While rs.EOF <> True
.Worksheets(1).Cells(Row, 3) = rs!field1
.Worksheets(1).Cells(Row, 4) = rs!field2
.Worksheets(1).Cells(Row, 5) = rs!field3
Row = Row + 1
Does anyone know of an attachment to perform this function. I also would like to take data from an access form, table or query and export automatically into excel and use excel as a form to email and get information and have it returned. The reason I would like to go with excel is so I can lock the field that have the data in it from access then the emailed copy can not be modified.