×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Macro Compiles but doesn't work
2

Macro Compiles but doesn't work

Macro Compiles but doesn't work

(OP)
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

RE: Macro Compiles but doesn't work



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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro Compiles but doesn't work

(OP)
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

RE: Macro Compiles but doesn't work



What is the value in file?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro Compiles but doesn't work

(OP)
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.
 

RE: Macro Compiles but doesn't work




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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro Compiles but doesn't work

(OP)
Oh! the workbook is already open. Book1.xls

RE: Macro Compiles but doesn't work


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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro Compiles but doesn't work

(OP)
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
 

RE: Macro Compiles but doesn't work



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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro Compiles but doesn't work

(OP)
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

RE: Macro Compiles but doesn't work




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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro Compiles but doesn't work

CODE

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

 

RE: Macro Compiles but doesn't work

(OP)
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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close