Finding last row in an excel column

[Jewtus]

I'm working with an excel script that will write to the end of an excel file... This is what I have so far:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = True
$workbook=$oExcel.Workbooks.Open("\\sovplafpsop1\docs\SALPP\MNGR\03-MIS\01-Call Matrix\04 KPIs Daily Dashboard by Agent\This Month\02-Input File\Input File - test.xlsx")
With $workbook
    $test= .Cells(.Rows.Count, "B").End(-4162).Row
    MsgBox(0,"",$test)
EndWith
$oExcel.Quit()

I've tried a number of variations of $test, such as:

$test= .Cells(.Rows.Count, "B").End('-4162').Row

$test= .Cells(.Rows.Count, "B").End('xlUp').Row

$test=.Range("B1").End(4121).Select

$test=.UsedRange.Rows(.Rows.Count).Row

 

and none of them seem to work. Does anyone have any idea how I can get the last record in a specific column? I tried looking into the excel UDF but I didn't see anything that stood out.

[alienclone]

maybe this thread can help.

'?do=embed' frameborder='0' data-embedContent>>

[Jfish]

Yes, see this line below.

$LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row

[Jewtus]

maybe this thread can help.

'?do=embed' frameborder='0' data-embedContent>>

I took a look at that script but when I try to execute it, I get a data type error. I pulled excel)read out of the UDF and found that the error is on Case 1 and case else lines:

Else
        Local $oExcel = $oWorkbook.Parent
        Switch $iReturn
            Case 1
                $vResult = $oExcel.Transpose($vRange.Value)
            Case 2
                $vResult = $oExcel.Transpose($vRange.Formula)
            Case 3
                $vResult = $oExcel.Transpose($vRange.Text)
            Case Else
                $vResult = $oExcel.Transpose($vRange.Value2)
        EndSwitch
        If @error Then Return SetError(5, @error, 0)
    EndIf

I commented out those lines and the script executes but never displays an array.

 

 

Yes, see this line below.

$LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row

I'm not sure I understand what you mean here... I looked for that on the other board, but I did see it. Where does $xlCellTypeLastCell come from?

[JLogan3o13]

$xlCellTypeLastCell is an object model constant. You really should read up on the whole Excel model, to get a better understanding of how to work with it.

 

http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15).aspx

http://msdn.microsoft.com/en-us/library/office/ff196157(v=office.15).aspx

[Jewtus]

That's exactly what I needed! Thanks!

I was looking into the different object model constant but there are so many and I'm not a big fan of using Excel (I'd rather use a SQLDB to handle any data). 

Thanks again!

[water]

The wiki entry about the Excel UDF (please see my signature) also describes some of this special ranges.

[jdelaney]

That's exactly what I needed! Thanks!

I was looking into the different object model constant but there are so many and I'm not a big fan of using Excel (I'd rather use a SQLDB to handle any data). 

Thanks again!

You can use your excel as a database, and connect via adodb.  Search the forums, you'll find examples.

[Jewtus]

@water

I took a quick look and these should be helpful. The only time I actually use excel is when the end user refuses to learn anything outside the stuff they know (IE, I offer to build a quick GUI and they say they want an excel spreadsheet so they can manipulate the data). I'll look into it more when they request more specifically formatted excel files.

 

@jdelaney

I know that excel can be used as a DB, but the problem I have with excel is its responsiveness and multi-user access. Typically, I have at least 5 users using anything I build. When I use Excel, the users have to fight for access to the file (read only if its in use). One of the groups I support had about 35 people using an access DB and they said they had timeout, data corruption, and read/write issues all the time. When I looked at it, it turned out they had about 15 access DBs (because of the readwrite issues) that were porting data into various places with different formatting and it was way more complicated than it needed to be. I tried using SQLite because they wanted something that could be easily backed up and after about 2 weeks they said they were having issues with responsiveness (IE one person would send a record to SQLite and the other users would have to wait for the record to inject... the issue was compounded by the location they wanted to store the sqldb... a network drive that is outrageously slow). When I looked into it, they had entered more than 20,000 records in less then a week.The data reports they wanted were also problematic because they wanted a single spreadsheet with all the data (which in this case was about 400,000 rows and about 60 columns). Because the network drive is so slow, it was taking 20-30 minutes to execute the query and another 30-40 minutes to write the 17 MB excel file (i even used CSV files to try to get some speed).  

I had to migrate everything to a SQLDB and now the transactions execute in less than a second and that massive 17 MB file takes about 15 seconds to generate in the exact same location (I created a view in SQL with the sql query and linked it to excel so they can just hit refresh data to get the new info).

I've been trying to get the group to define what data they actually want to build them optimized queries and reports... but in all honesty, I don't think they know what they want.