Macro Compiles but doesn't work
2

Question

Macro Compiles but doesn't work

link99sbc (TechnicalUser)

(OP)

22 Sep 09 12:21

I'm trying to scrape a little data from extra to excel.
The macro compiles but no data appears in excel.
I don't get any error messages.

[]
' 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("EXTRA.System")    ' Gets the system object
    If (System is Nothing) Then
        Msgbox "Could not create the EXTRA System object.  Stopping macro playback."
        STOP
    End If
    Set Sessions = System.Sessions

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

    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If

' 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 script

        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file
        set objworkbook = obj.worksheets("Sheet2")

        obj.WorkSheets("sheet2").Cells(B, 1).Value = sess0.Screen.GetString(5, 8, 31)
        obj.WorkSheets("sheet2").Cells(B, 2).Value = sess0.Screen.GetString(6, 2, 42)
        obj.WorkSheets("sheet2").Cells(B, 3).Value = sess0.Screen.GetString(7, 2, 42)

        System.TimeoutValue = OldSystemTimeout

End Sub

SkipVought (Programmer) · Answer 1 · 2009-09-22 14:02:24

Hi,

I'm guessing that you want the data in Column B...

CODE

        set objworkbook = obj.workbooks.open file

   With objworkbook
        .WorkSheets("sheet2").Cells(1, "B").Value = sess0.Screen.GetString(5, 8, 31)
        .WorkSheets("sheet2").Cells(2, "B").Value = sess0.Screen.GetString(6, 2, 42)
        .WorkSheets("sheet2").Cells(3, "B").Value = sess0.Screen.GetString(7, 2, 42)
   end with

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 2 · 2009-09-22 14:34:14

Thanks Skip
Error on Set objworkbook = obj.workbooks.open file

        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file

        set objworkbook = obj.worksheets("Sheet2")

'        obj.WorkSheets("sheet2").Cells(B, 1).Value = sess0.Screen.GetString(5, 8, 31)
'        obj.WorkSheets("sheet2").Cells(B, 2).Value = sess0.Screen.GetString(6, 2, 42)
'        obj.WorkSheets("sheet2").Cells(B, 3).Value = sess0.Screen.GetString(7, 2, 42)


        set objworkbook = obj.workbooks.open file       'bug on this line

        With objworkbook

        .WorkSheets("sheet2").Cells(1, "B").Value = sess0.Screen.GetString(5, 8, 31)
        .WorkSheets("sheet2").Cells(2, "B").Value = sess0.Screen.GetString(6, 2, 42)
        .WorkSheets("sheet2").Cells(3, "B").Value = sess0.Screen.GetString(7, 2, 42)

        end with

SkipVought (Programmer) · Answer 3 · 2009-09-22 14:45:25

What is the value in file?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 4 · 2009-09-22 14:59:45

I'm not sure I understand the question.
Screen position 5,8 is a name which could be 31 characters long.
Screen position 6,2 is the Address up to 42 characters.
Screen position 7,2 is city, state, zip ending at 42.
All of which I want to put in sheet 2 col B rows 1,2,3
respectively.

I rem out the line that had the bug. It compiles but still
no data appears on the excel sheet.

SkipVought (Programmer) · Answer 5 · 2009-09-22 15:11:31

You have this statement on which you have an error...

CODE

set objworkbook = obj.workbooks.open file

What is the value of file. It must be a valid path & workbook!

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 6 · 2009-09-22 15:35:58

RE: Macro Compiles but doesn't work

link99sbc (TechnicalUser)

(OP)

22 Sep 09 15:35

Oh! the workbook is already open. Book1.xls

SkipVought (Programmer) · Answer 7 · 2009-09-22 16:12:10

Then WHY are you using the OPEN method?

You want to assign an object when you ADD the workbook...

CODE

...
set objworkbook = obj.workbooks.Add
...

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 8 · 2009-09-22 16:26:53

OK this works but the data is going into a newly created Book1 instead of the Book1.xls that is already open.

        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True

        set objworkbook = obj.workbooks.Add

        With objworkbook

        .WorkSheets("sheet2").Cells(1, "B").Value = sess0.Screen.GetString(5, 8, 31)
        .WorkSheets("sheet2").Cells(2, "B").Value = sess0.Screen.GetString(6, 2, 42)
        .WorkSheets("sheet2").Cells(3, "B").Value = sess0.Screen.GetString(7, 2, 42)

        End with

        System.TimeoutValue = OldSystemTimeout

End Sub

SkipVought (Programmer) · Answer 9 · 2009-09-22 16:38:07

Why is the book already ADDED? Is there stuff already in it? Why have you not saved it?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 10 · 2009-09-22 16:56:38

Book1 Sheet1 is a form letter I designed in Excel.  The values for name,address, account number etc. are entered on Sheet2.  The fields on Sheet1 are formulated to pickup the values from sheet2.

This code works. But, it still opens a new file each time I run it. I need to keep the file open, fill in the values, print sheet1, clear sheet2 for the next values for the next letter and so on.  May have do 20 - 50 letters a day.  Once I get this to work right I can expand to get more values from different extra screens hopefully.

[]
        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.Workbooks.Open FileName:="C:\Book1.xls"   'added this statement
        obj.visible = True

'        set objworkbook = obj.workbooks.Add
'        set objworkbook = obj.workbooks.open file
        With objworkbook

        obj.WorkSheets("sheet2").Cells(1, "B").Value = sess0.Screen.GetString(5, 8, 39)
        obj.WorkSheets("sheet2").Cells(2, "B").Value = sess0.Screen.GetString(6, 2, 42)
        obj.WorkSheets("sheet2").Cells(3, "B").Value = sess0.Screen.GetString(7, 2, 42)

        End with

        System.TimeoutValue = OldSystemTimeout

End Sub

SkipVought (Programmer) · Answer 11 · 2009-09-22 22:36:49

Again, I ask, why is it not a saved workbook, that you can open? All the more since you have invested valuable effort into creating a "form letter I designed in Excel?"

Save it.

Then use the Open method to open the workbook, as you originally seemed to intend.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

vzachin (TechnicalUser) · Answer 12 · 2009-09-24 13:10:02

CODE

Set obj=CreateObject("Excel.Application")
'obj.Workbooks.Open FileName:="C:\Book1.xls" 'remove and replace with
Set obj = Getobject("C:\book1.xls")

link99sbc (TechnicalUser) · Answer 13 · 2009-09-24 16:58:24

Thank you both. It's working great. 2thumbsup

Using Set obj = Getobject("C:\book1.xls") caused
an error on obj.visible = True so I deleted it.
I used AppActivate instead so I can view the
finished letter.  Then added a print command,
Pause, to view the letter then obj.Worksheets("Sheet2").Range("A1:B5").ClearContents
for the next letter.  Works great!  Thanks again.
Maybe I should post it for other folks with similar
situations.

[]
Excel Form Letter. Sheet1 is a form letter used as a template.
Empty Cells are used as fields.  The values for the Cells, name,
address, account number etc. are entered on Sheet2.
The Cells on Sheet1 are formulated to pickup the values from sheet2.
The Extra Macro gets the data from Extra and puts it on sheet2.
Thus a letter is born.

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

    If (Sessions is Nothing) Then
        Msgbox "Could not create the Sessions collection object.  Stopping macro playback."
        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

' 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 DOES THE MAGIC

        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")

' File Location - Change as Needed!

       'obj.Workbooks.Open FileName:="C:\1895.xls"     'File location If the file is not already open*
        Set obj = Getobject("C:\book1.xls")            'File is already open**
       'obj.visible = True                             'Not used if file is already open

' Start The Magic Show

        With objworkbook

' Get Data from Current Extra Screen and put in XL file

        obj.WorkSheets("sheet2").Cells(1, "A").Value = sess0.Screen.GetString(4, 39, 9)   'Acct#
        obj.WorkSheets("sheet2").Cells(2, "A").Value = sess0.Screen.GetString(4, 11, 7)   'Case #
        obj.WorkSheets("sheet2").Cells(3, "A").Value = sess0.Screen.GetString(14, 66, 10) 'Amt
        obj.WorkSheets("sheet2").Cells(4, "A").Value = sess0.Screen.GetString(14, 29,20)  'Period
        obj.WorkSheets("sheet2").Cells(5, "A").Value = sess0.Screen.GetString(4, 23, 9)   'Tat
        obj.WorkSheets("sheet2").Cells(1, "B").Value = sess0.Screen.GetString(6, 17, 30)  'Name

' Move to Another Extra Screen

        Sess0.Screen.MoveTo 21,06
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys("sprai<Enter>")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Get Data from Extra Screen and put in xl file

        obj.WorkSheets("sheet2").Cells(2, "B").Value = sess0.Screen.GetString(14, 20, 30) 'Address
        obj.WorkSheets("sheet2").Cells(3, "B").Value = sess0.Screen.GetString(15, 20, 30) 'City
        obj.WorkSheets("sheet2").Cells(4, "B").Value = sess0.Screen.GetString(15, 55, 3)  'State
        obj.WorkSheets("sheet2").Cells(5, "B").Value = sess0.Screen.GetString(15, 63, 10) 'Zip

' Print The XL File ------ This code will have to be changed according to printer setup.

    AppActivate "Microsoft Excel - Book1.xls"

    Pause 4  'TO VIEW THE LETTER

        Sendkeys "%FP{Enter}"


' *Close The XL File

        obj.Workbooks("1895.XLS").Close SaveChanges:=False

' **Or leave the file open and clear the contents to run the macro again.

    obj.Worksheets("Sheet2").Range("A1:B5").ClearContents

        End With

        System.TimeoutValue = OldSystemTimeout

End Sub

Come Join Us!

Posting Guidelines

Macro Compiles but doesn't work
2