Bearpocalypse Posted May 30, 2014 Share Posted May 30, 2014 We have a spreadsheet that tracks hardware that has or will be destroyed. Said spreadsheet is currently at 2863 rows. I've been messing around with some automation to remove the people problem element from this process and I have everything sorted out except getting the next unused cell to activate before writing data. Since the spreadsheet is rather large, I don't want to read it into an array. I have been using $newRow = $oExcel.ActiveCell.Row $newRow = $oExcel.ActiveCell.Row + 1 This works like a charm, IF the last cell used is where my script has left off. Example, if I run the script and data gets written to it and it ends at row 2864 and column I and then the spreadsheet saves, everything is all good. I tested this out and was able to write to each new row. But if I clicked on a different row and saved, then the row below it would be overwritten with data. Say I selected D:40 and saved the sheet. If I then ran my script, A:41 would be the next in line to be written to, not (just for semi-simplicity based on the next empty row being 2863) A:2864. I have trolled over the internet and the forums here and have tried different things. I saw someone use $newRow = $oRange.SpecialCells($xlCellTypeLastCell).Activate But alas, that did not work for me. An example of what I am doing is: ;Sets up the Excel sheet. $oExcel = ObjCreate("Excel.Application") $oBook = $oExcel.Workbooks.Open("SomeWillyNillyThing.xlsx") $oExcel.Visible = True $oSheet = $oBook.Worksheets(1) $oSheet.Activate $oRange = $oSheet.UsedRange $newRow = $oExcel.ActiveCell.Row $newRow = $oExcel.ActiveCell.Row + 1 $oExcel.Range("A" & $newRow).Activate With $oExcel .Range('A' & $newRow).Interior.Color = 65535 .Range('A' & $newRow).Borders.LineStyle = $xlContinuous .range('A' & $newRow).Borders($xlEdgeBottom).LineStyle = $xlContinuous .range('A' & $newRow).Borders($xlEdgetop).LineStyle = $xlContinuous .range('A' & $newRow).Borders($xlEdgeright).LineStyle = $xlContinuous .range('A' & $newRow).Borders($xlEdgeLeft).LineStyle = $xlContinuous EndWith _ExcelWriteCell($oExcel, 'My Data Here', $newRow, 1) This goes on checking different variables and writing data. It's not the most efficient way, but I'm wanting to make it work before I make it efficient. Any help is appreciated. Please let me know if I need to clarify anything. Link to comment Share on other sites More sharing options...
water Posted May 30, 2014 Share Posted May 30, 2014 If all rows starting with row one are filled you could use "UsedRange" to get the last used row. $iLastUsedRow = $oSheet.UsedRange.Rows.Count My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Solution Bearpocalypse Posted May 31, 2014 Author Solution Share Posted May 31, 2014 I figured it out! All it took was making a thread about it for me the little light bulb to go CLICK! I created a new spreadsheet and copied over the data to it. That way, ALL 65000 are not formatted. Then suddenly this code magically worked. $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 And thank you Water, I came across a reply you had made about the formatting of unused cells screwing things up. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now