WRQ Reflection/VBA copy read data to Excel

[ambertje]

Hello everybody,

I'm trying to read out text information out of a reflection session.
The lines are stored in strData and I can see all the textlines in the Locals window.

There is however a problem I can't solve no mather how hard I try.
My programming skills are these from a beginner.

After the textstrings are stored in the variable strData I would like to copy them into an Excel sheet.
If all the textlines are copy-d then I would like to send that sheet with an Email to a group of people. All this by using VBA

Can anybody help me please

Here's is an example of the code I've already have:

'Inlezen rapport SPRRA
'Sessie : Reflection Newbase sessie,cursor staat op het in te lezen rapport in het SPRRA scherm
'Resultaat : 1 dimensionele array van de lijnen van het betreffende rapport
Private Function fPick_Rep(Sessie As Reflection4.Session) As Variant
    Dim strData() As String, i As Integer, j As Integer, r As Integer, l As Long, t As Long, s As Long
    ReDim strData(0 To 0)
    With Sessie
        Dim strWacht(1 To 2) As String
        strWacht(1) = VBA.Chr(VBA.Asc(vbLf)) + ":"  'vbLf is een linefeed
        strWacht(2) = VBA.Chr(VBA.Asc(vbLf)) + "(EOF):"
        
        Sessie.DisplayMemoryBlocks = 10 'geheugen wordt hier vergroot
        Sessie.DisplayMemoryBlocks = 9 'hier terug verlaagd
        
        .Transmit "b"
        Do
            j = Sessie.Application.WaitForStrings(strWacht, 5)
            If j = 0 Then
                MsgBox "Er liep iets mis !"
                Exit Function
            Else
                r = .cursorRow 'kijkt waar de cursor staat en stopt dit in var.r
                l = UBound(strData) 'om de bovenkant van de file aan te geven
                s = 0
                ReDim Preserve strData(0 To l + r - VBA.IIf(l = 0, 1, 0) + VBA.Abs(Sessie.DisplayMemoryTopRow))
                t = VBA.IIf(l = 0, 0, 1)
                For i = Sessie.DisplayMemoryTopRow To r - 1
                    strData(l + s + t) = .GetText(i, 0, i, .DisplayColumns)
                    s = s + 1
                Next
            End If
       'Even schermhegeheugen resetten
            Sessie.DisplayMemoryBlocks = 10
            Sessie.DisplayMemoryBlocks = 9
            .Transmit VBA.Chr(13) 'character 13 is een carriage return
            DoEvents
        
        'Als j = 2 dan hebben we het einde bereikt
            If j = 2 Then
                .WaitForString "DETAIL"
                Exit Do
            End If
        Loop
    End With
    fPick_Rep = strData
End Function
Public Sub leesrapport()
    Dim varData As Variant
    varData = fPick_Rep(Me)
End Sub

Ambertje

 

[KinkyStef]

Hello Ambertje !

It was a nice surprise to see your message popping up on my desktop ! I wrote that code years ago.

It was ment as a basic routine to read retained reports from the Carrefour-WMS in Belgium by screenscraping in a WRQ-reflection session. I'm sure you work there ! It was simplified, explained and written for a certain Monique VK in a certain warehouse in Belgium. I suppose that you work for the same warehouse... I had some complex classes running with on the fly analysis of the specific reports (spares memory usage with big reports by writing directly to a database).

However, this routine was supposed to be simple. No analysis, a simple array that can be dumped to a textfile (ie. textstream), row by row. For writing to excel you can ask around in the company, some people wrote some scripts to read out/in excel (ie Peter S. and Tom C. and don't forget Karl S.!) and still work there on projects and IT ! Why don't you send it as a row by row textfile ?

For emailing you first need to write to a file so you can sent it ie. by the outlook object model. I know... everything should be done by VBA.

Good luck !

KinkyStef

 

[ambertje]

Hello Ambertje !

It was a nice surprise to see your message popping up on my desktop ! I wrote that code years ago.

It was ment as a basic routine to read retained reports from the Carrefour-WMS in Belgium by screenscraping in a WRQ-reflection session. I'm sure you work there ! It was simplified, explained and written for a certain Monique VK in a certain warehouse in Belgium. I suppose that you work for the same warehouse... I had some complex classes running with on the fly analysis of the specific reports (spares memory usage with big reports by writing directly to a database).

However, this routine was supposed to be simple. No analysis, a simple array that can be dumped to a textfile (ie. textstream), row by row. For writing to excel you can ask around in the company, some people wrote some scripts to read out/in excel (ie Peter S. and Tom C. and don't forget Karl S.!) and still work there on projects and IT ! Why don't you send it as a row by row textfile ?

For emailing you first need to write to a file so you can sent it ie. by the outlook object model. I know... everything should be done by VBA.

Good luck !

KinkyStef

Hello KinkyStef,

Indeed this code has been written by you.
I've presented this problem to the people you mentioned but it's puzzeling them to and so far nobody can make it work.
Can you give an example on how to do it? As I mentioned my programming skills are not that good.

Kind regards,
Ambertje

 

[KinkyStef]

Sorry, I can't help you explicitly. When I left your company your operational managment (=Erwin H.) forced an agreement not to help anyone in your company. Only through him, IT and projects. Those last two departments are part of your mothercompany. I know, it's a strange company where individual interests take precedence over those from the company. Your HR-manager confirmed that argreement because of budgetary issues.

I'll contact the right people within your company and will contact my contacts within Carrefour about that problem. Screenscraping a terminal emulator to excel should be a last option. Generating reports directly from their database is more reliable and cost-efficient and can be implemented on an national scale for al warehouses (ie Nijvel and Kontich, also third party).

I know that you and your co-workers spent a lot of time on such copy-paste tasks and that automatisation will give you more spare time. But don't forget... if you spend less time... carrefour will spend less money because that time is agreed in their contract... your company will gain less money... some people will not be pleased!



You'll hear from this !

Greetings,


Kinkystef

 

[ambertje]

Hi KinkyStef,

Thanks for taking the time to look at the problem.

Has anyone else a clue on how to solve this problem?
It's not an urgent mather.

Ambertje

 

[Ruddles]

[FONT=Courier New]Public Sub leesrapport()[/FONT]
[FONT=Courier New]    Dim varData As Variant[/FONT]
[FONT=Courier New]    varData = fPick_Rep(Me)[/FONT]
[FONT=Courier New][B][COLOR=red]    Range("A1").Resize(UBound(varData) + 1, 1) [/COLOR][COLOR=red]= Application.Transpose(varData)[/COLOR][/B][/FONT]
[FONT=Courier New]End Sub[/FONT]

After you call fPick_Rep, the array varData holds the lines of data you want in the worksheet. Add the line of code I've shown in red and that should copy the array to your worksheet.

 

[ambertje]

After you call fPick_Rep, the array varData holds the lines of data you want in the worksheet. Add the line of code I've shown in red and that should copy the array to your worksheet.

Hi Ruddles,

Thanks a million , with your help and a few changes like a small loop and so one it now works perfectly.

Ambertje

 

[Ruddles]

Excellent - another happy customer! Please will you let me have an adress where I can send the invoice?

 

[ambertje]

Excellent - another happy customer! Please will you let me have an adress where I can send the invoice?

Hello Ruddles,

What do you mean with adress?

I have one more little question, after running the code I see that the layout in excel is pore. I would like to try it in a word application.
Is there something like the Transpose function for word?

Kind regards,
Ambertje

 

[Ruddles]

What do you mean with adress?

Sorry, it was a joke. I was joking that I would be sending you an invoice for payment. Just ignore me!

I have one more little question, after running the code I see that the layout in excel is pore. I would like to try it in a word application.
Is there something like the Transpose function for word?

I have little experience of automation in Word so I'm not the best person to ask. There may be a simple way of copying the array to a Word table or you may have to create the table and then write simple For i = 0 to UBound(varData) loop to copy the values one at a time.

Sorry I can't be more help on this question.