Copying data from Hummingbird HostExplorer to Excel

Question

I'm currently trying to create a script to copy data from a Hummingbird HostExplorer screen to Excel (essentially screen-scraping). I can create the instance of Excel with no problems, and I can paste the data into Word with no problems, but I simply cannot work out (even after hours of searching online) how to actually paste the data into Excel.

Here's the code as it stands so far:

    Sub Main 
    Dim Host As Object                         
    Dim HE as Object                          
    Set HE = CreateObject( "HostExplorer" )
    Set Host = HE.CurrentHost
    Dim iPSUpdateTime
    Dim HostExplorer as Object
    Dim MyHost as Object

    On Error goto ErrorCheck

    Set HostExplorer = CreateObject("HostExplorer") ' Initialize HostExplorer Object
    Set MyHost = HostExplorer.HostFromProfile("EDC") ' Set object for the desired session

    iPSUpdateTime = 60   ' PS Update wait time set to 60 seconds

'-------------------------------------         
' insertion of code to change to A226         
'-------------------------------------

    MyHost.RunCmd("Home")
    MyHost.RunCmd("Back-Tab")
    MyHost.Keys("a226")
    MyHost.RunCmd("Enter")
    MyHost.WaitPSUpdated iPSUpdateTime, TRUE
    MyHost.Keys("001dis010101")
    MyHost.RunCmd("Tab")
    MyHost.RunCmd("Tab")
    MyHost.RunCmd("Tab")
    MyHost.RunCmd("Tab")
    MyHost.Keys("c")
    MyHost.RunCmd("Enter")
    MyHost.WaitPSUpdated iPSUpdateTime, TRUE     

'----------------------------   
' code to copy data to Excel    
'----------------------------

    Dim XLS As Object                          
    Set XLS = CreateObject("Excel.Sheet")      
    XLS.FileNewDefault         

    For i = 1 to Host.Rows                                                       
        XLS.FONT "Courier New"                  
        XLS.FontSize 8                               
    Next i




'-------------
' Error check
'-------------
ErrorCheck:
  if (Err = 440) Then
    Msgbox "The specified session is not running.", 16, "Hummingbird Macro Error"
  End If
  Exit Sub
End Sub

When I run this, it gets to XLS.FileNewDefault but then skips the For i section and goes straight to the error checking. However, if I change:

Set XLS = CreateObject("Excel.Sheet")  

to

Set XLS = CreateObject("Word.Basic")  

it pastes absolutely fine into Word.

Am I missing something incredibly easy here?

Answer 1

The following code creates an Excel workbook and pastes the content of the clipboard in a new worksheet:

option explicit

dim XLS, Book, Sheet

'Load Excel Application.
Set XLS = CreateObject("Excel.Application")

'By default it is invisible, so make it visible.
XLS.visible = true

'Still there is no workbook, so add one now.
set Book=XLS.Workbooks.Add()

'By default a workbook has 3 worksheets; we will work on the first one.
set Sheet=Book.Sheets(1)

'Call sheet.paste to paste the content of the clipboard into the active cell
'(which is by default "A1").
Sheet.paste