Screen Scrape to Excel or CSV or Access

[samato]

I need to scrape a host report to an external format. I go for about any format in fact. I used the following to send the screen scrape to a printer, any idea how I can append it to a file instead of sending it to a printer? The code I use to print is -

Dim rc%
Dim MaxColumn%
Dim row%
Dim MaxRows%
Dim filenum%
Dim Screenbuf$
Dim linebuf$
Dim FileName$

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' This section of code contains the recorded events
Sess0.Screen.Sendkeys(&quot;<Home>&quot
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(&quot;xqd<Enter>&quot
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(&quot;57221000000<Enter>&quot
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Set Sess0=System.ActiveSession
Set MyScreen=Sess0.Screen

FileIn=FreeFile
'Takes a &quot;snapshot&quot; of current screen and stores it in variable Screenbuf$
MaxRows%=Sess0.Screen.Rows()
MaxColumns%=Sess0.Screen.Cols()
Screenbuf$=&quot;&quot;
linebuf$=Space$(MaxColumns%)
For row%=1 to MaxRows%
linebuf$=Sess0.Screen.Area(row%,1,row%,MaxColumns%,xBlock)
Screenbuf$=Screenbuf$+linebuf$+Chr$(13)+Chr$(10)
Next
filenum%=Freefile
FileName$=&quot;\\BDCTAPE\HP_Eastwing&quot; 'name of your network printer
Open FileName$ For Output as filenum%
Print # filenum%,screenbuf$; chr$(12) 'this actually sends the screen print to the printer
Close filenum%

 

[calculus]

Changing the last 4 lines of code should work.

FileName$ = [Path + Filename.csv]
Open FileName$ for append as filenum%
Print ...
Close filenum%

The difference should just be the way you open the file. You may also want to use the &quot;write&quot; command instead of the print.

This will then open nicely in Excel.

Calculus

 

[samato]

Here is what I tried. It creates a file, with nothing in it though. I apoligize, I'm pretty good at this but not as good as some think I am. Pretty much self taught. Any additional help ios GREATLY appreciated.

Sam

'--------------------------------------------------------------------------------
' This macro was created by the Macro Recorder.
' Session Document: &quot;FDR2.EDP&quot;
' Date: Saturday, April 26, 2003
' User: Sam Amato
' This macro spins through all the Queues and automatically prints them using embedded code.
' The screens rotate through at the rate of 1 every half second.
'--------------------------------------------------------------------------------

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject(&quot;EXTRA.System&quot ' Gets the system object
If (System is Nothing) Then
Msgbox &quot;Could not create the EXTRA System object. Stopping macro playback.&quot;
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox &quot;Could not create the Sessions collection object. Stopping macro playback.&quot;
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 500 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If
' Name the variables
Dim rc%
Dim MaxColumn%
Dim row%
Dim MaxRows%
Dim filenum%
Dim Screenbuf$
Dim linebuf$
Dim FileName$

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox &quot;Could not create the Session object. Stopping macro playback.&quot;
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' This section of code contains the recorded events
Sess0.Screen.Sendkeys(&quot;<Home>&quot
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(&quot;xqd<Enter>&quot
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(&quot;57221000000<Enter>&quot
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Set Sess0=System.ActiveSession
Set MyScreen=Sess0.Screen

FileIn=FreeFile
'Takes a &quot;snapshot&quot; of current screen and stores it in variable Screenbuf$
MaxRows%=Sess0.Screen.Rows()
MaxColumns%=Sess0.Screen.Cols()
Screenbuf$=&quot;&quot;
linebuf$=Space$(MaxColumns%)
For row%=1 to MaxRows%
linebuf$=Sess0.Screen.Area(row%,1,row%,MaxColumns%,xBlock)
Screenbuf$=Screenbuf$+linebuf$+Chr$(13)+Chr$(10)
Next
filenum%=Freefile
FileName$ = &quot;c:\winnt\sam.csv&quot;
Open FileName$ for append as filenum%
Write # filenum%,&quot;&quot;
Close filenum%

End Sub

 

[calculus]

Sorry, you did exactly what I said.

What's happening is you've got a loop here:

For row% =1 to...
Next

Then your opening a file and printing an empty string here:
Open...
write...
close

Move the write statement into the loop like this:

filenum%=Freefile
FileName$ = &quot;c:\winnt\sam.csv&quot;
Open FileName$ for append as filenum%

For row%=1 to MaxRows%
linebuf$=Sess0.Screen.Area(row%,1,row%,MaxColumns%,xBlock)
Screenbuf$=Screenbuf$+linebuf$+Chr$(13)+Chr$(10)
Write # filenum%,&quot;&quot;
Next
Close filenum%

This will write the data as it goes through the loop. I've never used a screen scrape like your doing here, so I'm assuming your codeing is correct on the linebuf$ and ScreenBuf$.

calculus