• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 859
  • Last Modified:

using Application.FollowHyperlink to open a xls based on a xlt

I'd like to open a excel workbook based on a template (xlt) from access. I'm using this code:

  If CanRead(es_RealisatieDT) = True Then
      Application.FollowHyperlink cdbDataPad & "Slabloon\Realisatie_001.xlt"
    End If
  End If

Instead of opening a xls based on the xlt this code opens the xlt.

Any suggestions

gr Leon
2 Solutions
Hello Leon,

The FollowHyperlink performs the "Open" action of the registered application for the file's extension. You want to perform the "Add" method of the Workbooks collection.

For example, add a reference to Excel (from VB, "tools / options", find and check "Excel ?.? Object Library") and do something like this:

Function WorkbookFromTemplate(pstrTemplate As String) As Excel.Workbook

    Dim xlapp As Excel.Application
On Error Resume Next
    ' find running instance of Excel:
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
        ' not found; start a new instance of Excel
        Set xlapp = CreateObject("Excel.Application")
        If Err Then
            MsgBox "Could not start Excel"
            Exit Function
        End If
        ' make the automated instance visible
        xlapp.Visible = True
        xlapp.UserControl = True
        ' activate and maximize the instance?
    End If
    ' xlapp is now pointing to a running instance
    ' create a document based on the template
    Set WorkbookFromTemplate = xlapp.Workbooks.Add(Template:=pstrTemplate)
    If Err Then
        MsgBox Err.Description
    End If

End Function

Good luck!
U could also use ShellExecute

eg add this lot to a module

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
  (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Sub OpenXLSViaTemplate(byval sFile as String)
    ShellExecute Application.hWndAccessApp, "", sFile, vbNullString, vbNullString, vbNormalFocus
End Sub

Now call OpenXLSViaTemplate passng in your xlt file
OpenXLSViaTemplate cdbDataPad & "Slabloon\Realisatie_001.xlt

Now here Im using the default action, which should be to create a new instance of it
If u right click on a xlt file, u will see the options like New, Open, Print etc
New is usually the default but to ensure u always select New, u can specify the action to do

    ShellExecute Application.hWndAccessApp, "New", sFile, vbNullString, vbNullString, vbNormalFocus
You back Rocki?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Na, I posted a question in the Java section. I thought a I'd do a quick visit here.
Also need to get min 3k points. Gonna be a case of popping in and out.
sojocaAuthor Commented:
Excellent answer, both. I like the ShellExecute more because of the the reference that should be used in Harfangs solution.

thanks Leon
Cool, glad to have helped
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now