6 Replies Latest reply on Jul 6, 2010 9:46 PM by mziegler

    Opening Excel via VBA loses DataLink

    scm0094

      I have put together a toolbar for users to be able to click on buttons to open various items.  Some of these items are Excel workbooks.  The code below shows the code that I'm using to open the workbooks.  The two "Addins" lines in "Button1_Click" are a work around for an issue that I'm having.  Without these two lines Excel opens and the PI DataLink addin is selected, but there is no "PI" menu item and DataLink does not work.  I would like to remove the two "Addins" lines from the code and have Excel open correctly.  Any suggestions?  I'm using PI DataLink version 3.1.6, Microsoft Visual Basic version 6.5, and Microsoft Excel 2003 SP3.

      Option Strict Off 
      Imports Excel = Microsoft.Office.Interop.Excel
      Imports Word = Microsoft.Office.Interop.Word
      Imports System.IO

      Public Class Form1

      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
         Dim xlApp As Excel.Application
         xlApp = New Excel.ApplicationClass

         xlApp.Application.Workbooks.Open(Label1.Text)
         xlApp.Visible = True
         xlApp.AddIns("PI-DataLink").Installed = False
         xlApp.AddIns("PI-DataLink").Installed = True
      End Sub 

      Sub read_text()
         Dim line As String
         Using sr As StreamReader = New StreamReader(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\FileLocations.txt") 
            ' Read and display the lines from the file until the end of the file is reached.
            line = sr.ReadLine()
            Label1.Text = line
         End Using
      End Sub 

      Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
         Dim fso
         fso = CreateObject("Scripting.FileSystemObject")
         fso.CopyFile("R:\Duty_Scheduling\Toolbar\FileLocations.txt", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\FileLocations.txt") 
         Call read_text()
      End Sub

      End Class

        • Re: Opening Excel via VBA loses DataLink

          Add to your code:

          
          

          XLApp.RegisterXLL ("C:\Program Files\PIPC\Excel\pipc32.xll")

           

            • Re: Opening Excel via VBA loses DataLink
              Michael

              And verify that is working on all your client machines. I had the issue (but years ago) that the unloading/loading procedure took so long, that the Office Automation believed it isn't working anymore and came up with its annoying message box.

                • Re: Opening Excel via VBA loses DataLink

                  Well that's good to know! It's nice to have the solution to the problem, but do you happen to know what the root cause of this problem is?

                    • Re: Opening Excel via VBA loses DataLink

                      I believe it is by design from Microsoft.  Opening Excel via Automation means that no add-ins are loaded at startup.

                        • Re: Opening Excel via VBA loses DataLink
                          Bannikov

                          Two comments about it

                           

                          1. I believe that use fixed path in code is not right. DataLink path can be retrieved from Registry -- HKLM\SOFTWARE\PISystem\PI-DataLink\CurrentInstallationPath or ...\CurrentInstallation  (in various versions of DataLink this parameter has different names) -- then you can build full path to PIPC32.XLL

                           

                          2. When Excel is opened via Automation it maybe useful to control Macros Security via program like this:

                           

                          private 
                          ExcelApplication m_application;

                          ...

                          m_application.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;

                            • Re: Opening Excel via VBA loses DataLink
                              mziegler

                              On behalf of the DataLink development team ...

                               

                              If you open Excel from some external VBA code, add-ins do not get automatically loaded on startup.  You need to write code to manually “install” the addin.  Here is some sample code I have given out in the past for people to make this work.

                              Private Sub Command1_Click()

                              Dim MSExcel As Excel.Application
                              Dim addXL As Excel.AddIn

                              On Error GoTo ErrorHandler

                              'functionally works.  can't figure out how to get the pi add-in to update

                              Set MSExcel = CreateObject("EXCEL.APPLICATION")

                              MSExcel.Workbooks.Open "C:\VbAppTest.xls", True, False
                              MSExcel.Workbooks("VbAppTest.xls").Activate
                              MSExcel.ActiveWorkbook.RefreshAll
                              MSExcel.Visible = True

                              'add the addin
                              Set addXL = MSExcel.AddIns.Add("e:\program files\pipc\excel\pipc32.xll")
                              'for some reason you have to set is to false, then true to work (in my testing)
                              addXL.Installed = False
                              addXL.Installed = True

                              'just some sample work of mine
                              MSExcel.Range("c1").Value = "sinusoid"
                              MSExcel.ActiveWorkbook.RefreshAll

                              MSExcel.Application.Quit
                              Set MSExcel = Nothing

                              Exit Sub

                              ErrorHandler:
                                  Err.Clear
                                  MSExcel.ActiveWorkbook.Close
                                  MSExcel.Application.Quit
                                  Set MSExcel = Nothing

                              End Sub