Jump to content
BigDaddyO

Scroll Excel worksheet to first row (Solved)

Recommended Posts

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.

#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 by BigDaddyO

Share this post


Link to post
Share on other sites
Juvigy

$oExcel.Application.ActiveWindow.ScrollRow = 1

Share this post


Link to post
Share on other sites
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.


Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.