Send data from Attachmate Extra to current selected cell in Excel

[jay3120]

Hello,

I am running a macro in Attachmate Extra that has a small part where it needs to send 3 pieces of data to an Excel spreadsheet. I have successfully created code to send the data to the already open Excel file. The problem I am running into is that I need to send the info to the currently selected cell in column A, and cells in column B and C of the same row. If I can achieve this, I would also like to move the selected cell down 1 row as the last step.

Here is the code I have so far:

' Send info to Excel 
        Dim xlApp As Object, xlSheet As Object
        Set xlApp = Getobject("C:\Users\jay3120\Desktop\Excel Test.xlsx")
        Set xlSheet = xlApp.activesheet
         
        StartLine = Sess0.Screen.GetString (4,19,9)
        Middle = Sess0.Screen.GetString (4,31,2)
        FinishLine = Sess0.Screen.GetString (6,13,24)
         
        xlSheet.Cells(5303, "A").Value = StartLine
        xlSheet.Cells(5303, "B").Value = Middle
        xlSheet.Cells(5303, "C").Value = FinishLine

So instead of row 5303, I want it to be the row wherever the currently selected cell is.
I am using Attachmate Extra VB

Thank you for any help.

 

[SkipVought]

Hi,

Some comments on your code:
I'd code in Excel VBA; much prefer a Caddilac to a Yugo.
I'd reference the next row/cell rather tha use Select & ActiveCell.
I'm guessing that the start/middle/finish are ALWAYS in this row/column on your screen. What are you doing; manually supplying the next key value from a list (maybe from Excel?) and hitting ENTER?

'
        StartLine = Sess0.Screen.GetString (4,19,9)
        Middle = Sess0.Screen.GetString (4,31,2)
        FinishLine = Sess0.Screen.GetString (6,13,24)
         
        With xlSheet.ActiveCell
           .Offset(0,0).Value = StartLine
           .Offset(0,1).Value = Middle
           .Offset(0,2).Value = FinishLine 
        End With

 

[SkipVought]

Also please explain the structure of your table in the active sheet.

Headings? On what row/column?

Any empty cells in the table?

For instance if you table started in A1, as most do, your next cell reference would be

   XlSheet.Cella(1,1).End(xlDown).offset(1)

 

[jay3120]

Thank you for your response Skip,

Unfortunately, The much larger portion of the macro is already in Attachmate VB rather than Excel VB, so I'm stickin with Attachmate.
Yes the StartLine, Middle, and FinishLine are always in the same row/column. I am simply taking 3 pieces of data from a single screen in Attachmate and manually entering them into a single row in Excel starting with column A. Then I go to the next row, find the correct Attachmate screen and manually enter the 3 pieces of data again. I can either hit Enter, use my arrows, or just click column A of the next row.

Your code looks good, but it doesnt work. Im not sure, but I dont think Attachmate VB cant use ActiveCell, as I have also tried this earlier. I was trying to use ActiveCell.Offset and I could not get it to work. This would be exactly what I would need though.

My Excel table does have Headings in row 1 columns A B & C. The empty cells are in the next rows and everthing above is filled for all 3 columns. My company is currently on row 5311 and it keeps growing and being saved over by multiple users, as it is a shared network file. So everytime I use it, the table is on a different row from where I last was. This is why I am trying to use the currently selected row/cell in my macro.

Thanks again.

 

[SkipVought]

That's one of the reasons I avoid Extra VB like the plague!

Use this code rather than xlSheet.ActiveCell, assuming the assumption I made in the previous post

XlSheet.Cells(1,1).End(xlDown).offset(1)

 

[remy988]

not the best method, but you can always perform a loop in column A to find the next blank cell; then you can use that as a row reference.
another way is to place the last row in another cell at the close of the script.

 

[jay3120]

I receive a syntax error when I try that.

I have found out that .Offset does work though. I also combined that with .Select successfully which will solve my last step of selecting the next row.

xlSheet.Cells(5303, "A").Offset(1).Select

Now I just need something like .ActiveCell that will recognize my current position, instead of directly referencing a cell.

 

[jay3120]

Do you think I might have to Activate the Window or Sheet first in order to get .ActiveCell to work? If so, how would I do this?

 

[SkipVought]

Please post your current code

 

[jay3120]

Its just a working code. I keep testing everything I can think of.

 ' Send info to Excel 
        Dim xlApp As Object, xlSheet As Object
        Set xlApp = Getobject("C:\Users\jay3120\Desktop\Excel Test.xlsx")
        Set xlSheet = xlApp.activesheet
         
        StartLine = Sess0.Screen.GetString (4,19,9)
        Middle = Sess0.Screen.GetString (4,31,2)
        FinishLine = Sess0.Screen.GetString (6,13,24)
         
        xlSheet.Cells(5303, "A").Value = StartLine
        xlSheet.Cells(5303, "A").Offset(0, 1).Value = Middle
        xlSheet.Cells(5303, "A").Offset(0, 2).Value = FinishLine 

        xlSheet.Cells(5303, "A").Offset(1).Select

Thanks

 

[SkipVought]

I assumed you were incorporating the code i sent.

THAT is what i want to see, along with the results you've been posting about.

 

[jay3120]

Thanks for helping me out Skip,

Here are a few things that I have tried and the errors received.

"No such property or method" line ".Offset(0,0)..."

        StartLine = Sess0.Screen.GetString (4,19,9)
        Middle = Sess0.Screen.GetString (4,31,2)
        FinishLine = Sess0.Screen.GetString (6,13,24)
         
        With xlSheet.ActiveCell
           .Offset(0,0).Value = StartLine
           .Offset(0,1).Value = Middle
           .Offset(0,2).Value = FinishLine 
        End With

"Syntax error" on each of these. Great idea as an alternative though.

XlSheet.Cells(1,1).End(xlDown).offset(1)  

XlSheet.Cells(1,1).End(xlDown).offset(1).Value = StartLine

This is exactly replacing what you suggested with what you originally sent. "WITH Syntax error" on every line.

        With XlSheet.Cells(1,1).End(xlDown).offset(1)
           .Offset(0,0).Value = StartLine
           .Offset(0,1).Value = Middle
           .Offset(0,2).Value = FinishLine 
        End With

Using 2 .offset does work though

xlSheet.Cells(1,1).offset(1).offset(1, 1).value = StartLine

"No such property or method" even still with xlSheet.Activate above it.

xlSheet.ActiveCell.offset(1, 1).value = StartLine

Same thing with xlApp instead of xlSheet

I have tried several other tweaks, but nothing seems to work.

 

[SkipVought]

As I stated earlier, I do not code in Extra, but I know that it is quirky.

Since you don't have a reference to the Excel Object Library, yet another shortcoming of Extra, you cannot use the Excel Constants like xlDown.

So substitute -4121 for xlDown in the With statement

'
        With XlSheet.Cells(1,1).End(-4121).offset(1)
           .Offset(0,0).Value = StartLine
           .Offset(0,1).Value = Middle
           .Offset(0,2).Value = FinishLine 
        End With

 

[jay3120]

No luck, and I tried with it just about every combination of parentheses, quotes, brackets and spaces. Still a syntax error.

 

[SkipVought]

Syntax error on what statement?

 

[jay3120]

It says "WITH syntax error" on the first line, "syntax error" on the next 3 lines, and "illegal statement" on End With.

 

[SkipVought]

In the Extra VB Help menu, look up With. It could be a VBA only construct.

If that's the case, use the statement without the With...End With

'
        XlSheet.Cells(1,1).End(-4121).offset(1).Offset(0,0).Value = StartLine
'...

and so on for each variable.

 

[jay3120]

With does exist in Extra. Unfortunately, I tried that code earlier, and again now, and there is still a syntax error.

 

[remy988]

is there any reason why you would not try to perform a loop in column a to get to the last row?

 

[jay3120]

Honestly, because I dont know how to yet. I will have to research it. Thank you for the tip.