BigDaddyO Posted December 7, 2017 Posted December 7, 2017 (edited) I put together a function that will split a selected spreadsheet evenly into multiples while keeping the header row. It works fine, but for some reason when I open the split files it's always displaying where the last row would have been. I want it to be at A1. I'm using $oWB.ActiveSheet.Range("A1").Select at the end just before saving each split which works, that cell is selected, but it's still showing the worksheet at the bottom when opening it. I found some info about using .ActiveWindow.ScrollRow = 1 but that doesn't seem to do anything for me. Any ideas? Here is the function. expandcollapse popup#include <Excel.au3> $sMaster = FileOpenDialog("File To Split", @ScriptDir, "Excel (*.xls;*.xlsx)", 1) $iSplit = Number(InputBox("Number of Splits", "Please insert the number of splits to perform", 4, "", 300, 130)) _Excel_Split($sMaster, $iSplit, 1) MsgBox(0, "Done", "Finished the split") ;--------------------------------------------------------------------------------------------------------------- ; This function will split up the specified spreadsheet into X smaller spreadsheets ; The Row1 Header will be copied to the new spreadsheets as well ; If there are more splits than there are items in the spreadsheet, it will ignore others. ; ; $sExcelSpreadsheet = The full path to the spreadsheet we are going to split ; $iSplit = The total number of split files it should create ; $iHeader = If > 0 then it will not remove anything up to that row in any of the spreadsheets ;--------------------------------------------------------------------------------------------------------------- Func _Excel_Split($sExcelSpreadsheet, $iSplit, $iHeader = 1) ; 1. Make copies of the Excel spreadsheet ; 2. Remove X number of rows from each of the spreadsheets keeping track of what was removed from all previous ones $iCurrentRow = 0 $oExcel = _Excel_Open(False, False, False, True, True) ;Open a new excel item that we will use throughout this process ;Split up the selected Excel workbook Path\name. Will piece back together with _# to identify splits $sOutput = StringLeft($sExcelSpreadsheet, StringInStr($sExcelSpreadsheet, ".", 0, -1) - 1) $sExtension = StringRight($sExcelSpreadsheet, StringLen($sExcelSpreadsheet) - StringInStr($sExcelSpreadsheet, ".", 0, -1) + 1) ConsoleWrite("$sOutput = " & $sOutput & @CRLF & "$sExtension = " & $sExtension & @CRLF) For $s = 1 to $iSplit ConsoleWrite("Copying " & $sExcelSpreadsheet & " to " & $sOutput & "_" & $s & $sExtension & " and splitting up into chunks" & @CRLF) If FileCopy($sExcelSpreadsheet, $sOutput & "_" & $s & $sExtension, 9) = 0 Then ;Failed to copy ConsoleWrite("ERROR failed to copy (" & $sExcelSpreadsheet & ") to (" & $sOutput & "_" & $s & $sExtension & ")" & @CRLF) ContinueLoop ;Let's try the next one EndIf ;Open Spreadsheet we just saved ;Get the total row count and divide by the Ubound($aSplitInfo) $oWB = _Excel_BookOpen($oExcel, $sOutput & "_" & $s & $sExtension) $oWB.Worksheets(1).Activate $iTotalRows = $oWB.Activesheet.UsedRange.Rows.Count ;Figure out the chunk row count $iChunk = Round($iTotalRows / $iSplit, 0) ;Delete everything after the chunk we want to keep. Skip on last run since we want to ensure any left over stays in the last file If $s < $iSplit Then _Excel_RangeDelete($oWB.Worksheets(1), $iCurrentRow + $iHeader + $iChunk + 1 & ":" & $iTotalRows) If @error Then ConsoleWrite("Failed to delete " & $iCurrentRow + $iHeader + $iChunk + 1 & ":" & $iTotalRows & " from Sheet " & $s & " with error code " & @error & @CRLF) Else ConsoleWrite("Deleted extra rows " & $iCurrentRow + $iHeader + $iChunk + 1 & " to " & $iTotalRows & @CRLF) EndIf EndIf ; If $iCurrentRow > 0 then delete everything from $iHeader + 1 to $iCurrentRow If $iCurrentRow > 0 Then _Excel_RangeDelete($oWB.Worksheets(1), $iHeader + 1 & ":" & $iCurrentRow + $iHeader) If @error Then ConsoleWrite("Failed to delete initial data " & $iHeader + 1 & ":" & $iCurrentRow & " from Sheet " & $s & " with error code " & @error & @CRLF) Else ConsoleWrite("Deleted prior used rows " & $iHeader + 1 & " to " & $iCurrentRow + $iHeader & @CRLF) EndIf EndIf ;Select Cell A1 again so it's not at the bottom of the sheet when it's re-opened $oWB.ActiveSheet.Range("A1").Select ;~ $oWB.ActiveWindow.Scrollrow = 1 ;tried with = 1 and (1) and neither worked _Excel_BookSave($oWB) _Excel_BookClose($oWB) $iCurrentRow += $iChunk If $iTotalRows - $iCurrentRow <= 0 Then ExitLoop ;if we have exceeded the max row count, then exit out of the loop Next _Excel_Close($oExcel) EndFunc Thanks, Mike Edited December 11, 2017 by BigDaddyO
Juvigy Posted December 8, 2017 Posted December 8, 2017 $oExcel.Application.ActiveWindow.ScrollRow = 1 SkysLastChance 1
BigDaddyO Posted December 11, 2017 Author Posted December 11, 2017 On 12/8/2017 at 7:52 AM, Juvigy said: $oExcel.Application.ActiveWindow.ScrollRow = 1 Thank you Juvigy, that worked. I didn't realize you had to send it to the Application and not the workbook.
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