Solved

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

Posted on 2007-04-04
6
774 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now