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
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
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!
RE: Macro Compiles but doesn't work
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro Compiles but doesn't work
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
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro Compiles but doesn't work
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro Compiles but doesn't work
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro Compiles but doesn't work
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro Compiles but doesn't work
CODE
'obj.Workbooks.Open FileName:="C:\Book1.xls" 'remove and replace with
Set obj = Getobject("C:\book1.xls")
RE: Macro Compiles but doesn't work
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