|
I have something I have been using for some time. Basically you take a query via VBA, and then based on the size of the query (rows and columns) it is placed into an Excel spreadsheet just the same. What you do from there is up to you, for example, I utilize an Excel formatting module so I can clean up the report to my liking.
I took out all my surrounding code to make the important part stand out. I declare some public types, and then in a sub procedure store query data into an array, I finish with a second sub procedure which exports the data to Excel.
‘This first part is done publicly as you can see. 'Excel Objects Public WST As Object ' OLE automation object Public oExcel As New Excel.Application
Private Type ExlCell row As Long col As Long End Type
Then I get my data during a sub routine. _______________________________________ Sub ReportExport() '* Query recordset, store data into an array, export to Excel based by department '* Each sheet is formatted, and a summary report is also created. On Error GoTo ReportExport_Error: ‘Here we use our Private Type 'Our Excel type Dim StartingCell As ExlCell
‘Here I identify the starting location to place the data, in this case it is row 1 and column 1 oExcel.Workbooks.Add StartingCell.row = 1 StartingCell.col = 1
‘My query for my data, replace this with your own query! qryEmpString = ( _ "Select Employees.LName, Employees.FName, Employees.Shift, Employees.DateHired, " & _ "Departments.DepartmentName From qryCompleteEmployeeList " & _ "Where (((Employees.DeptID) = " & DeptArray(i) & ") AND ((Employees.Term)= No));")
‘My array which stores the query data, and will be used to export out to Excel 'Get employee data For row = 1 To rsEmp.RecordCount For col = 0 To rsEmp.Fields.Count - 1 EmpArray(row, col) = rsEmp.Fields(col).Value Next rsEmp.MoveNext Next
Prep StartingCell, NumRecs, rsEmp, EmpArray, SheetName '1) Send data to excel
Exit Sub
End Sub
This is the second sub procedure I use to send the data out to Excel, though you could depending upon your needs put this all into one procedure. I use the sub here because in my actual program I have other code that I have removed to make this easier to read. _____________________________________________________ Sub Prep(stCell As ExlCell, RecNum As Integer, SN As Recordset, TheArray As Variant, _ NameSheet As String)
Dim sReturnLink As String ‘This is the KEY! Note how we refer to the worksheet range, this literally takes your query and slaps into an Excel spreadsheet 'Prep the Excel Workbook oExcel.ActiveWorkbook.Sheets.Add Set WST = oExcel.ActiveWorkbook.Sheets(1) WST.Name = NameSheet 'Copy data out to Excel WST.Range(WST.Cells(stCell.row, stCell.col), _ WST.Cells(stCell.row + SN.RecordCount + 1, _ stCell.col + SN.Fields.Count)).Value = TheArray End Sub
Paste this into a module for easier reference. The private type and last sub are what you are mainly looking for. I showed the middle piece to show how to get your data.
Craig Gross Senior Financial Publisher Analyst Baker and Taylor Bridgewater NJ
-----Original Message-----
I have been banging my head on this one. I have a query that I need to get the results into Excel, then create a pivot table, then create a three line chart from the pivot table.
One successful attempt was to export the data into Excel, but then I get the message saying that "TableName" already exist after my first attempt. This will have to be done with automation over and over depending upon the user selections.
Has anyone done this before and already has a device in place?
I am running out of time and have been trying different options for over a week.
May God bless you beyond
your imagination! Yahoo!
FareChase - Search multiple travel sites in one click. |
- [AccessDevelopers] Access data to Excel Chart Lonnie Johnson
- Re: [AccessDevelopers] Access data to Excel Chart hal_mcgee
- RE: [AccessDevelopers] Access data to Excel Chart Craig Gross
