Pivot Tables - Multiple Ranges

[Gr8lyConfused]

Is there a way to use pivot tables in Excel to accomplish the same result as
an MS-Access process to "join" two tables on matching fields? For example,
I have one range of data that contains employee time card information that
includes a costing code for each time entry. I have a separate data range
that contains, for each costing code, one or more records containing
percentages that are used to allocate labor to specific business units. The
desired result is a table or data range that contains all of the records from
the time card range and all matching records from the allocation table.

I've been able to build the result that I want using VBA code and vlookups
but I'm looking for a better way. I've been researching Data consolidations
and pivot tables with multiple ranges to see if this might work. So far, it
seems that these processes in Excel are simply to combining data rather than
simulating a join. Is there a way to do this with pivot tables or is code
the only answer?

Thanks in advance for any input!!!

 

[Tim Williams]

You might be able to adapt this to work for you.
Need to add a reference to the ADO library to your VBA project.

Tim


'***********************************
Sub TestJoin()

Const S_TEMP_1 As String = "SQLtempTable1"
Const S_TEMP_2 As String = "SQLtempTable2"

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath

Dim rng1 As Range, rng2 As Range

Set rng1 = Sheet1.Range("A1:B6")
Set rng2 = Sheet2.Range("A1:B16")

Dim sSQL As String

sSQL = "select t1.ID, t1.COL1, t2.COL2 " & _
" from SQLtempTable1 t1, SQLtempTable2 t2 " & _
" where t1.ID = t2.ID"

'delete the range names and re-apply
On Error Resume Next
ThisWorkbook.Names.Item(S_TEMP_1).Delete
ActiveWorkbook.Names.Item(S_TEMP_2).Delete
On Error GoTo 0
ThisWorkbook.Names.Add Name:=S_TEMP_1, RefersToLocal:=rng1
ThisWorkbook.Names.Add Name:=S_TEMP_2, RefersToLocal:=rng2

sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties=""Excel 8.0;HDR=Yes"""

Set oRS = oConn.Execute(sSQL)

If Not oRS.EOF And Not oRS.BOF Then
Sheet3.Range("A2").CopyFromRecordset oRS
Else
MsgBox "No records found"
End If

End Sub
'***************************************

 

[Gr8lyConfused]

Thanks Tim! This is great stuff. It looks like the modifications I would
have to make would be in setting the ranges, tailoring the SQL select
statement to match my data and maybe the Conn.Open statement.

Would the ADO library be "Microsoft ADO Ext. 2.7 for DDL and Security"?
This is the only one I could find in my available references containing
"ADO".

Thanks so much Tim!

Rick

 

[Gr8lyConfused]

Ok, now I'm finding several ActiveX Data Object libraries. The one that
looks like it has real promise is "ActiveX Data Objects Recordset 2.7
Library".

 

[Tim Williams]

"Microsoft ActiveX Data Objects 2.7 Library"

Earlier versions (eg. 2.5, 2.6) should also work.

Tim

 

[Gr8lyConfused]

Tim, I got this to work like I wanted on test data! This is powerful,
powerful stuff. I experimented with various types of SQL statements to
select and sum data ranges that I can use in other places in the application
as well. This certainly seems far superior to the VBA coding that I was
doing that, at times, seemed like I was building the result one brick at a
time. Thanks again!!!!!

 

[Tim Williams]

Glad to hear it was useful.

Tim

Tim, I got this to work like I wanted on test data! This is powerful,
powerful stuff. I experimented with various types of SQL statements to
select and sum data ranges that I can use in other places in the
application
as well. This certainly seems far superior to the VBA coding that I was
doing that, at times, seemed like I was building the result one brick at a
time. Thanks again!!!!!

 

[Gr8lyConfused]

I'm using the SQL approach recommended above in a variety of ways to match,
update and sum data in ranges. While I'm close to getting the results I
want, a problem has come up that I wasn't experincing prior to using ADO/SQL.
The problem is that, while testing the program, I often have to delete a
few thousand records in my worksheet prior to testing a modified version of
the program. Deleting these records now takes an extraordinarily long time.
Sometimes an hour or longer. So long that I've tried to use cntl-break to
break out of the code to no avail. After the records finally delete and I
try my program again, Excel seems to be randomly recognizing cntl-breaks to
stop execution of the VBA code when I haven't hit a cntl-break. Re-starting
Excel doesn't seem to help. Re-starting Window (XP) does seem to get rid of
the cntl-break problem but doesn't help with the time issue of deleting
records. One more piece of background information is that my VBA code is
getting quite lengthy. Since I'm re-working an existing program, I've copied
all of the original subs from my old program into the new one so that any
subs that I can reuse are readily available. My questions are:

1) Are the problems that I'm experiencing more likely related to using ADO
or the size of the program?

2) If the cause is likely using ADO, is there something that should be done
to free or clean up memory after each use to keep from causing performance
issues with deleting records?

Any help would be greatly appreciated.

Thanks in advance for your help.

 

[Tim Williams]

Hard to say exactly what's going on. How are you deleting your records ?
Do you have any sheet-level event handlers which might be firing during the
delete ?

If you're running the delete via code, try turning off Events and
Calculation before doing the delete, then turn them back on after.

with Application
.EnableEvents = False
.Calculation = xlCalculationManual
.Screenupdating = False
end with

'do deletes

with Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.Screenupdating = True
end with

If that doesn't work you'll need to provide some code.

If you'd like to mail me the workbook I will take a look.
timxxjxxwilliams (remove the x's) at gmail.com

Tim

 

[Gr8lyConfused]

Tim, I'm not aware of any sheet events that are programmed into my code.
But, I think you're right that the cntl-break issue is related to that
somehow. One of the places that the code would break was on"Loop" statement
after timer related DoWhile section looped around a DoEvents statement. So,
it has to be related to events.

I turned on manual calculation for the spreadsheet and the time required to
delete records seem to have returned to normal for the time being anyway.
If the problem shows up again I'll post some code. Thank you for your help
and expertise.

Rick