Set Sess0 = System.activesession

[talliaman]

I will admit that I do not know VBA so please bear with my ignorance. I work for a very small company and we do not have any experts in this area. Enough about that.
I have the following code that does a screen scrape from Reflections - Attachmate and its not working.
The error I get is Compile Error - Variable not defined......screen shot attached.
It highlights this line of code.

Set Sessions = System.Sessions

Here is all the code...

Public Sub StartProcess()

Dim oSheet As Worksheet
Dim bIsStartDate As Boolean
Dim sMonth As String
Dim sBeginningValue As String
Dim sReceiptDate As String
Dim sMonthPremiumIsPayingFor As String
Dim sContributions As String
Dim sPremiumTax As String
Dim sAdminFee As String
Dim sCostofInsurance As String
Dim sDisbursement As String
Dim sInterest As String
Dim lRowIndexMF As Long
Dim lRowIndexXL As Long

Dim sTransCode As String
Dim sCheck As String
Dim iTransaction_year As Integer

Dim sPrevMonth As String
Dim sPrevCostofInsurance As String


Set Sessions = System.Sessions



Set oSheet = ActiveSheet
lRowIndexMF = 13
lRowIndexXL = START_ROW

If bIsStartDate = True Then

Else
oSheet.Range("k6").Value = "0"
End If


Do
sCheck = Trim(Session.GetText(lRowIndexMF, 0, lRowIndexMF, 80))
Select Case sCheck
Case "": GoTo NxtLine
Case Else:
If (InStr(sCheck, "COST") > 0) Or (Not (IsNumeric(VBA.Left(sCheck, 4)))) Then
GoTo NxtLine
End If
End Select
lRowIndexXL = lRowIndexXL + 1
'Read Values
sMonth = Session.GetText(lRowIndexMF, 76, lRowIndexMF, 80)
'sBeginningValue = Session.GetText(lRowIndexMF, 1, lRowIndexMF, 6)
sReceiptDate = Session.GetText(lRowIndexMF, 0, lRowIndexMF, 4)
sMonthPremiumIsPayingFor = Session.GetText(lRowIndexMF, 12, lRowIndexMF, 14)
'sMonthPremiumIsPayingFor = Session.GetText(lRowIndexMF, 6, lRowIndexMF, 10)
sContributions = Trim(Session.GetText(lRowIndexMF, 26, lRowIndexMF, 35))
'On Error Resume Next
If Trim(sContributions) <> "" Then
sContributions = IIf(Right(Trim(sContributions), 1) = "-", -Mid(Trim(sContributions), 1, Len(Trim(sContributions)) - 1), Trim(sContributions))
End If
'On Error GoTo 0
sPremiumTax = Trim(Session.GetText(lRowIndexMF, 36, lRowIndexMF, 43))
If Len(Trim(sPremiumTax)) > 0 Then
sPremiumTax = IIf(Right(Trim(sPremiumTax), 1) = "-", -Mid(Trim(sPremiumTax), 1, Len(Trim(sPremiumTax)) - 1), Trim(sPremiumTax))
End If
sAdminFee = Trim(Session.GetText(lRowIndexMF, 44, lRowIndexMF, 49))
sCostofInsurance = Trim(Session.GetText(lRowIndexMF, 50, lRowIndexMF, 58))
sDisbursement = 0 'Session.GetText(lRowIndexMF, 1, lRowIndexMF, 6)
sInterest = Trim(Session.GetText(lRowIndexMF, 59, lRowIndexMF, 67))
sTransCode = Session.GetText(lRowIndexMF, 16, lRowIndexMF, 17)

'Debug.Print Format(VBA.Right(sMonth, 2) & "/01/" & VBA.Left(sMonth, 2), "mmm")
'Update Excel
If sMonth = "0000" Then
If lRowIndexXL = START_ROW Then
oSheet.Range("A" & lRowIndexXL).Value = "ERROR"
'sMonth = "ERROR"
GoTo NxtEntry
Else
'oSheet.Range("A" & lRowIndexXL).Value = oSheet.Range("A" & lRowIndexXL - 1).Value
sMonth = Format(oSheet.Range("A" & lRowIndexXL - 1).Value, "yy") & Format(oSheet.Range("A" & lRowIndexXL - 1).Value, "mm")
End If
End If
oSheet.Range("A" & lRowIndexXL).Select
oSheet.Range("A" & lRowIndexXL).Value = "'" & Format(VBA.Right(sMonth, 2) & "/01/" & VBA.Left(sMonth, 2), "mmm") & "-" & Format(VBA.Right(sMonth, 2) & "/01/" & VBA.Left(sMonth, 2), "yy")
NxtEntry:


'oSheet.Range("B" & lRowIndexXL).Value = sBeginningValue
'oSheet.Range("C" & lRowIndexXL).Value = Format(VBA.Mid(sReceiptDate, 2, 2) & "/" & VBA.Right(sReceiptDate, 2) & "/" & VBA.Left(sMonth, 2), "mm/dd/yyyy")
'oSheet.Range("D" & lRowIndexXL).Value = "'" & Format(VBA.Right(sMonthPremiumIsPayingFor, 2) & "/01/" & VBA.Left(sMonth, 2), "mmm") & "-" & Format(VBA.Right(sMonthPremiumIsPayingFor, 2) & "/01/" & VBA.Left(sMonth, 2), "yy")
oSheet.Range("C" & lRowIndexXL).Value = Format(VBA.Mid(sReceiptDate, 2, 2) & "/" & VBA.Right(sReceiptDate, 2) & "/" & GetTransactionYear(sMonth, sReceiptDate), "mm/dd/yyyy")
oSheet.Range("D" & lRowIndexXL).Value = "'" & Format(VBA.Right(sMonthPremiumIsPayingFor, 2) & "/01/" & VBA.Left(sMonth, 2), "mmm") & "-" & Format(VBA.Right(sMonthPremiumIsPayingFor, 2) & "/01/" & GetTransactionYear(sMonth, sMonthPremiumIsPayingFor), "yy")
oSheet.Range("E" & lRowIndexXL).Value = sContributions
oSheet.Range("F" & lRowIndexXL).Value = IIf(sPremiumTax = "", 0, -Val(sPremiumTax))
oSheet.Range("G" & lRowIndexXL).Value = IIf(sAdminFee = "", sAdminFee, -Val(sAdminFee))
If InStr(sCostofInsurance, ".") > 0 Then
oSheet.Range("H" & lRowIndexXL).Value = -sCostofInsurance
Else
oSheet.Range("H" & lRowIndexXL).Value = oSheet.Range("H" & lRowIndexXL - 1).Value
End If

If sPrevMonth = sMonth Then
If InStr(sPrevCostofInsurance, ".") = 0 Then
oSheet.Range("H" & lRowIndexXL).Value = 0
End If
End If

Select Case sTransCode
Case "31", "32", "34", "37":
sDisbursement = sContributions
oSheet.Range("E" & lRowIndexXL).Value = "0"
Case "05"
oSheet.Range("E" & lRowIndexXL).Value = "0"
End Select
oSheet.Range("I" & lRowIndexXL).Value = sDisbursement
oSheet.Range("J" & lRowIndexXL).Value = sInterest

sPrevMonth = sMonth
sPrevCostofInsurance = sCostofInsurance


Call EnterFormulas(lRowIndexXL, oSheet)
NxtLine:
If lRowIndexMF = 24 Then
lRowIndexMF = 13
'next page
Session.TransmitTerminalKey (rcHpF4Key)
Application.Wait (Now + TimeValue("00:00:02"))
Else
lRowIndexMF = lRowIndexMF + 1

End If

Loop While VBA.Trim((Session.GetText(0, 0, 0, 19))) <> "NOTHING TO SCAN" '"SCAN FOR MORE DATA"

MsgBox "Data Extraction has been completed"

End Sub

 

[SkipVought]

hi,

YES! You have [highlight #FCE94F]Option Explicit[/highlight]! YES, which is great! It tells you exactly what's wrong. You have not declared System & Session. They BOTH ought to be declared as Object. You don't need Sessions, rather...

Set Session = System.ActiveSession

But wait! There's more. Fix those two and recompile and you'll see more variables to declare. I can't really tell you how many more, because you have not posted each of your subroutines that are called in your code.

 

[talliaman]

I need to add another caveat, Reflections is being run through Citrix, I am assuming that will make a difference? I tried changing per the suggestion above and still get the same error. I can open Excel either on my desktop or thru Citrix and I get the same error.

Thank you for helping a non techy person!!

 

[SkipVought]

Well tell me exactly what you did before trying to compile?

 

[talliaman]

I changed this line...
Set Sessions = System.Sessions
to
Set Session = System.ActiveSession

Error stopped as it did before, variable not defined,
Again, thank you!

 

[SkipVought]

Well I also stated in my previous post, "Fix those two and recompile [highlight #FCE94F]and you'll see more variables to declare[/highlight].

 

[talliaman]

I am not getting any other compile errors, just the session error. But as I stated, I could be doing something wrong.

 

[SkipVought]

What does the error actually state and what statement of code is selected with this error?

 

[talliaman]

A popup that states Compile Error, cannot find Project or Library.
It highlights this line..
Set Session = System.ActiveSession
I also tried changing that line to
Set Session = System.ActiveSession

Again, this is through Citrix, not sure if that's relevant??

 

[SkipVought]

Where did you Set (assign) the System object? I do not see where this occurs. This is crucial!

 

[SkipVought]

You need to look in the system HELP menu to see how this ought to be done in Reflections.

 

[talliaman]

When I click on Tools - references in the VBA module, I see I am missing the Reflection for HP reference. Guessing this is of some importance??? How can I download that reference? Is it part of Office or part of REflections?

 

[SkipVought]

In The Attachmate Reflections application.

You are coding I'm the Reflections VB editor, yes?

 

[talliaman]

No, the VB is in Excel doing a screen scrape from Reflections. Long story short, the program worked perfectly until our division was sold off and we had to start administering ourselves. So for some reason something has changed. We use the same programs, but are now through Citrix. It worked flawlessly before but now has the bug where it cant seem to find the session.

 

[SkipVought]

Bottom line: you need to Set the System object, by use of the CreateObject() method, however that's done for your Attachmate Reflections system, which is in their HELP menu.

Call up your emulator. Go to the VB editor and there is where your HELP is.

 

[Andrzejek]

Just wondering… Do you have Reflections installed on the Citrix box where you run your app?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[talliaman]

Yes, both Excel and Citrix are running on the same box.

 

[Andrzejek]

Maybe I have a different understanding of what Citrix is.
Me and all of my users have their PC’s where they do their work. They all have access to Citrix servers where a lot of applications are also installed. So in my world when users access any Citrix box, their PC works pretty much like a terminal.
So if my application is written in VBA in Excel and uses other application (like Reflections) and needs to run on Citrix box, this Citrix box needs to have Excel and Reflections installed.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PeteG]

Just stumbled across this post and thought I'd add my take on it. In my world, being on Citrix also, we have some apps that appear to be installed as if they were local installations but others that are kind of 'virtually' installed (XenApp) - where it appears more akin to running a shortcut to a network location when launching it. My assumption (and forgive me if the previous answers have made this obvious anyway) is that you *used* to have a local reference to the Reflections object library on your machine - something like c:\program files\attachmate\reflections\.... that would have been ticked in Tools/References but now it simply isn't there anymore.

I know that doesn't answer the question but might help in some way! It might be possible to install Reflections locally instead, in order to get that object library back where it is expected to be (although part of the move to Citrix might have been to avoid such things so who knows....)