Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-04-04
6
Medium Priority
?
846 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 58

Assisted Solution

by:harfang
harfang earned 400 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 600 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

610 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