Nightmare in coding Excel module convert to Crystal

[aushia]

Hello All,
Well this is going to be a nightmare for a very basic newbie. I don't know what will be worse doing it or trying to explain it well enough to maybe get some help from the more expert experts here.

Here it goes.
I am working with Crystal 9.0 professional
I have two connections the first is an ODBC connection and the second is an Excel workbook.

The connection is no problem,

I work for a hospital and we have a database called the Discharge Abstract Database. In this database there are many fields but the only ones I need for this process is
(instito) (institfrom) (Age Group) (CaseMixGroup) (LengthofStay) (disp.)

In order to calculate the Weighted cases...this is just a term used to put a intensity amount to the types of cases seen in a hospital...the Ministry makes available this excel workbook that you can go into and dump all your data onto the sheet and it has this built in model that will calculate the PAC10riw. That PAc10riw is what I need, it is the Weighted case number I mentioned. Well it just seems silly to me to go to all the trouble of dumping the data 100,000 records into this sheet to get this calculation. But then I looked at the module behind the workbook, Oh my god, but if I could figure some way of getting crystal to look at the workbook and then just do the calculation looking at the live data...that just seems the best. I know I can't attach the workbook here but if you are into a challange it is at

http://www.mohltcfim.com

and is called Pac-10 Calculator for 2004/2005 and 2005/2006

Here is the module behind the sheets..it is written in visual basic...I am willing to work hard and learn but I just don't know where to begin but I really need this thing to work with crystal...

Function PAC10RIW(instto, instfrom, disp, cmg, los, age)
If age = 9 Then
age = 3
End If

instto = Application.WorksheetFunction.Trim(instto)
instfrom = Application.WorksheetFunction.Trim(instfrom)
disp = Application.WorksheetFunction.Trim(disp)
cmg = Application.WorksheetFunction.Trim(cmg)

Dim pac10Range As Range
Set pac10Range = Worksheets("PAC10").Range("A1:U479")
typriw = Application.WorksheetFunction.VLookup(cmg, pac10Range, Choose(age, 3, 7, 11), False)
bopdw = Application.WorksheetFunction.VLookup(cmg, pac10Range, Choose(age, 4, 8, 12), False)
rapdw = Application.WorksheetFunction.VLookup(cmg, pac10Range, Choose(age, 5, 9, 13), False)
cihipdw = Application.WorksheetFunction.VLookup(cmg, pac10Range, Choose(age, 6, 10, 14), False)
elos = Application.WorksheetFunction.VLookup(cmg, pac10Range, Choose(age, 15, 17, 19), False)
lostrim = Application.WorksheetFunction.VLookup(cmg, pac10Range, Choose(age, 16, 18, 20), False)

plxgrade = Application.WorksheetFunction.VLookup(cmg, pac10Range, 21, False)
If plxgrade = "1" Then
plxgradeN = 1
ElseIf plxgrade = "3" Then
plxgradeN = 2
Else
plxgradeN = 3
End If

If disp = "07" Then
riwexcl = 4
ElseIf disp = "06" Then
riwexcl = 3
ElseIf instfrom = "AT" Or instfrom = "AP" Or instfrom = "1" Or instto = "AT" Or instto = "AP" Or instto = "1" Then
riwexcl = 2
Else
riwexcl = 0
End If

If riwexcl = 0 And los > lostrim Then
riwexcl = 1
End If

If cmg = "996" Or cmg = "997" Then
riwexcl = 7
ElseIf cmg = "910" Or cmg = "912" Then
riwexcl = 5
ElseIf cmg = "999" Or cmg = "998" Then
riwexcl = 6
End If

If los = 0 Then
riwexcl = 7
End If

If los > 10 Then
los10 = 10
Else
los10 = los
End If

Dim pacccRange As Range
Set pacccRange = Worksheets("CostCurves").Range("B3:K13")

If riwexcl = 4 Then
pacCC = Application.WorksheetFunction.VLookup(los10, pacccRange, Choose(plxgradeN, 4, 7, 10), False)
End If

If riwexcl = 2 Then
If instfrom = "AT" Or instfrom = "AP" Or instfrom = "1" Then
pacCC = Application.WorksheetFunction.VLookup(los10, pacccRange, Choose(plxgradeN, 2, 5, 8), False)
ElseIf instto = "AT" Or instto = "AP" Or instto = "1" Then
pacCC = Application.WorksheetFunction.VLookup(los10, pacccRange, Choose(plxgradeN, 3, 6, 9), False)
End If
End If

If riwexcl = 0 Then
PAC10RIW = typriw
ElseIf riwexcl = 1 Then
PAC10RIW = typriw + (bopdw * (los - elos))
ElseIf riwexcl = 2 Or riwexcl = 4 Then
If los <= lostrim Then
PAC10RIW = cihipdw * los * pacCC
Else
PAC10RIW = typriw + (bopdw * (los - elos))
End If
ElseIf riwexcl = 3 Then
If los <= elos Then
PAC10RIW = cihipdw * los
ElseIf los > elos And los <= lostrim Then
PAC10RIW = cihipdw * elos
Else
PAC10RIW = typriw + (bopdw * (los - elos))
End If
ElseIf riwexcl = 7 Then
PAC10RIW = 0
Else
If los * bopdw < typriw Then
PAC10RIW = los * bopdw
Else
PAC10RIW = typriw
End If
End If

End Function



What a nightmare...any suggestions would be so appreciated...I have already tried pinching myself but I can't seem to wake up so I guess the nightmare continues.

 

[IdoMillet]

This is doable by creating a function in a UFL (User Function Library) that does the same thing.

Cheers,
- Ido




Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Thr trick seems to be to recreate the function in Crystal, however an altrernative might be to link (not import) The Excel and your database into an MS Access database (shame you didn't bother to state the database type, there may be other options - ODBC is not a database, it's a generic form of connectivity common to many databases).

Then you might ultimately e3xpose a query as the datasource for Crystal.

Ido's thought of a UFL might work, as might linking to the worksheets and using subrepoorts within Crystal.

I would 1st analyze importing the worksheet data into your database, and create the equivalent functionality on the database using either Views or a Stored Procedure. This would allow all future processes to use it.

-k

 

[IdoMillet]

Though I wasn't able to retrieve the spreadsheet (the web site requires an application process), I'm quite sure it provides a model rather than simply data. That's why an approach that attempts to use it as a data source would probably fail.

A UFL, on the other hand, would provide a function that accepts the 6 arguments, "drives" the spreadsheet, and returns the result.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[aushia]

Thank you all for your advice...I will try the UFL...I have to learn how to do that..but it won't be the first thing I have learned...it is the writing of the code that I find the most difficult...I will let you know how it goes...

Thanks again