×
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

Appending an excel file

Appending an excel file

Appending an excel file

(OP)
' Make Excel visible on the screen
objExcel.Visible = True

' Create a new Workbook
Set objWorkBook = objExcel.Workbooks.Open("C:\Users\*******\Desktop\testcopy.xlsx")

Do

Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
gs = Sess0.Screen.GetString (03, 13, 06) 'Gets sup name
objWorkBook.WorkSheets("Sheet1").Cells(r,1).Value = gs 'Transfers case number to Excel
gs = Sess0.Screen.GetString (14, 19, 11) 'auditor
objWorkBook.WorkSheets("Sheet1").Cells(r,2).Value = gs 'Transfers auditor to Excel
gs = Sess0.Screen.GetString (14, 48, 10) 'Gets aud name
objWorkBook.WorkSheets("Sheet1").Cells(r,3).Value = gs 'Transfers supervisor to Excel


Sess0.Screen.Sendkeys("")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
r=r+1
Loop Until objWorkBook.WorkSheets("Sheet1").Cells(r,1).Value = ""

' Excel will remain open after this Sub ends.
' To close out Excel, unremark the following 4 lines of code. .
objExcel.ActiveWorkBook.Save
'objWorkBook.Close
'objExcel.Quit
'set objWorkBook = Nothing
'set objExcel = Nothing




So I'm working with the above code. I simply want to capture some info on a screen, and paste it into different columns. However, the excel file will be added to. So what I want to do it to check each row for a blank cell in Column 1, and if the cell is blank, then paste the information in that row. It the cell already has data, then move to the next row. I can Get info to the workbook, but I'm not sure how to check for a new empty row in the workbook to pate the info into.

Thanks in advance!!

RE: Appending an excel file

(OP)
That loop in there is not needed, I was trying a buch of stuff and can't edit the post

the following lines should not be in there...
DO..
r=r+1..
Loop....

RE: Appending an excel file

hi,

CODE

'
    Dim r As Long
    
    ' Make Excel visible on the screen
    objExcel.Visible = True
    
    ' Create a new Workbook
    Set objWorkBook = objExcel.Workbooks.Open("C:\Users\*******\Desktop\testcopy.xlsx")
    
    'find the NEXT empty row in column A
    With objWorkBook.Worksheets("Sheet1")
        r = .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
    End With
    
    Do
    'do you stop at at red traffic light and ALWAYS wait x seconds???
    'of course not! So why do you do that here?
    'you wait at the traffic light until you get verification that you can safely go.
        'Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
      
    'similarly you wait for confirmation that the asynchronous screen is at rest!
        Sess0.Screen.MoveRelative 1, 1
        Do Until Sess0.Screen.WaitForCursor
            DoEvents
        Loop

        gs = Sess0.Screen.GetString(3, 13, 6)    'Gets sup name
        objWorkBook.Worksheets("Sheet1").Cells(r, 1).Value = gs 'Transfers case number to Excel
        gs = Sess0.Screen.GetString(14, 19, 11)  'auditor
        objWorkBook.Worksheets("Sheet1").Cells(r, 2).Value = gs 'Transfers auditor to Excel
        gs = Sess0.Screen.GetString(14, 48, 10)  'Gets aud name
        objWorkBook.Worksheets("Sheet1").Cells(r, 3).Value = gs 'Transfers supervisor to Excel
        
        
        Sess0.Screen.SendKeys ("")
        'Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        r = r + 1
    Loop Until objWorkBook.Worksheets("Sheet1").Cells(r, 1).Value = "" 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

oops,

I failed to indicate that the WaitForCursor requires a set of screen coordinates (row, col) that indicate THAT SCREEN's rest coordinates.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file



Also, IMNSHO, the Attachmate Extra! VB Editor/Language is analogous to a Yugo, while the Excel VBA Editor/Language is like driving a fully loaded Cadillac. Not to mention that nearly all the data I manipulate in Extra, start or end up in Excel.

PS: I don't drive a Yugo!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

(OP)
Hi Skip, thanks for the input.

I'm new to all this programming and have only taken one VB course so I'm trying my best to figure all this stuff out.

As far as the wqaitforcursor, that's a good idea, I'll work on that.

I still don't understand how to get to the next row. That bolded tidbit of code you wrote throws a bug.

With objWorkBook.Worksheets("Sheet1")
r = .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
End With

RE: Appending an excel file


yea, I thought of that and then promptly forgot to follow up (forgive me for my senility at 71).

" throws a bug."

CODE

With objWorkBook.Worksheets("Sheet1")
r = .Cells(.Cells.Rows.Count, 1).End(-4162).Offset(1).Row 
End With 

Quote (Excel_VBA_Help)


XlDirection Enumeration
Specifies the direction in which to move.
Version Information
Version Added: Excel 2007
Name      Value  Description 
xlDown    -4121  Down. 
xlToLeft  -4159  To left. 
xlToRight -4161  To right. 
xlUp      -4162  Up.  

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

(OP)
I can tell this editor is not very good. And if you did drove a Yugo, I would not think any less of you LoL!

Will attachmate read that xl enmueration. Because I cant get it to work :(

RE: Appending an excel file

No, not unless you can attach the dynamic link library (.dll) for the Excel version you have.

You must use the numeric value that the xlUP Excel Application Constant represents.

In the Excel VBA Editor, one can attach the Attachmate Extra! 6.5 Object Library via Tools > Reference.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

(OP)
Is the only way to accomplish what I want through ExcelVBA?

RE: Appending an excel file

Did this not work?

CODE

With objWorkBook.Worksheets("Sheet1")
   r = .Cells(.Cells.Rows.Count, 1).End(-4162).Offset(1).Row 
End With 

One CAN perform the task in Extra VB.

However, it is my contention, that one can perform that same task much easier, and perhaps better, in Excel VBA, at least that is my experience.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

(OP)
no, with the numeric value, still throwing an error.

RE: Appending an excel file

(OP)
I'd use excel VBA, but I don't really know how to interact with it.

RE: Appending an excel file

It is quite simple. In ANY Microsoft Office Application, alt+F11 toggles between the VB Editor and the application GUI.

Check VBA Help.

In Excel, each procedure is best coded in a Module, that must be INSERTED in the Project Explorer ctr+r.

When you save the workbook (as a Macro Enable Workbook .xlsm) the code is saved with it.

Post VBA questions in forum707: VBA Visual Basic for Applications (Microsoft). This forum is good for Extra Object questions.

If you code in Excel VBA, you only need CreatObject() etc. for the Extra application, the Excel application is implied in Excel VBA.

Post your specific relevant questions here or in forum707: VBA Visual Basic for Applications (Microsoft).

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

DustoBOE,

see if this makes any sense to you. it's basically checking the cell value.

CODE

do
if trim(objWorkBook.WorkSheets("Sheet1").Cells(r,1).Value) = "" then
objWorkBook.WorkSheets("Sheet1").Cells(r,1).Value = gs
exit do
else
'continue testing for the next row 
end if
loop 

RE: Appending an excel file


Sure, but I might do it like this

CODE

'
    Do
        With objWorkBook.Worksheets("Sheet1").Cells(r, 1)
            If Trim(.Value) = "" Then
                .Value = gs
                Exit Do
            Else
            'continue testing for the next row
                r = r + 1
            End If
        End With
    Loop 

Or you could get there much more directly, assuming that DATA in column A begins in row 2...

CODE

'
   with objWorkBook.Worksheets("Sheet1")
      .Cells(.Cells(2, 1).End(xlDown).row + 1, 1). value = gs
   end with 

The End(xlDown) is like performing these keystrokes on your Excel sheet, where [END] is the END Key...

[END] [Down Arrow]

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Appending an excel file

(OP)
Man, both those worked, thanks Skip and Remy.

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!

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