Select cell/row in Excel and scroll it to top

Get help with using AutoHotkey and its commands and hotkeys
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Select cell/row in Excel and scroll it to top

09 Mar 2018, 16:05

Is there any way to select a cell/row and make it the first row without manually scrolling? I think VBA allows for it, but not sure if it can be COM scripted in AHK

Starting point I assume would look like this:

MyFile.Sheets(1).select
MyFile.ActiveSheet.Cells(10,1).select

Considering 10th row is the target row I want moved to top
opiuetasfd

Re: Select cell/row in Excel and scroll it to top

09 Mar 2018, 16:58

;) https://www.google.com/search?q=excel+vba+scroll+to+row
hidefguy wrote:Starting point I assume would look like this:

MyFile.Sheets(1).select
MyFile.ActiveSheet.Cells(10,1).select
Assuming "MyFile" is a Workbook object: MyFile.Application.ActiveWindow.ScrollRow := 10

Code: Select all

App := ComObjCreate("Excel.Application")
App.Visible := true
App.Workbooks.Add()
App.ActiveWindow.ScrollRow := 500
FanaticGuru
Posts: 1402
Joined: 30 Sep 2013, 22:25

Re: Select cell/row in Excel and scroll it to top

09 Mar 2018, 17:43

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.Goto(xlApp.Range("D125"),1)
This will make D125 the left top cell in the active sheet.

Also, almost everything that can be done in VBA can be done with COM as both tie into the same underlying system.

The syntaxes are different of course and there are a few exceptions.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Select cell/row in Excel and scroll it to top

09 Mar 2018, 20:22

Thanks guys. Tried this and got error ScrollRow not recognized

FileToName := "MyFile"
xlApp := ComObjActive("Excel.Application")
FileTo_Path := A_Desktop "\" FileToName ".xlsx"
FileTo := xlApp.Workbooks.Open(FileTo_Path)
FileTo.Sheets(1).ScrollRow := 10

This here works:
xlApp.Goto(FileTo.Range("A10"),1)

End goal is to scroll all sheets in the workbook in a loop. Tried this, and script completes with no action:
Loop, % FileTo
{
FileTo.Worksheets.Range("A10"),1
}

If I do the sheets individually like this it works, but just figured for sake of efficiency loop makes sense
xlApp.Goto(FileTo.Sheets(1).Range("A10"),1)
xlApp.Goto(FileTo.Sheets(2).Range("A10"),1)
xlApp.Goto(FileTo.Sheets(3).Range("A10"),1)
and so on.......
Last edited by hidefguy on 09 Mar 2018, 21:19, edited 7 times in total.
gregster
Posts: 4257
Joined: 30 Sep 2013, 06:48

Re: Select cell/row in Excel and scroll it to top

09 Mar 2018, 20:52

The method ScrollRow is for window objects, not for Sheets: https://msdn.microsoft.com/en-us/librar ... e.14).aspx
First activate a sheet, then scroll.
opiuetasfd

Re: Select cell/row in Excel and scroll it to top

09 Mar 2018, 21:15

opiuetasfd wrote:Application.ActiveWindow.ScrollRow
hidefguy wrote:Tried this and got error ScrollRow not recognized
Workbook.Sheets(1).ScrollRow
Sheet.ScrollRow does not exist. (See ScrollRow on this page? No.)
it should be Window.ScrollRow. (See ScrollRow on this page? Yes.)

.ScrollRow does not belong to a sheet, it belongs to the window.
hidefguy wrote:End goal is to scroll all sheets in the workbook in a loop like this I guess:

Code: Select all

FileToName := "MyFile"
xlApp := ComObjActive("Excel.Application")
FileTo_Path := A_Desktop "\" FileToName ".xlsx"
FileTo := xlApp.Workbooks.Open(FileTo_Path)
Loop % x1App.Worksheets.Count()
{
 FileTo.Goto(xlApp.Range("A10"),1)
}

Code: Select all

FileToName := "MyFile"
App := ComObjActive("Excel.Application")
FileTo_Path := A_Desktop "\" FileToName ".xlsx"
FileTo := App.Workbooks.Open(FileTo_Path)
for CurrentSheet in App.Worksheets
{
    CurrentSheet.Activate
    App.ActiveWindow.ScrollRow := 10  ; or: FileTo.Application.ActiveWindow.ScrollRow := 10
}
Also note that above you sometimes used xlApp and other times use x1App. These different names refer to different variables.
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Select cell/row in Excel and scroll it to top

09 Mar 2018, 21:26

opiuetasfd wrote:Also note that above you sometimes used xlApp and other times use x1App. These different names refer to different variables.
Thanks for info. You caught that x1 typo before I edited post. Not only time I screwed that up. FantasticGuru knows :)

This here worked gloriously:
for CurrentSheet in xlApp.Worksheets
{
CurrentSheet.Activate
xlApp.ActiveWindow.ScrollRow := 10
}

Return to “Ask For Help”

Who is online

Users browsing this forum: assak123, Google [Bot], marwanco, Preed, swagfag, vsub and 136 guests