Macro Compiles but doesn't work 2

[link99sbc]

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]

Hi,

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

        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]

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]

What is the value in file?

Skip,

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

 

[link99sbc]

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]

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

        set objworkbook = obj.workbooks.open [b]file[/b]

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

Skip,

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

 

[link99sbc]

Oh! the workbook is already open. Book1.xls

 

[SkipVought]

Then WHY are you using the OPEN method?

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

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

Skip,

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

 

[link99sbc]

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]

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]

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]

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]

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

 

[link99sbc]

Thank you both. It's working great.
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