Solved

Create excel application opening with pre-installed add-in

Posted on 2010-08-23
14
352 Views
Last Modified: 2012-05-10
Hi,

I'm trying to create a file to store on my desktop which i can use to open all the application i used everyday (usually i leave my PC open at night but this is usefull after a restart). I wantt o do the following:

1. Open Outlook (and the VbaProject.OTM normally opening with it because I have macros running when mail arrive in Outlook)
2. Open a file containing timers for several macros running during the day
2. Open 2 others excel applications
- one with a file (also with a timer)
- one with 2 files with password protection (this needs to load all the usual add-ins, especially Bloomberg add-in (a data provider)).

I've writen a small code but somehow the excel appication don't open the same way as when i go to the start menu and open excel. The add-ins don't open as usual. Any idea why? As i only know VBA, i've written this code in a macro running when an excel file is open. It opens the rest and then close the file containing this macro. If someone knows a better way (a scipt or bat file?)

Addins not openning:
- ATPVBAEN.XLA
- FUNCRES.XLA
- 3 Bloomberg installed addins
- UBS installed addin

Thanks!

Open in new window


Option Explicit

Const MyRoot As String = "Y:\"

Private Sub Workbook_Open()

Dim Xlapp2 As Object, Xlapp3 As Object
Dim OutlookApp As Object
Dim Xlwb1 As Workbook, Xlwb2 As Workbook, Xlwb3 As Workbook
Dim Path1 As String, Path2 As String, Path3 As String, Path4 As String


Set OutlookApp = CreateObject("Outlook.Application")
OutlookApp.Visible = True

Path1 = MyRoot & "Excel_Models\DailyUpdater.xls"
    Set Xlwb1 = Application.Workbooks.Open(Path1)
    Application.Run "DailyUpdater.xls!StartTimerg10"

Set Xlapp2 = CreateObject("Excel.Application")
    Path2 = MyRoot & "Excel_Models\UBSVOLS_Timer.xls"
    Set Xlwb2 = Xlapp2.Workbooks.Open(Path2)
    Xlapp2.Run "UBSVOLS_Timer.xls!StartTimerUBSvols"
    Xlapp2.Visible = True
    
Set Xlapp3 = CreateObject("Excel.Application")
    Xlapp3.Visible = True
    Path3 = MyRoot & "TRADING\GCF\Live_NAV.xls"
    Set Xlwb3 = Xlapp3.Workbooks.Open(Path3, Password:="gcf")
    Xlapp3.Run "Live_NAV.xls!SnapshotG10"

    Path4 = MyRoot & "TRADING\UCITSIII\Live_UCITS_NAV.xls"
    Set Xlwb3 = Xlapp3.Workbooks.Open(Path4, UpdateLinks:=xlUpdateLinksAlways, Password:="ucits")


Set Xlapp2 = Nothing
Set Xlapp3 = Nothing

ThisWorkbook.Close

End Sub

Open in new window

0
Comment
Question by:potter09
  • 7
  • 6
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33500201
You need to either specifically open the add-ins (using Workbooks.Open if they are .xla files) or toggle their Installed property to False and back to True. For example:
With xlApp3.Addins("Analysis Toolpak")
   .Installed = False
   .Installed = True
End With
0
 

Author Comment

by:potter09
ID: 33500217
isn't there a way to open excel from the usual place instead of creating an "empty" object and filling it with add-ins ? It would make sure it keeps all the add-in I've currently installed and I wouldn't need to change the code later if i install more add-ins...

i'll give a try to the installed property as well
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33500249
Not if you use CreateObject, no, but you can simply loop through all the addins:
For each objAddIn in  xlApp3.Addins
If objAddIn.Installed then
   objaddin.Installed = False

   objaddin.Installed = True
End If
Next objAddin

Open in new window

0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:potter09
ID: 33500276
Thanks rorya,

is there another way than using CreateObject ?

[OutlookApp.Visible = True] actually doesnt work. I cannot ee outlook openned and teh property is not supported. Could I not just go to the programs and open the application ?

0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 33500304
For opening other excel files, I've always used:

Sub openfiles()
   Workbooks.Open Filename:="Y:\foldername\excelfilename.xls"
End Sub


If your addins aren't opening, go to Tools > Addins and make sure your addins are checked. If they don't appear, click "browse" and navigate to them, and install them.

In the case of bloomberg addins, we use these as well, and when we have issues with them, we simply click Start > Bloomberg > and select the addin name from the menu to re-install it. We use BB professional.

Al
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 33500314
You can just shell outlook since you don't manipulate it in your code:
shell "c:\program files\microsoft office\office12\outlook.exe", vbNormalNoFocus




0
 

Author Comment

by:potter09
ID: 33500334
Thanks,

I think it works except for one add-ins:

it gives me an error message when opening and i think it's coming from this section of the code:
Maybe it cannot load my user preferences?

'' load the settings...
Public Sub LoadSettings()
   
    On Error GoTo skip
     
    Dim preferenceValue As String
    preferenceValue = VolFeeder.Config.getRegistryProperty("TimerInterval")
       
    '' set the value
    VolFeeder.myTimerInterval = CInt(preferenceValue)
    Exit Sub
   
skip:

    VolFeeder.myTimerInterval = 0

    Call logErrorDetails("Wasn't able to load settings!", "Loading settings failed: " + Err.Description + ". Source: " + Err.Source + ".")

    MsgBox prompt:="There has been a problem loading your preferences from file ! They will be lost," & Chr(10) & Chr(13) & _
        "so you may want to reset the timer delay from the VolFeeder menu if you use this feature !", Title:=myAppName
   
End Sub
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33500352
I don't have a clue what VolFeeder is or what it does, so I can't really help you there, I'm afraid.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33500356
What kind of add-in is it - .xla or dll?
0
 

Author Comment

by:potter09
ID: 33500434
it's a .xla developped by an external party that feeds data.

But i need this add-in only for one file so I'll open that file on the application where the code is written (because it' open mnually without CreateObject, and will open the other excel using the createobject.

I just need to find where myoutlook exe file is stored to use you shell method but it should work.

thanks a lot!
0
 

Author Comment

by:potter09
ID: 33500733
Hi rorya,

It all works but I've a problem with the add-ins of the xlapp3. They don't open with the installed = true method...


Option Explicit

Const MyRoot As String = "Y:\"

Private Sub Workbook_Open()

Dim Xlapp2 As Object, Xlapp3 As Object
Dim OutlookApp As Object
Dim Xlwb2 As Workbook, Xlwb3 As Workbook, Xlwb4 As Workbook
Dim Path1 As String, Path2 As String, Path3 As String, Path4 As String
Dim objAddin
    
Dim oSvc, oProcess, strProcess, nProcessID

'
'Launch Outlook if not already launched
    strProcess = "Outlook.exe"
    nProcessID = 0
    
    Set oSvc = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
    For Each oProcess In oSvc.InstancesOf("Win32_Process where Name='" & strProcess & "'")
        nProcessID = oProcess.ProcessID  'If process is running it will have a process id
    Next
    
    If Not nProcessID > 0 Then
        Shell "C:\Program Files\Microsoft Office\OFFICE11\outlook.exe", vbNormalNoFocus
    End If

'
'Create New Excel Application and start add-ins
Set Xlapp2 = CreateObject("Excel.Application")
    Xlapp2.Visible = True
    
    Path2 = MyRoot & "Excel_Models\DailyUpdater.xls"
    
    
    On Error Resume Next
    For Each objAddin In Xlapp2.AddIns
        If objAddin.Name <> "VolFeeder.xla" Then
            If objAddin.Installed Then
               objAddin.Installed = False
    
               objAddin.Installed = True
            End If
        End If
    Next objAddin
    On Error GoTo 0
    
    Set Xlwb2 = Xlapp2.Workbooks.Open(Path2)
    Xlapp2.Run "DailyUpdater.xls!StartTimerg10"

'
'Create New Excel Application and start add-ins
Set Xlapp3 = CreateObject("Excel.Application")
    Xlapp3.Visible = True
    Path3 = MyRoot & "TRADING\GCF\Live_NAV.xls"
    Set Xlwb3 = Xlapp3.Workbooks.Open(Path3, Password:="xxx")
    
    On Error Resume Next
    For Each objAddin In Xlapp2.AddIns
        If objAddin.Name <> "VolFeeder.xla" Then
            If objAddin.Installed Then
               objAddin.Installed = False
    
               objAddin.Installed = True
            End If
        End If
    Next objAddin
    On Error GoTo 0
    
    Xlapp3.Run "Live_NAV.xls!SnapshotG10"

    Path4 = MyRoot & "TRADING\UCITSIII\Live_UCITS_NAV.xls"
    Set Xlwb4 = Xlapp3.Workbooks.Open(Path4, UpdateLinks:=xlUpdateLinksAlways, Password:="xxx")
    
    
'
'Open UBS Timers in current Excel
Path1 = MyRoot & "Excel_Models\UBSVOLS_Timer.xls"
Application.Workbooks.Open (Path1)
Run "UBSVOLS_Timer.xls!StartTimerUBSvols"

'
'Close Current File (StartAll.xls) and set xlapps = nothing
Set Xlapp2 = Nothing
Set Xlapp3 = Nothing

Xlwb2 = Nothing
Xlwb3 = Nothing
Xlwb4 = Nothing

On Error Resume Next
Workbooks("StartAll.xls").Close SaveChanges:=False
On Error GoTo 0

End Sub

Open in new window

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 33500783
That's because line 60 refers to xlApp2 not xlApp3. :)
0
 

Author Closing Comment

by:potter09
ID: 33501109
You are the man!
0
 

Author Comment

by:potter09
ID: 33501727
Hi Rorya,

I tried to have this macro a bit moer solid, by checking if the files were already open before trying to open them but the difficulty is that it might be opened in different excel instance.

I could use something like
set xlbook = nothing
on error resume next
set xlbook = workbooks("Books1")
on error go to 0

if xkbook is nothing = true then ... 'file is not open yet

The problem is that i don't know how to check that in all excel application currently opened.

I tried the getobject property but this gives me weird result, it open the file in the current excel application but only in the background. So i cannot see it but it opens it in the VBA project library. I can see it there but not in excel. Any idea?

Path2 = MyRoot & "Excel_Models\DailyUpdater.xls"
On Error Resume Next
Set Xlwb2 = GetObject(Path2)
On Error GoTo 0

If Xlwb2 Is Nothing = True Then '...

even if DailyUpdater was not open before, it is "sort of" open after the getobject...

It works great if the file is not open yet: it wont open it again. But it doesnt work it the file is not yet open...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

830 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