Scraping text and Exporting to text file

[CompuDork]

I got a question. I have a macro that scrapes data from one screen, switches to another screen, and then exports to a text file. I use the GetString command to scrape the data from one screen, but when I switch screens, it seems to act like the macro is passing a reference instead of copying the string into a variable. Is there a workaround?

 

[SkipVought]

Hi,

Need to see your code.

Skip,
[sub]
[red][/red]
[/sub]

 

[WinblowsME]

Here's code I use to scrape info from multiple pages.

Declare Sub Wait()

GLOBAL Sys as Object
GLOBAL Sess as Object

Sub Main()
   Dim outfile As String, aline As String
   Dim i as Integer, iRows As Long, iCols As Long

   outfile = "C:\Documents and Settings\All Users\Documents\Attachmate\Macros\Output\Scrape.txt"

   Set Sys = CreateObject("Extra.System")

   If Sys Is Nothing Then
      MsgBox ("Could not create Extra.System...is E!PC installed on this machine?")
      Exit Sub
   End If

   Set Sess = Sys.ActiveSession

   If Sess Is Nothing Then
      MsgBox ("No session available...stopping macro playback.")
      Exit Sub
   End If

   ' ###

   iRows = Sess.Screen.Rows
   iCols = Sess.Screen.Cols

   Open outfile For Output As #1
      Do
         For i = 1 To iRows
            aline = UCase(Trim(Sess.Screen.GetString(i, 1, iCols)))

            Print #1, aline
         Next i

         ' F8 key to skip to the next page
         Sess.Screen.SendKeys ("<Pf8>")

         Wait
         
      ' YOU'LL NEED ANOTHER CRITERIA TO STOP THE LOOP!
      ' If line 24 contains "LAST PAGE," stop the loop.      
      Loop While Trim(Sess.Screen.GetString(24, 1, iCols)) = "LAST PAGE"
   Close #1
End Sub

Sub Wait()
   Do While Sess.Screen.OIA.Xstatus <> 0
      DoEvents
   Loop
End Sub

 

[IknowMe]

Code looks sound, have you stepped through to identify where your issue occurs? What do you mean by trying to pass a reference?

what happens when...

For i = 1 To iRows
aline = UCase(Trim(Sess.Screen.GetString(i, 1, iCols)))
msgbox aline
Print #1, aline
Next i


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[CompuDork]

I was using set instead of getstring... But WinBlows answered another part of my question. Thanks for the help.

 

[curri23]

I need help for a similar macro (in fact part of it has been taken from WinblowsME). I need to send data from xcel to xtra, send the screenprint to txt and start over for x time while I have data in excel to input in xtra. In the end I expect to have a consolidated txt file where I see one screen after another. At the moment I'm only getting 1 single screen in the txt file.


Global Sys As Object
Global Sess As Object

Sub Main()
Dim outfile As String, aline As String
Dim i As Integer, iRows As Long, iCols As Long

outfile = "C:\Temp\File.txt"

Set Sys = CreateObject("Extra.System")

If Sys Is Nothing Then
MsgBox ("Could not create Extra.System...is E!PC installed on this machine?")
Exit Sub
End If

Set Sess = Sys.ActiveSession

If Sess Is Nothing Then
MsgBox ("No session available...stopping macro playback.")
Exit Sub
End If

' ###

iRows = Sess.Screen.Rows
iCols = Sess.Screen.Cols


Dim Screen As Object
Dim system As Object
Dim Sess0 As Object
Dim Sessions As Object
Dim timeout As Integer
timeout = 500

Set system = CreateObject("EXTRA.System")
Set Screen = system.ActiveSession.Screen
Set Sessions = system.Sessions
Set Sess0 = system.ActiveSession

a = 2
valorabsoluto = Range("G" & a).Value
sucursal = Range("D" & a).Value
debehaber = Range("H" & a).Value
While valorabsoluto <> ""

'AppActivate "SAN - EXTRA! X-treme"
AppActivate "SESSION1 - EXTRA! X-treme"
SendKeys "{F9}"
Sess0.Screen.WaitHostQuiet (timeout)
SendKeys "{F3}"
Sess0.Screen.WaitHostQuiet (timeout)
SendKeys "82"
SendKeys "~"
Sess0.Screen.WaitHostQuiet (timeout)
SendKeys "1"
SendKeys "~"
Sess0.Screen.WaitHostQuiet (timeout)
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "1"
SendKeys "~"
Sess0.Screen.WaitHostQuiet (timeout)
Sess0.Screen.WaitHostQuiet (timeout)

'íniciamos intercentro'
SendKeys "0049"
SendKeys sucursal
SendKeys "N"
SendKeys debehaber
SendKeys valorabsoluto
SendKeys "{TAB}"

Sess0.Screen.PutString "EUR", 8, 58
Sess0.Screen.PutString "719", 9, 27
Sess0.Screen.PutString "ENV.DOCUMENTACION APTE.CORRESPONDIDO POR", 10, 27
SendKeys "{TAB}"
Sess0.Screen.PutString "MMPP SIGUIENDO SUS INSTRUCCIONES", 11, 27

Sess0.Screen.PutString "ENV.DOCUMENTACION APTE.CORRESPONDIDO POR", 19, 27
SendKeys "{TAB}"
Sess0.Screen.PutString "MMPP SIGUIENDO SUS INSTRUCCIONES", 20, 27
Sess0.Screen.PutString "NO", 18, 63
SendKeys "~"
Sess0.Screen.WaitHostQuiet (timeout)
'SELECCIONA CTA INTERNA
SendKeys "{F10}"
Sess0.Screen.WaitHostQuiet (timeout)
SendKeys "{TAB}"
SendKeys "X"
SendKeys "~"
Sess0.Screen.WaitHostQuiet (timeout)
SendKeys "~"
Sess0.Screen.WaitHostQuiet (timeout)
Open outfile For Output As #1
Do
For i = 1 To iRows
aline = UCase(Trim(Sess.Screen.GetString(i, 1, iCols)))
'MsgBox aline

Print #1, aline
Next i
a = a + 1
valorabsoluto = Range("G" & a).Value
sucursal = Range("D" & a).Value
debehaber = Range("H" & a).Value
' F8 key to skip to the next page
'Sess.Screen.SendKeys ("<Pf8>")

'Wait

' YOU'LL NEED ANOTHER CRITERIA TO STOP THE LOOP!
' If line 24 contains "LAST PAGE," stop the loop.
Loop While Trim(Sess.Screen.GetString(24, 1, iCols)) = "LAST PAGE"



Close #1
Wend

End Sub

 

[CompuDork]

One thing that will help you right off the bat is changing your output statement.

Currently, you have:
Open outfile For Output As #1

Change to:

Open outfile For Append As #1

Output means to replace all the stuff in the text file with new stuff. Append means add to the bottom of the text file. Also since your looking to add more screens to the text file, you may want to print a blank line after each screen.

So, After your statement :


Next i


Put:
Print #1, " "




So your code looks Like this :


Open outfile For Append As #1
Do
For i = 1 To iRows
aline = UCase(Trim(Sess.Screen.GetString(i, 1, iCols)))
'MsgBox aline

Print #1, aline
Next i
Print #1, " "

a = a + 1
valorabsoluto = Range("G" & a).Value
sucursal = Range("D" & a).Value
debehaber = Range("H" & a).Value
' F8 key to skip to the next page
'Sess.Screen.SendKeys ("<Pf8>")

'Wait

' YOU'LL NEED ANOTHER CRITERIA TO STOP THE LOOP!
' If line 24 contains "LAST PAGE," stop the loop.
Loop While Trim(Sess.Screen.GetString(24, 1, iCols)) = "LAST PAGE"



Close #1
Wend

End Sub

 

[curri23]

Many thanks, Compudork. The output file is now gathering what I expected.
Just one final "cosmetic" question:When host line starts with blank spaces, corresponding txt line is ignoring them and first no blank host character appers on the first position of the txt line.

Any ideas?

 

[CompuDork]

I think it is because of the Trim statement. it basically edits the text string before outputting into your text file... I would try this as a simple fix to make the first line of each screen a blank line in your text file..


So your code looks Like this :


Open outfile For Append As #1
Print #1, " "

Do
For i = 1 To iRows
aline = UCase(Trim(Sess.Screen.GetString(i, 1, iCols)))
'MsgBox aline

Print #1, aline
Next i
Print #1, " "

a = a + 1
valorabsoluto = Range("G" & a).Value
sucursal = Range("D" & a).Value
debehaber = Range("H" & a).Value
' F8 key to skip to the next page
'Sess.Screen.SendKeys ("<Pf8>")

'Wait

' YOU'LL NEED ANOTHER CRITERIA TO STOP THE LOOP!
' If line 24 contains "LAST PAGE," stop the loop.
Loop While Trim(Sess.Screen.GetString(24, 1, iCols)) = "LAST PAGE"



Close #1
Wend

End Sub