Select cell/row in Excel and scroll it to top

[hidefguy]

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]

https://www.google.com/search?q=excel+vba+scroll+to+row

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]

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

[hidefguy]

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.......

[gregster]

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]

Application.ActiveWindow.ScrollRow

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.

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]

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
}