How to get TOC shown in HTML Help when calling from VBA?

[myunker]

I have built a Help file using HTML Help Workshop (.chm file). When I call
the file from VBA (application.help filename) all I see is my default page.
There is no TOC, or search, or any other options that I see if I simply open
the .chm file by itself.
Any help would be appreciated.

 

[Mat P:son]

Instead of using the Application.Help method you can call the proper HTML Help:

==========================

' Used for the HtmlHelp() Win32 function
Private Const HhDisplayTopic As Long = &H0
Private Const HhHelpContext As Long = &HF

' Names on the HTML Help file and the HTML Help window
' TODO: Fill in your own names here!!!
Private Const XlHelpFile As String = "\XlAddIn.chm"
Private Const XlHelpWin As String = "Main"
Private Const XlHelpEntry As String = "Welcome.htm"

' The function declaration for HTML Help
' TODO: HHCtrl.ocx should already be present on all systems except
' perhaps some old NT4 boxes. Install HTML Help if/when required!
Private Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" ( _
ByVal hwndCaller As Long, _
ByVal pszFile As String, _
ByVal uCommand As Long, _
dwData As Any) As Long

.....

Private Sub ShowHtmlHelp()
' Show HTML Help for the add-in: the CHM file should be located
' in the same folder as the add-in, and we're opening the home page
Dim lResult As Long
lResult = HtmlHelp( _
0, _
ThisWorkbook.Path & XlHelpFile & ">" & XlHelpWin, _
HhDisplayTopic, _
ByVal XlHelpEntry)

If lResult = 0 Then
' Handle the error...
End If
End Sub

==========================

 

[Peter T]

Just to add, after using this code try closing the file (your xls or xla)
but with your help.chm still open. If I do that my Excel will crash. I
prevent this by closing the help first from the close event like this -

Const HH_CLOSE_ALL = &H12
HTMLHelp 0&, vbNullString, HH_CLOSE_ALL, 0&

I only do that having determined the chm is still open. I store the chm's
window handle and check if it still exists with the IsWindow API.

What puzzles me is I've tried other peoples where there is no problem to
allow the API opened chm to remain open after the xls/a with the API has
closed. Anyway worth checking with your setup.

If anyone knows why only sometimes it's necessary to ensure the chm is
closed I'd be very interested indeed!

Regards,
Peter T

 

[myunker]

Thanks Mat - that did the trick.

Additional issue now arises. My Excel spreadsheets are templates that I am
producing for some elementary school teachers. I KNOW they will simply put
these templates on some given disk on their system (e.g. their local account)
rather than in the templates directory. In so doing, when the template is
opened I don't see how I can determine where the original template location
was so that I can access the Help file. I can do a "file search " of course,
but depending on their server setup that could take hours to complete.

Any idea on how to determine where the template file was located? That way
the "new" workbook can find where the Help file was also installed and I can
then save the help file along with the new file. As it stands now the new
workbook tries to get the help file from "C:\" since it hasn't been saved
anywhere.

thanks

 

[Tom Ogilvy]

The workbook created from a template file has no knowledge of the location
of the template or any other association with it. In fact, if a template
has a workbook_open event, to avoid having the code run when the template is
edited, people add code to check if the workbook has a path - if it doesn't
they know it is not the template being opened but a workbook created from
the template.

Sounds like you need to distribute your program with an install routine.

 

[Mat P:son]

Luckily, there are two good (and free-of-charge) installers available for
Windows:

NSIS (from the NullSoft guys, who have written WinAmp)
[ http://nsis.sourceforge.net/Main_Page ]

InnoSetup (I've used it extensively in the past, and it's rock stable)
[ www.jrsoftware.org/isinfo.php ]

Good luck,
/MP