Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Create excel application opening with pre-installed add-in

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
potter09
Asked:
potter09
  • 7
  • 6
3 Solutions
 
Rory ArchibaldCommented:
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
 
potter09Author Commented:
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
 
Rory ArchibaldCommented:
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
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.

 
potter09Author Commented:
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
 
ragnarok89Commented:
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
 
Rory ArchibaldCommented:
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
 
potter09Author Commented:
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
 
Rory ArchibaldCommented:
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
 
Rory ArchibaldCommented:
What kind of add-in is it - .xla or dll?
0
 
potter09Author Commented:
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
 
potter09Author Commented:
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
 
Rory ArchibaldCommented:
That's because line 60 refers to xlApp2 not xlApp3. :)
0
 
potter09Author Commented:
You are the man!
0
 
potter09Author Commented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now