×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Send data from Attachmate Extra to current selected cell in Excel

Send data from Attachmate Extra to current selected cell in Excel

Send data from Attachmate Extra to current selected cell in Excel

(OP)
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:

CODE --> VB

' 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.

RE: Send data from Attachmate Extra to current selected cell in Excel

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?

CODE

'
        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 

RE: Send data from Attachmate Extra to current selected cell in Excel

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

CODE

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

RE: Send data from Attachmate Extra to current selected cell in Excel

(OP)
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.

RE: Send data from Attachmate Extra to current selected cell in Excel

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

CODE

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

RE: Send data from Attachmate Extra to current selected cell in Excel

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.

RE: Send data from Attachmate Extra to current selected cell in Excel

(OP)
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.

CODE --> ExtraVB

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.

RE: Send data from Attachmate Extra to current selected cell in Excel

(OP)
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?

RE: Send data from Attachmate Extra to current selected cell in Excel

Please post your current code

RE: Send data from Attachmate Extra to current selected cell in Excel

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

CODE --> ExtraVB

' 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

RE: Send data from Attachmate Extra to current selected cell in Excel

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.

RE: Send data from Attachmate Extra to current selected cell in Excel

(OP)
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)..."

CODE --> ExtraVB

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.

CODE --> ExtraVB

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.

CODE --> ExtraVB

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

CODE --> ExtraVB

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

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

CODE --> ExtraVB

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.

RE: Send data from Attachmate Extra to current selected cell in Excel

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

CODE

'
        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 

RE: Send data from Attachmate Extra to current selected cell in Excel

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

RE: Send data from Attachmate Extra to current selected cell in Excel

Syntax error on what statement?

RE: Send data from Attachmate Extra to current selected cell in Excel

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

RE: Send data from Attachmate Extra to current selected cell in Excel

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

CODE

'
        XlSheet.Cells(1,1).End(-4121).offset(1).Offset(0,0).Value = StartLine
'... 
and so on for each variable.

RE: Send data from Attachmate Extra to current selected cell in Excel

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

RE: Send data from Attachmate Extra to current selected cell in Excel

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

RE: Send data from Attachmate Extra to current selected cell in Excel

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

RE: Send data from Attachmate Extra to current selected cell in Excel

I'd avoid loops when other more direct methods are available.

[code]
With xlSheet.Cells(1,1).CurrentRegion
NextRow = .Row + .Rows.Count
End With

With xlSheet.Cells(NextRow,1)
'Put your .offset for you 3 variables here
End With

RE: Send data from Attachmate Extra to current selected cell in Excel

here's a formula that you can use to return the last row:
=MAX(IF(ISBLANK(A1:A9001),"",ROW(A1:A9001)))

enter as an ARRAY (Control + Shift) in a blank cell that you do not use.


RE: Send data from Attachmate Extra to current selected cell in Excel

(OP)
That was it! It works perfect. You're the man Skip. Thanks for the help, I couldn't have done it myself.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close