How to set active cell when exporting html table to Excel

Question

I have a function which exports an html table to excel, similar to this question: HTML Table to Excel Javascript

My problem is that by default, Excel selects the A1 cell when starting up, which is not desired. I want to either start with no selection whatsoever, or with another predefined cell (let's say A3).

Is there a way to do that? Can I add some markup in the generated html code to say "select A3"?

score 0 · Answer 1 · answered Dec 05 '16 at 13:16

If this Javascript is creating an excel workbook for you, then you can't add a macro to the workbook by default. You can however do the below, but i'd like to note that this is bad practice and I wouldn't reccomend it. Also noteworthy is that at least one cell has to be selected at all times for each worksheet, so you cant select 'nothing'

You can add an event handler to a personal excel workbook (if you need to create this, follow this guide), which if you have a standard file name for your HTML tables output, then this will select whatever cell you like when you open them in excel:

In ThisWorkbook of Personal, paste:

Private Sub Workbook_Open()
    Set ExcelEvents = New CExcelEvents
End Sub

Then create a new class module (Right Click ThisWorkbook, > Insert... > Class Module, and paste this code in:

Option Explicit

Private WithEvents XLApp As Application

Private Sub Class_Initialize()
    Set XLApp = Application
End Sub

Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
    If InStr(1, Wb.Name, "HTMLTableOut", vbTextCompare) > 0 Then Wb.ActiveSheet.Range("A3").Select
End Sub

Replace HTMLTableOut with text that you expect to exist in the filename, that is exclusive to tables exported by this process, and replace A3 with the cell you want to select.

... But that's a hell of a workaround for this.

Sorry if I wasn't clear, but the solution must be in the generated html output, not by manually doing stuff in Excel. Can I somehow insert the module code you provide in the html markup for excel to pick up? — pkExec, Dec 05 '16 at 13:28
No, I don't think so. As far as I know, there is no easy method for changing things like selection without actually opening excel at some point. You can push macro code into an excel document with some difficulty, but the selection would still change only when the file is first opened, and only after the users have enabled active content for the document, and they would need to do this for all documents that are created, instead of just the one personal file. — Andy Wynn, Dec 05 '16 at 13:46

score 0 · Accepted Answer · answered Dec 05 '16 at 16:28

After several hours of searching, I found the answer.

Add the following code before the </head> section of your html:

<!--[if gte mso 9]>
<xml>
    <x:ExcelWorkbook>
        <x:ExcelWorksheets>
            <x:ExcelWorksheet>
                <x:Name>Worksheet</x:Name>
                <x:WorksheetOptions>
                    <x:ActivePane>3</x:ActivePane>                  
                    <x:Panes>
                        <x:Pane>
                           <x:Number>3</x:Number>
                           <x:ActiveRow>3</x:ActiveRow>
                           <x:ActiveCol>2</x:ActiveCol>
                        </x:Pane>
                    </x:Panes>
                </x:WorksheetOptions>
            </x:ExcelWorksheet>
        </x:ExcelWorksheets>
    </x:ExcelWorkbook>
</xml><![endif]-->

This selects the B3 cell (ActiveCol:2=B, ActiveRow:3) when the user opens the generated excel file.

The pane number must be 3 for some reason. Not sure why. All I could find was this: http://dev.bowdenweb.com/css/v/ms-office/ofhtml9/Excel/xleleNumber.htm which mentions

When the data comes from a PivotList component, the Number subelement of the Pane element is required and contains the integer 3.

How to set active cell when exporting html table to Excel

2 Answers2