Appending an excel file

[DustoBOE]

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

 

[DustoBOE]

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

 

[SkipVought]

hi,

'
    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")
    
    '[b]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.
        '[s]Sess0.Screen.WaitHostQuiet (g_HostSettleTime)[/s]
      
    'similarly you wait for confirmation that the asynchronous screen is at rest![/b]
        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 ("")
        '[s]Sess0.Screen.WaitHostQuiet (g_HostSettleTime)[/s]
        r = r + 1
    Loop Until objWorkBook.Worksheets("Sheet1").Cells(r, 1).Value = ""

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

oops,

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DustoBOE]

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

 

[SkipVought]

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

" throws a bug."

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

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DustoBOE]

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

 

[SkipVought]

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

You must use the [highlight]numeric valu[/highlight]e 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DustoBOE]

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

 

[SkipVought]

Did [highlight]this[/highlight] not work?

With objWorkBook.Worksheets("Sheet1")
   r = .Cells(.Cells.Rows.Count, 1).End([highlight]-4162[/highlight]).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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DustoBOE]

no, with the numeric value, still throwing an error.

 

[DustoBOE]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[remy988]

DustoBOE,

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

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

 

[SkipVought]

Sure, but I might do it like this

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

'
   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...
[tt]
[END] [Down Arrow]
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DustoBOE]

Man, both those worked, thanks Skip and Remy.