Scrolling an Excel worksheet object back to A1 (top-left) from Access (AC2007)

[AOB]

Hi guys,

I have a function which exports some data to Excel, performs some work, and then attaches the final report to an e-mail. All works fine apart from one small piece which used to work, but doesn't now, and I can't figure out why?

As a result of various temporary columns being added / removed (for formulas to determine scope etc.), the main sheet ends up scrolled to the bottom of the used range. Before I attach it to the e-mail, I want to scroll the sheet back to the top-left corner (A1).

I was using this :

Public appExcel As Object   ' Excel Application object (late-bound)
....
Dim objWorksheet As Object  ' Excel Worksheet object (late-bound)
....
objWorksheet.Activate
With appExcel
    With .ActiveWindow
        .ScrollRow = 1
        .ScrollColumn = 1
    End With
End With

It worked up until this week, when I had to make some changes to the rest of the code to refine the output. Now, when it hits the ".ScrollRow = 1" line, I get RTE 91 :

Error 91: Object variable or With block variable not set

I don't understand quite why (this particular piece of code remains unchanged from before)

Any suggestions on how I can get the sheet to scroll to the top-left before I save it? (Such that, when subsequently attached to the e-mail, the recipient will open the workbook at the top-left cell and not sacrolled to the bottom)

Thanks!

Al

 

[Ranman256]

dont scroll, just go there...
Range("a1").select

 

[AOB]

Should have mentioned; top row (headers) is frozen, so selecting a cell in the first row doesn't necessarily scroll up...

 

[JHB]

I think we need to see the whole code.
I suppose you made a copy before you made the changes, does that copy run okay?
If you can remember exactly what code lines you changed, then mark them with another color when you post the code.

 

[GinaWhipp]

Select the first cell in the first row under the frozen headers, i.e.

xlWBk.Worksheets("YOURWORKSHEET NAME").Range("A5").Select

...in mine that is A5. Note, you need to use your designation for Workbook.

 

[AOB]

Thanks guys,

JHB, the code is mountainous - it was an already complex report made even more complicated by a requirement to sub-categorise a load of stuff. So dumping the whole code in here wouldn't really be feasible; plus I would probably struggle to remember all the changes I made. Suffice to say, yes, I did keep a copy from before I made the changes and yes, it still runs okay.

I went with Gina's suggestion of selecting the first cell in the first row under the frozen headers. (I'd already done that before your post Gina but delghted to see you suggesting the same thing!)

Thanks to all (would still like to know why the original method no longer works though?...)

 

[GinaWhipp]

Great minds think alike!

Can't help with the answer to your question, never used the Scroll method.

 

[vbaInet]

As a side note, whenever you're working in Excel always select the top-left "available" cell and save. Saving it is crucial because Excel will remember which cell the cursor was last in and it will move to that cell next time you open the spreadsheet. You don't need the scroll code.

 

[Rx_]

Will butt into this with this offering. For those who create multi worksheet reports.

Sub ScrollWSToTop()
'Each Sheet will scroll to top
Application.ScreenUpdating = False
Dim WScount
For WScount = 1 To Sheets.Count
 Worksheets(WScount).Activate
 ActiveWindow.ScrollRow = 1
Next
Application.ScreenUpdating = True
Exit Sub
 ' optional error trap
End Sub

My best guess is that the Worksheet needs to be activated. Activate the worksheet before applying your original code.
This code can be used with out the loop.

Might suggest ActiveWindow.ScrollRow = 1
This has an advantage over the solutions mentioned above.
It doesn't leave the Cell active so when the user opens it and accidentally hits the <space> bar - clears the data.
- Ok, maybe not the "users", its just me? LOL

On my Excel Reports, my header starts on line 5 and the scroll "Freeze Panes" start on Line 6. The solution above won't really address that.
My solution will bring all of the Frozen Paynes back up to the top regardless of where the Frozen Paynes are.

The reason it just started:
Search Microsoft TechNet for "Form Controls stop working after December 2014 Updates (Updated March 10, 2015)"
Down at the bottom, the Excel 2007 was also updated. The object model covered a big span of objects to close security and other gaps.
So, it is very possible that recently with security updates your Excel Object Model just got jacked!
At home my Credit Union portal for scanning checks for deposit stopped working right after this recent update when running on Windows 8. My Windows 7 was updated and still works fine.

However, the scrollrow = 1 should have the worksheet activated before being used.

It is my guess that this Scrollrow =1 problem appeared in excel 2013.
So, there was a patch.

Another question is: Do you use User Forms?
There is a direct problem with having a User Form and using the scrollrow = 1. It gets back to the worksheet not being activated. If that is the case, there are established process to unload the User form while Scrollrow = 1 is used.