Scroll Excel worksheet to first row (Solved)

[BigDaddyO]

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]

$oExcel.Application.ActiveWindow.ScrollRow = 1

[BigDaddyO]

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.