Solved

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

Posted on 2007-04-04
6
812 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

733 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