First blank row in Excel

[reggie55555]

I think this is a simple one but I can't get the syntax correct. I need to find the first blank row in excel and input some data.

I can do this in VB but can't quite tweak it to work with Extra. Any suggestions?

Here is my macro, I want to go to the first blank row instead of "A1"

Sub Main
Dim objxl as Object, wb As Object
Dim sUserName As String, stime
    sUserName = UCase$(Environ$("username"))
    Set objxl = CreateObject("Excel.Application")
    stime = Format (cVar(Time), "hh:mm:ss")
With objxl 
	Set wb = .Workbooks.Open ("C:\Documents and Settings\" + sUsername + "\Desktop\test214.xls") 
	With wb.Sheets(1)
                .Range("a1").Value = sUsername+", "+Date+", "+stime+", Macro Name" 
	        '.PrintOut 1, True 'send to printer
	End With
	wb.Close True
	Set wb = Nothing
	.Quit
End With
Set objXL = Nothing
End Sub

 

[SkipVought]

hi,

    With wb.Sheets(1)
                .Range("a1")[b].End(xldown).Offset(1)[/b].Value = sUsername+", "+Date+", "+stime+", Macro Name" 
            '.PrintOut 1, True 'send to printer
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[reggie55555]

Thanks for that Skip. I've been messing around with the End(xldown) and I keep getting a syntax error. It's specifically the End(xldown) that doesn't work. Any thoughts?

 

[vzachin]

hi, i would test each cell until i find a blank.

rw = 2
for x = rw to obj.ActiveSheet.Rows.Count   

Dat = .cells(x,1)
if Dat = "" then exit for
next x

 

[SkipVought]

xldown is an Excel constant. If you do not have the Excel Object Library available, then you'll need to use the VALUE that that constant represents. I am ont on a computer that has Excel, but you can go to

http://www.microsoft.com

and find the value, I am sure.

However, whenever I code in Attachmete, I ALWAYS write my code in the language that is richer, which in this case would be Excel VBA BY FAR!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[reggie55555]

Skip, I added the constant but it is a Syntax problem. I believe Extra will not allow the word End in there.
I do have access to the excel object library.

Sub Main()
Dim objxl as Object, wb As Object
Dim sUserName As String, stime
    [B]Const xlDown = -4121[/B]
    sUserName = UCase$(Environ$("username"))
    Set objxl = CreateObject("Excel.Application")
    If objxl Is Nothing Then Exit Sub
    stime = Format (cVar(Time), "hh:mm:ss")
    objxl.Visible= True
With objxl 
        Set wb = .Workbooks.Open ("C:\Documents and Settings\" + sUsername + "\Desktop\testvbs2.xls") 
   	With wb.Sheets(1)
        .Range("a1").end(xldown).Offset(1).Value = sUsername+", "+Date+", "+stime+", Macro Name" 
        End With
        wb.Close True
	Set wb = Nothing
	.Quit
End With
Set objXL = Nothing
End Sub

 

[reggie55555]

Thanks vz. I used a similar method and it works. Unfortuntaely it checks all 65536 rows so it's taking about 20 seconds to complete. I need it to work in millaseconds.

My intended use is to embed this in other macros that are being used by many users, so that I can do some analysis on usage.

Sub Main()
Dim objxl as Object, wb As Object
Dim sUserName As String, stime, BCell, NBCell
    sUserName = UCase$(Environ$("username"))
    Set objxl = CreateObject("Excel.Application")
    If objxl Is Nothing Then Exit Sub
    stime = Format (cVar(Time), "hh:mm:ss")
    'objxl.Visible= True
With objxl 
    Set wb = .Workbooks.Open ("C:\Documents and Settings\" + sUsername + "\Desktop\test284.xls") 
    With wb.Sheets(1)
  
        [B].Range("A1").Select
            For I = 1 To 65536
                If objxl.activecell.Value = Empty Then
                    BCell = "A" & CStr(I - 1)
                    NBCell = "A" & CStr(I - 2)
                Else
                    .Range("A" & CStr(I + 1)).Select
                End If
            Next I
        objxl.activecell.Value = sUsername+", "+Date+", "+stime+", Macro "[/B]
        
     End With
        wb.Close True
	Set wb = Nothing
	.Quit
End With
Set objXL = Nothing
End Sub

 

[vzachin]

hi reggie, i have never had any luck making this work with xldown or xlup...

it really doesn't check all 65536 rows...if it did, you wouldn't have much more room to navigate :-D

one other way is to put a formula in a cell

 xl.range("b1").FormulaArray = _
        "=MAX(IF(ISBLANK(R[4]C[3]:R[5000]C[3]),"""",ROW(R[4]C[3]:R[5000]C[3])))"

you would need to adjust the cell,R,C

 

[reggie55555]

Thanks to both of you for the help. I'm going to stick with the code I pasted above and change the rows to 1000, which will suit my needs and speed up the macro.

 

[SkipVought]

i have never had any luck making this work with xldown or xlup.

That is bacause you must have the Excel Object Library referenced in your Attachmate VB editor. I'm not sure that that is possible.

It IS however, possible, and in my pea brain's eye, preferable to code Attachmate Basic in Excel VBA. It's like saying, "I'd ruther drive to work in my SUV than pedal my bike in the rain."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[P2A3W4E5]

Try this


Private Function FindEmptyRow() As Integer
Dim intEmptyRow As Integer
Dim strRowAddress As String
Dim objRow As Object
Dim objExcel As object
Dim objSheet As Object

objExcel = GetObject(, "Excel.Application")
objSheet = objWorkBook.Worksheets(1)

With objSheet.Range("A1:A64000")
objRow = .Find("", LookAt:=1)
If Not objRow Is Nothing Then
strRowAddress = objRow.Address
intEmptyRow = Val(Mid(strRowAddress,4))
Return intEmptyRow
End If
End With

End Function