SuperKoopa
Technical User
Good Afternoon!
Here's what I'm trying to accomplish:
There is an Attachmate screen with 5 columns of data on each page. There are several pages navigated to using F8. Each colomn of data has several rows of data that must remain in line. Example:
12/12/12
11/15/11
123
00.
YES
447.24...
...
..
And there are 19 rows of data for each column.I would like to move each columns data to an excel spreadsheet while keeping all of the data in order. I would like each Attachmate data column to have its own Excel column.
I've tried it 2 different ways.
The first way I tried to isolate each column of data using get string for the length of the data for the column and move it to excel. Not only was it time consuming, it wasn't reliable.
The second way which I'm currently messing with is copying the column data as long rows of data, using getstring to map each page to one single coloumn in excel and then using text to columns to order it properly. I feel as though I'm almost there. My main issue lies in the fact that Text to columns will not function properly. I can get it only to account for column A in Excel. Each Attachmate column will take up 10 Excel columns after Text To Columns is complete so in the loop as well I've included +10 to write the next F8 Page of 5 columns to the 10th Excel column and then use Text to columns and start the process over again untill all pages are completed.
After Excel column a has Text to columns applied to it, it works, but when the code loops to map the 2nd page of columns to excel column K, it gets the data just fine, but does not apply text to columns. Here is the code I am currently using. Any areas that you see can help me out I would absolutely appreciate it!
--------------------------------------------------------------------------------------------------------------
Sub Fillfigs()
' Get the main system object
Dim Sessions As Object
Dim I As Integer
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
ag_HostSettleTime = 5 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (ag_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = ag_HostSettleTime
End If
' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 Is Nothing) Then
MsgBox "512 Session is not open! Please open login to MSP and try again."
Stop
End If
If Not Sess0.Visible Then Sess0.Visible = True
Sess0.Screen.WaitHostQuiet (ag_HostSettleTime)
' Declare variables to contain the OLE objects
Dim objExcel As Object
Dim objWorkBook As Object
Dim gs As String
Dim r As Integer
Dim j As Integer
r = 1
On Error Resume Next
' Attempt to get a reference to an open instance of Excel
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
'If GetObject failed, open a new instance of Excel
Set objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If
' Make Excel visible on the screen
objExcel.Visible = True
' Create a new Workbook
Set objWorkBook = objExcel.ActiveWorkbook
'THE CODE ABOVE IS JUST WHAT I COPY/PASTE TO USE WHEN TESTING A SNIPPET OF CODE FROM A BROADER PROJECT
'***************
'PULL FIGURES
'***************
With objWorkBook.Sheets("Sheet1")
acol = .Cells(, "A").Column
Do
lRow = .Cells(4, acol) + 4
For z = 6 To 20
Sess0.Screen.WaitHostQuiet (ag_HostSettleTime)
FIGS = Sess0.Screen.GetString(z, 11, 69)
.Cells(lRow, acol).Value = FIGS
lRow = z
.Cells(lRow, acol).Select
Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(14, 1), Array(16, 1), Array(28, 1), _
Array(30, 1), Array(42, 1), Array(44, 1), Array(56, 1), Array(58, 1), Array(70, 1), Array( _
72, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
If z = 20 Then acol = acol + 10
Next z
Sess0.Screen.SendKeys ("<PF8>")
If Sess0.Screen.GetString(23, 20, 4) = "PRESS" Then Exit Do
Loop Until Sess0.Screen.GetString(4, 68, 3) = "xxxx" 'not referenceing anything in particular, just waiting until the IF above become TRUE
End With
End Sub
Here's what I'm trying to accomplish:
There is an Attachmate screen with 5 columns of data on each page. There are several pages navigated to using F8. Each colomn of data has several rows of data that must remain in line. Example:
12/12/12
11/15/11
123
00.
YES
447.24...
...
..
And there are 19 rows of data for each column.I would like to move each columns data to an excel spreadsheet while keeping all of the data in order. I would like each Attachmate data column to have its own Excel column.
I've tried it 2 different ways.
The first way I tried to isolate each column of data using get string for the length of the data for the column and move it to excel. Not only was it time consuming, it wasn't reliable.
The second way which I'm currently messing with is copying the column data as long rows of data, using getstring to map each page to one single coloumn in excel and then using text to columns to order it properly. I feel as though I'm almost there. My main issue lies in the fact that Text to columns will not function properly. I can get it only to account for column A in Excel. Each Attachmate column will take up 10 Excel columns after Text To Columns is complete so in the loop as well I've included +10 to write the next F8 Page of 5 columns to the 10th Excel column and then use Text to columns and start the process over again untill all pages are completed.
After Excel column a has Text to columns applied to it, it works, but when the code loops to map the 2nd page of columns to excel column K, it gets the data just fine, but does not apply text to columns. Here is the code I am currently using. Any areas that you see can help me out I would absolutely appreciate it!
--------------------------------------------------------------------------------------------------------------
Sub Fillfigs()
' Get the main system object
Dim Sessions As Object
Dim I As Integer
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
ag_HostSettleTime = 5 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (ag_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = ag_HostSettleTime
End If
' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 Is Nothing) Then
MsgBox "512 Session is not open! Please open login to MSP and try again."
Stop
End If
If Not Sess0.Visible Then Sess0.Visible = True
Sess0.Screen.WaitHostQuiet (ag_HostSettleTime)
' Declare variables to contain the OLE objects
Dim objExcel As Object
Dim objWorkBook As Object
Dim gs As String
Dim r As Integer
Dim j As Integer
r = 1
On Error Resume Next
' Attempt to get a reference to an open instance of Excel
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
'If GetObject failed, open a new instance of Excel
Set objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If
' Make Excel visible on the screen
objExcel.Visible = True
' Create a new Workbook
Set objWorkBook = objExcel.ActiveWorkbook
'THE CODE ABOVE IS JUST WHAT I COPY/PASTE TO USE WHEN TESTING A SNIPPET OF CODE FROM A BROADER PROJECT
'***************
'PULL FIGURES
'***************
With objWorkBook.Sheets("Sheet1")
acol = .Cells(, "A").Column
Do
lRow = .Cells(4, acol) + 4
For z = 6 To 20
Sess0.Screen.WaitHostQuiet (ag_HostSettleTime)
FIGS = Sess0.Screen.GetString(z, 11, 69)
.Cells(lRow, acol).Value = FIGS
lRow = z
.Cells(lRow, acol).Select
Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(14, 1), Array(16, 1), Array(28, 1), _
Array(30, 1), Array(42, 1), Array(44, 1), Array(56, 1), Array(58, 1), Array(70, 1), Array( _
72, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
If z = 20 Then acol = acol + 10
Next z
Sess0.Screen.SendKeys ("<PF8>")
If Sess0.Screen.GetString(23, 20, 4) = "PRESS" Then Exit Do
Loop Until Sess0.Screen.GetString(4, 68, 3) = "xxxx" 'not referenceing anything in particular, just waiting until the IF above become TRUE
End With
End Sub
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]![[dazed]](/data/assets/smilies/dazed.gif "[dazed] [dazed]")