Solved

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

Posted on 2007-04-04
6
785 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:sojoca
6 Comments
 
LVL 58

Assisted Solution

by:harfang
harfang earned 100 total points
ID: 18856442
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
        Err.Clear
        ' 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
    Else
        ' 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
        Err.Clear
    End If

End Function


Good luck!
(°v°)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 150 total points
ID: 18857058
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
eg
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
eg

    ShellExecute Application.hWndAccessApp, "New", sFile, vbNullString, vbNullString, vbNormalFocus
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18857438
You back Rocki?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 65

Expert Comment

by:rockiroads
ID: 18857719
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.
0
 

Author Comment

by:sojoca
ID: 18862358
Excellent answer, both. I like the ShellExecute more because of the the reference that should be used in Harfangs solution.

thanks Leon
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18864167
Cool, glad to have helped
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question