Tricky one!

Hi everyone and especially the brilliant mvidas (matt)!

i have the following macro:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)

REM This line calls the macro subroutine
subSub1_

sub subSub1_()
   autECLSession.autECLOIA.WaitForAppAvailable
   
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "10"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[enter]"
   
   autECLSession.autECLPS.WaitForAttrib 6,34,"10","3c",3,10000

   autECLSession.autECLPS.WaitForCursor 6,35,10000

   autECLSession.autECLOIA.WaitForAppAvailable
   
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "rc"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "65205"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "100206"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[enter]"
end sub

i want to alter it in the following manner:

i want to at the end of the sub copy to the clipboard the following information
from coordinates: 02/002 to 02/070

and another section from 09/002 to 15/038

I want to store this information on the clipboard

then run the macro again for a different six digit number for this line:
autECLSession.autECLPS.SendKeys "65205"

again copying the info at the same coordinates.

Once it has looped round for all my required six digit numbers

i want to paste all the information into an excel spreadsheet.

Is this possible or asking too much?

many thanks indeed!
jim25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
Hi Jim,

I got your email, but had to hunt around a little bit trying to find your new question.  This should be possible for you to do, though I have a couple questions on how you would want to do this.

To get a section of data, like from 2,2 to 2,70, you could use the GetText method:
 autECLSession.autECLPS.GetText(2, 2, 69)

But you couldn't put everything you wanted in the clipboard (VBScript doesn't easily work directly with the clipboard, actually).  You'd have to store everything you wanted in variables, then transfer the data to excel.  Is it something you'd have to have in the clipboard, or would you be willing to have the data transferred directly to excel?  If so, where in excel do you want it? Or could I just have the script produce a .csv file that you could open in excel then paste wherever needed?

I guess what I'm saying is the clipboard idea is probably out, but we can find a good workaround for you. I just need to know more about what you're doing.

For an example (possibly a solution), the following will loop through the numbers you enter into the TheNumbers array, send them to the function to put them in place of the 65205, then return an array with the two blocks of data you want.  That array is then put into columns A and B of a new excel worksheet, and saved as "Your Excel File" when all the data you want is transferred there.  Then the excel workbook is closed, and the application is closed as well.  I can easily modify this if need be, just explain what you need to do.

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
Option Explicit

autECLSession.SetConnectionByName (ThisSessionName)
MainSub
MsgBox "Done!"

Sub MainSub()
 Dim xlApp, xlWB, xlWS, i, TheNumbers, ANumber
 Set xlApp = CreateObject("excel.application")
 Set xlWB = xlApp.Workbooks.Add
 Set xlWS = xlWB.Sheets(1)
 xlWS.Range("A1:B1").Value = Array("Field 1", "Field 2")
 TheNumbers = Array("65205", "65206", "65207", "65208")
 For Each ANumber In TheNumbers
  With xlWS.Cells(xlWS.Rows.Count, 1).End(-4162) '-4162 = xlUp
   xlWS.Range(.Offset(1, 0), .Offset(1, 1)).Value = GetTheData(ANumber)
  End With
 Next
 xlWB.SaveAs "C:\Your excel file.xls"
 xlWB.Close False
 Set xlWS = Nothing
 Set xlWB = Nothing
 xlApp.Quit
 Set xlApp = Nothing
 Set i = Nothing
 Set TheNumbers = Nothing
 Set ANumber = Nothing
End Sub

Function GetTheData(ByVal YourNumber)
 Dim var1, var2
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "10[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "rc[tab][tab]"
 autECLSession.autECLPS.SendKeys YourNumber
 autECLSession.autECLPS.SendKeys "[tab]100206[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 var1 = autECLSession.autECLPS.GetText(2, 2, 69)
 var2 = autECLSession.autECLPS.GetText(9, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(10, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(11, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(12, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(13, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(14, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(15, 2, 37)
 GetTheData = Array(var1, var2)
 Set var1 = Nothing
 Set var2 = Nothing
End Function

Matt
jim25Author Commented:
Hi Matt

Thanks for getting back to me!

I think it is fine about sending the data straight to excel. I would like the data to be laid out (if it is possible) in the following manner:

(Data for 65205) 02/002 to 02/070         09/002 to 15/038


(Data for 65206) 02/002 to 02/070         09/002 to 15/038

(Data for 65207) 02/002 to 02/070         09/002 to 15/038

Etc....

Is this do able?

Cheers.




mvidasCommented:
That is exactly as the code I posted above will do (just into a new workbook). Do you want it to go to an existing workbook?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

jim25Author Commented:
No that should be fine! I shall see if the macro works for me! I shall let you know!
jim25Author Commented:
Hi Matt

The macro hangs on the second screen....
mvidasCommented:
Hmm, I did not do anything different relating to the screen than what you currently had, how does it hang / what does it need to do differently?
jim25Author Commented:
The macro does not respond. The P button for play is on but nothing happens and eventually the session comes up with not respondin. All I changed in your macro where the 5 digit numbers...
jim25Author Commented:
were*
mvidasCommented:
Are you on the same screen you originally recorded it from (where '10' is the first option entered)?
jim25Author Commented:
No after option 10 it goes to another screen and then more data is added ending in "100206". Then another screen appears.
mvidasCommented:
I see, I didn't add anything to exit out of the final screen, so it won't work.

I'll need more info to finish that for you.  Here is how it is currently flowing:

type "10", press enter
type "rc", then tab twice
send number (ex 65205)
press tab, then type "100206", then press enter
resulting screen is where the two groups of data are taken from

From that point, what do you press to go back to screen that you enter "10"?

Also, if the code is stopping mid-run, you will likely have a hidden excel instance open that needs to be closed by the task manager.
jim25Author Commented:
Matt is it easier for you to take out the "10" screen and just go from "rc"...?
mvidasCommented:
It wouldn't make a difference, I'd still need to know how to get back to the screen the 'rc' gets entered into.

I just need to know the keypresses you do to get back to the 10 or rc screen, do you press F3? if so how many times?
jim25Author Commented:
Hi mate sorry took so long to get back to you it was the end of the day at work.

From that point, what do you press to go back to screen that you enter "10"?

The answer is I press function key F7.

does that help?



mvidasCommented:
No worries, I understand.

Insert this SendKeys line at the end of your function:

 Set var2 = Nothing
 autECLSession.autECLPS.SendKeys "[pf7]"
End Function

That should take care of it and bring you back, and should hopefully work (when started on the screen that needs the '10' entered into it).
Matt
jim25Author Commented:
So the function should be:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
Option Explicit

autECLSession.SetConnectionByName (ThisSessionName)
MainSub
MsgBox "Done!"

Sub MainSub()
 Dim xlApp, xlWB, xlWS, i, TheNumbers, ANumber
 Set xlApp = CreateObject("excel.application")
 Set xlWB = xlApp.Workbooks.Add
 Set xlWS = xlWB.Sheets(1)
 xlWS.Range("A1:B1").Value = Array("Field 1", "Field 2")
 TheNumbers = Array("65205", "65206", "65207", "65208")
 For Each ANumber In TheNumbers
  With xlWS.Cells(xlWS.Rows.Count, 1).End(-4162) '-4162 = xlUp
   xlWS.Range(.Offset(1, 0), .Offset(1, 1)).Value = GetTheData(ANumber)
  End With
 Next
 xlWB.SaveAs "C:\Your excel file.xls"
 xlWB.Close False
 Set xlWS = Nothing
 Set xlWB = Nothing
 xlApp.Quit
 Set xlApp = Nothing
 Set i = Nothing
 Set TheNumbers = Nothing
 Set ANumber = Nothing
End Sub

Function GetTheData(ByVal YourNumber)
 Dim var1, var2
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "10[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "rc[tab][tab]"
 autECLSession.autECLPS.SendKeys YourNumber
 autECLSession.autECLPS.SendKeys "[tab]100206[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 var1 = autECLSession.autECLPS.GetText(2, 2, 69)
 var2 = autECLSession.autECLPS.GetText(9, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(10, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(11, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(12, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(13, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(14, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(15, 2, 37)
 GetTheData = Array(var1, var2)
 Set var1 = Nothing
 Set var2 = Nothing
autECLSession.autECLPS.SendKeys "[pf7]"
End Function

Is this right?
mvidasCommented:
Looks right to me, assuming those are the numbers you want to use and the location to save the excel file to
jim25Author Commented:
Hi Matt

It did the loop fine but when it came to the excel bit it crashed the AS/400 program.

I am on a remote server does this make any difference?
mvidasCommented:
It is possible, I don't have a problem doing the excel portion on my pc.. at what point does it crash?
Would you be alright with having it export a .csv file that can be opened in excel?
jim25Author Commented:
I think it crashes after it completes the last loop.

Yeah we can try a .csv file and see if that works...
mvidasCommented:
Hmm.. if it completes the loop then thats odd, since the excel object is created and everything, and the data is already transferred to it.

After the "Set xlApp = createobject..." line, try adding
 xlApp.Visible = True

So you can see the excel instance.  Is it possible you have an excel spreadsheet where it is trying to saveas to?  If so, the excel application could be bringing up the "a workbook already exists with this name, do you want to overwrite it?" prompt, and crashing because there is no answer (invisible).
If that is the case (you can tell with the .Visible = true) we can hide the prompts so it will overwrite an existing file and not crash.

The CSV file may not work if either of the blocks of text you're scraping from the AS400 have a comma in them.  It might be a better idea to use the tab character (chr(9)).  I've coded this to include a comma for the delimiter, if you want to use a tab instead then change the   vDelim = ","   line.  Actually I am going to use the tab now (you can change Chr(9) instead to "," if you want to use comma), and save the file as an .xls so it will open in excel by default.

Sub MainSub()
 Dim TheNumbers, ANumber, FSO, FL, vDelim
 Set FSO = CreateObject("scripting.filesystemobject")
 Set FL = FSO.CreateTextFile("C:\your file.xls")
 vDelim = Chr(9) 'for tab character.. if you want comma use "," instead
 FL.WriteLine "Field 1" & vDelim & "Field 2"
 TheNumbers = Array("65205", "65206", "65207", "65208")
 For Each ANumber In TheNumbers
  FL.WriteLine Join(GetTheData(ANumber), vDelim)
 Next
 FL.Close
 Set FL = Nothing
 Set FSO = Nothing
 Set vDelim = Nothing
 Set TheNumbers = Nothing
 Set ANumber = Nothing
End Sub

Still keep the "GetTheData" function the way it is
Matt
jim25Author Commented:
You are a bloody genius!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I added the line and it worked it created the excel sheet but crashed the programme! But at least I have the excel sheet!
mvidasCommented:
What error is happening?  The excel object is my little baby, I try to know it inside and out.  Hopefully we can get rid of the error!

Something to try is to put these two lines around the .SaveAs line:
 xlApp.DisplayAlerts = False
 xlWB.SaveAs "C:\Your excel file.xls"
 xlApp.DisplayAlerts = True


You'll have the excel sheet without error using my second subroutine too, FWIW.
jim25Author Commented:
Hi Matt

It is a bit better with the two lines it only crashes at the end of the macro now but still crashes...
mvidasCommented:
OK, making progress.  It is also possible your excel is generating a blank workbook, which could be hanging it up a little.  Try replacing this line:
 xlWB.Close False

With:
 For Each xlWB in xlApp.Workbooks
  xlWB.Close False
 Next

That will close any workbooks still open.  What error messages are you getting anyways?
jim25Author Commented:
I tried your second .csv version macro and it came up with a permission error on line 14 which was

 Set FL = FSO.CreateTextFile("C:\your file.xls")


 
jim25Author Commented:
added the line but still closes the AS/400 session. There is no error message it just closes the session for some reason...

Got to do a valentines party now! I will be back online in about 16 hours time in the UK you see! Thanks for the help so far will talk tomorrow I hope if you are around!

Cheers!
mvidasCommented:
Hmmm... well have fun at your party, and give the second version a try too.  I will be around tomorrow, so let me know how it goes!
mvidasCommented:
I just saw your comment about the permission error... are you trying to write to a file that is already opened?  Try using a new name, in a location you know you have write access to
jim25Author Commented:
Hi Matt

The following macro crashes the programme at the end of the cycle (as far as I can make out)


[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
Option Explicit

autECLSession.SetConnectionByName (ThisSessionName)
MainSub
MsgBox "Done!"

Sub MainSub()
 Dim xlApp, xlWB, xlWS, i, TheNumbers, ANumber
 Set xlApp = CreateObject("excel.application")
 xlApp.Visible = True
 Set xlWB = xlApp.Workbooks.Add
 Set xlWS = xlWB.Sheets(1)
 xlWS.Range("A1:B1").Value = Array("Field 1", "Field 2")
 TheNumbers = Array("65205", "64613", "63625", "64669", "64705")
 For Each ANumber In TheNumbers
  With xlWS.Cells(xlWS.Rows.Count, 1).End(-4162) '-4162 = xlUp
   xlWS.Range(.Offset(1, 0), .Offset(1, 1)).Value = GetTheData(ANumber)
  End With
 Next
 xlApp.DisplayAlerts = False
 xlWB.SaveAs "C:\Your excel file.xls"
 xlApp.DisplayAlerts = True
 For Each xlWB in xlApp.Workbooks
  xlWB.Close False
 Next
 Set xlWS = Nothing
 Set xlWB = Nothing
 xlApp.Quit
 Set xlApp = Nothing
 Set i = Nothing
 Set TheNumbers = Nothing
 Set ANumber = Nothing
End Sub

Function GetTheData(ByVal YourNumber)
 Dim var1, var2
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "10[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "rc[tab][tab]"
 autECLSession.autECLPS.SendKeys YourNumber
 autECLSession.autECLPS.SendKeys "[tab]100206[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 var1 = autECLSession.autECLPS.GetText(2, 2, 69)
 var2 = autECLSession.autECLPS.GetText(9, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(10, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(11, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(12, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(13, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(14, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(15, 2, 37)
 GetTheData = Array(var1, var2)
 Set var1 = Nothing
 Set var2 = Nothing
autECLSession.autECLPS.SendKeys "[pf7]"
End Function

The next one still gives a permission error even if I change the name and location! Why is that?

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
Option Explicit

autECLSession.SetConnectionByName (ThisSessionName)
MainSub
MsgBox "Done!"

Sub MainSub()
 Dim TheNumbers, ANumber, FSO, FL, vDelim
 Set FSO = CreateObject("scripting.filesystemobject")
 Set FL = FSO.CreateTextFile("C:\your_file.xls")
 vDelim = Chr(9) 'for tab character.. if you want comma use "," instead
 FL.WriteLine "Field 1" & vDelim & "Field 2"
 TheNumbers = Array("65205", "64613", "63625", "64669", "64705")
 For Each ANumber In TheNumbers
  FL.WriteLine Join(GetTheData(ANumber), vDelim)
 Next
 FL.Close
 Set FL = Nothing
 Set FSO = Nothing
 Set vDelim = Nothing
 Set TheNumbers = Nothing
 Set ANumber = Nothing
End Sub

Function GetTheData(ByVal YourNumber)
 Dim var1, var2
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "10[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "rc[tab][tab]"
 autECLSession.autECLPS.SendKeys YourNumber
 autECLSession.autECLPS.SendKeys "[tab]100206[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 var1 = autECLSession.autECLPS.GetText(2, 2, 69)
 var2 = autECLSession.autECLPS.GetText(9, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(10, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(11, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(12, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(13, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(14, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(15, 2, 37)
 GetTheData = Array(var1, var2)
 Set var1 = Nothing
 Set var2 = Nothing
autECLSession.autECLPS.SendKeys "[pf7]"
End Function


mvidasCommented:
Hmm.. the only thing that comes to mind is that you said you're on a remote server.  The way I have mine setup is that my .mac files are on my local C drive, and the as400 box is on a remote server.  I don't know why it would matter, since you're creating files on your local drive (where you know you have permission).  Seems like both codes are falling out when the file is being created.  I could understand it more if you had problems right from the get go (because of CreateObject), but if they both error out when the file is saved, I think it has more to do with that.

Is it possible your local drive doesn't allow a remote server to create a file?  It has to be something like that, just not sure what without actually looking at your setup.

Matt
jim25Author Commented:
Hi Matt

I am a bit confused. I have on my local computer (the actual physical hard drive) in my office excel BUT I also have on the remote server excel which the macro is calling (I know this for sure). I think everything is being done on the remote server c: drive but I can not see this drive. Could this be the permission problem?

James
mvidasCommented:
Where is the .mac file located that you're running?  If that is on the remote server, then I think it would be trying to create it on the server's C drive like you thought.  It's possible the server doesn't even have a C drive (would easily explain the error).

It might be possible to have it specify your computer in the filepath, but is there any way to run the .mac file locally?
jim25Author Commented:
Hi matt the macro is being run from the remote server x:/ drive!
mvidasCommented:
Hmm.. This is just a guess, but try changing the reference of
"C:\your_file.xls" to ".\your_file.xls"

See if the error goes away.  Might give us a couple more clues on what to do
jim25Author Commented:
Hi Matt

Yeah it completes the macro with a message box done BUT there is not worksheet!
mvidasCommented:
There is .. somewhere! (probably on that x drive, did you check the location of the .mac file?)
that is good news at least.

The bad news being that I'm unsure where to go from here.  Networking is not a strongpoint of mine, but I'll do my best to try some things out.  First thing I can think of is to get the computer name (right-click My Computer, and go to properties - Network ID tab). Once you have that (for example I'll call it JimsComputer), go to Start, then Run, and in the box type \\ followed by the computer name, eg:
\\JimsComputer

See if anything is listed there.  If so, you could use that path structure to identify where on your local computer to have it saved (\\JimsComputer\cdrive\your folder\subfolder\xlFile.xls).  If it is not there, your hard drive must not be setup to be shared across the network.  As far as I know, it would have to be shared so the remote server can create a file.  If you need help in figuring out how to do that, let me know your Windows version and I can look it up.
jim25Author Commented:
Your are an absolute genius!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I followed ur instructions and I found it! How did you know it would be there? How clever are you????
jim25Author Commented:
In addition the format of the .csv file is a lot better than the other version (less chunks of white spaces). Plus the fact the macro did not crash the system!
mvidasCommented:
>>How did you know it would be there
In dos/windows, "." refers to "current directory", so I was hoping that using ".\excel file.xls" would save it in the .mac directory.  Looks like it worked out

>>format of the .csv file is a lot better
Shorter file too, as it is pretty much just flat text.  Did you try the alternative using Chr(9) as the delimiter and saving it still as an .xls file? That to me seems the best way overall to go, but I'll leave it to your judgement.

Please let me know if there is anything else!
Matt
jim25Author Commented:
Hi Matt this is code that works and I am using:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
Option Explicit

autECLSession.SetConnectionByName (ThisSessionName)
MainSub
MsgBox "Done!"

Sub MainSub()
 Dim TheNumbers, ANumber, FSO, FL, vDelim
 Set FSO = CreateObject("scripting.filesystemobject")
 Set FL = FSO.CreateTextFile(".\your_file.xls")
 vDelim = Chr(9) 'for tab character.. if you want comma use "," instead
 FL.WriteLine "Field 1" & vDelim & "Field 2"
 TheNumbers = Array("65205", "64613", "63625", "64669", "64705", "66336")
 For Each ANumber In TheNumbers
  FL.WriteLine Join(GetTheData(ANumber), vDelim)
 Next
 FL.Close
 Set FL = Nothing
 Set FSO = Nothing
 Set vDelim = Nothing
 Set TheNumbers = Nothing
 Set ANumber = Nothing
End Sub

Function GetTheData(ByVal YourNumber)
 Dim var1, var2
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "10[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 autECLSession.autECLPS.SendKeys "rc[tab][tab]"
 autECLSession.autECLPS.SendKeys YourNumber
 autECLSession.autECLPS.SendKeys "[tab]100206[enter]"
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
 var1 = autECLSession.autECLPS.GetText(2, 2, 69)
 var2 = autECLSession.autECLPS.GetText(9, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(10, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(11, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(12, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(13, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(14, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(15, 2, 37)
 GetTheData = Array(var1, var2)
 Set var1 = Nothing
 Set var2 = Nothing
autECLSession.autECLPS.SendKeys "[pf7]"
End Function

Is this the option you were talking about?

mvidasCommented:
That's exactly it! I had not scrolled up to see how I presented it to you, I wasn't sure if I only mentioned using the tab character or if I gave you the code for it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jim25Author Commented:
I think this is the best version to be honest so I shall except this as the answer! Thanks so much for all your hard work on this question it really is appreciated!!!
mvidasCommented:
No problem at all! Let me know if/when you need any more help.

Also, if you want to email me again, include a link to your question or even your ee username.  I had to search though my old questions for as400 since your titles don't mention it :)  In any case, I did find it, so I could find it again if need be
jim25Author Commented:
Matt if I wish to reduce the area to be copied for the var2 do I just take out the line I do not wish to be copied?

ie this bit:

var2 = autECLSession.autECLPS.GetText(9, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(10, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(11, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(12, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(13, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(14, 2, 37) & vbLf & _
        autECLSession.autECLPS.GetText(15, 2, 37)
take out the bit I do not want?

jim25Author Commented:
I tried it and it worked!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.