SQL data from VBA - Sort into groups via heading.

  • Thread starter Thread starter djforgone
  • Start date Start date
D

djforgone

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....
 
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).Insert 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
 
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/DatabaseProgramming.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
 
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/DatabaseProgramming.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
Click to expand...

Hi Garry...

This message comes up when looking at the URL provided..
"The requested URL /conference/DatabaseProgramming.zip was not found on this server."
 
Hi Garry...
This message comes up when looking at the URL provided..
"The requested URL /conference/DatabaseProgramming.zip was not found
on this server."
Click to expand...

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
 
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
 
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
Click to expand...

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!
 
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!
Click to expand...

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
 
Back
Top