Solved

Create excel application opening with pre-installed add-in

Posted on 2010-08-23
14
339 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

10 Experts available now in Live!

Get 1:1 Help Now