| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL data from VBA - Sort into groups via heading.
Hi Everyone...
I am trying to figure out how to go about doing this. I want to build an Excel Finance Reporting Template that will import GL transactions from an Access Database based on a cost center. Thus Cost Centre in spreadsheet = Cost Center in Excel loop. That part I've done before and just need to manipulate the code for it to make it work. What I need assistance with is how to 'group' the transactions that are being obtained from GL table in Access by heading. For example: H1 = EMPLOYEE BENEFITS EXPENSE - - rows relating to employee benefits expense here - - - H1 = SUPPLIES AND SERVICES - - rows relating to supplies and services here - - - and so forth.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
I'm also having troubles (at the moment) with the following code...
Sub import_GL_Transactions() Dim conn As New Connection, rec As New Recordset Dim ws As Worksheet Dim sql$, i& ' advise which worksheet that is going to have the exported transactions placed Set ws = ThisWorkbook.Worksheets("GL_TRANS") ' 'create the data connection conn.Open "Provider=microsoft.ACE.oledb.12.0;" + _ "Data Source=" + ThisWorkbook.Path + "\OEPA Fiancial Database.accdb" sql = "Select * " & _ " FROM GL_DATABASE WHERE PERIOD_NAME = " & ws.Range("PERIOD.QUERY").Value & _ " AND CC = " & ws.Range("CCA.QUERY").Value & _ " ORDER BY POSTED_DATE " Dim rng As Range, Cell As Range Set Cell = ws.Range("A10") Do While Cell.Value < "TOTALS" If rng Is Nothing Then Set rng = Cell Else Set rng = Union(Cell, rng) End If Set Cell = Cell.Offset(1) Loop If Not rng Is Nothing Then rng.EntireRow.Delete End If rec.Open sql, conn, adopenstatic ws.Range("A6").EntireRow.Resize(rec.RecordCount).I nsert Shift:=xlDown While Not rec.EOF i = i + 1 ws.[A10].Cells(i) = rec!GL_CODE ws.[B10].Cells(i) = rec!PERIOD_NAME ws.[C10].Cells(i) = rec!POSTED_DATE ws.[D10].Cells(i) = rec!BATCH ws.[E10].Cells(i) = rec!JOURNAL ws.[F10].Cells(i) = rec!Line ws.[G10].Cells(i) = rec!ACCOUNTED_DR ws.[H10].Cells(i) = rec!ACCOUNTED_CR ws.[I10].Cells(i) = rec!AMOUNT ws.[J10].Cells(i) = rec!Description ws.[K10].Cells(i) = rec!Source ws.[L10].Cells(i) = rec!SOURCE_DATE ws.[M10].Cells(i) = rec!AP_INVOICE_NAME ws.[N10].Cells(i) = rec!AP_SUPPLIER_NAME ws.[O10].Cells(i) = rec!DEPT ws.[P10].Cells(i) = rec!CC ws.[Q10].Cells(i) = rec!AC ws.[R10].Cells(i) = rec!SER ws.[S10].Cells(i) = rec!ACT ws.[T10].Cells(i) = rec!RES ws.[U10].Cells(i) = rec!PRO ws.[V10].Cells(i) = rec!JOB rec.movenext Wend rec.Close: conn.Close End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
You should both be loading data into recordsets, then dump the
recordset into the worksheet at the desired location/position. Looping takes way too long and requires an inordinate amount of code when there's many fields. For example, you can put the entire recordset into a worksheet as follows... Range("A1") = Application.CopyFromRecordset(rsData) ...where the result will be all the rows/cols in the worksheet exactly the same as they are in the recordset. Note that you may need to do some formatting fixups, but getting the data quickly where you want it is a snap. To insert the next recordset on the same sheet just ref an empty row below the existing data. For comprehensive instruction/samples of how to properly manage database records in Excel... http://www.appspro.com/conference/Da...rogramming.zip -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
On Tuesday, 10 September 2013 13:19:44 UTC+8, GS wrote:
You should both be loading data into recordsets, then dump the recordset into the worksheet at the desired location/position. Looping takes way too long and requires an inordinate amount of code when there's many fields. For example, you can put the entire recordset into a worksheet as follows... Range("A1") = Application.CopyFromRecordset(rsData) ..where the result will be all the rows/cols in the worksheet exactly the same as they are in the recordset. Note that you may need to do some formatting fixups, but getting the data quickly where you want it is a snap. To insert the next recordset on the same sheet just ref an empty row below the existing data. For comprehensive instruction/samples of how to properly manage database records in Excel... http://www.appspro.com/conference/Da...rogramming.zip -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Hi Garry... This message comes up when looking at the URL provided.. "The requested URL /conference/DatabaseProgramming.zip was not found on this server." |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
Hi Garry...
This message comes up when looking at the URL provided.. "The requested URL /conference/DatabaseProgramming.zip was not found on this server." I've sent an email to Rob (the author) asking if there's a problem with this! I'll report back his reply... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
Ok, Rob rebuilt his website and didn't see any point to keeping old
conference material around. He has restored it to the original location and the link I posted now works! Good luck! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
On Wednesday, 11 September 2013 04:09:53 UTC+8, GS wrote:
Ok, Rob rebuilt his website and didn't see any point to keeping old conference material around. He has restored it to the original location and the link I posted now works! Good luck! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Excellent stuff!!! Thanks heaps. I've just done a parameter query and am trying to see how I can go about grouping the results. Sincerely appreciated! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL data from VBA - Sort into groups via heading.
On Wednesday, 11 September 2013 04:09:53 UTC+8, GS wrote:
Ok, Rob rebuilt his website and didn't see any point to keeping old conference material around. He has restored it to the original location and the link I posted now works! Good luck! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Excellent stuff!!! Thanks heaps. I've just done a parameter query and am trying to see how I can go about grouping the results. Sincerely appreciated! Thanks for the feedback. Best wishes in your endeavors! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort a database without also sorting the column heading? | Excel Discussion (Misc queries) | |||
| sort by groups in excel | Excel Discussion (Misc queries) | |||
| Sort Groups of Cells | Excel Worksheet Functions | |||
| Can I set up a sort by clicking on table a heading | Excel Worksheet Functions | |||
| Selecting in groups within a sort | Excel Programming | |||
Linear Mode
