2 Replies Latest reply on Sep 30, 2016 12:10 AM by Mark.Marquez

    Link and update an excel to Process book

    Mark.Marquez

      Hello OSS community,

       

      I am currently creating a process book display that has a capability to update linked excel for reporting purposes. I created a script on how to open, write and save to excel,

       

      but the problem is, instead of updating the file, it deletes the old one, and created a new excel file.

       

      Here's my code:

       

       

          Dim oExcel As Object

          Dim oBook As Object

          Dim oSheet As Object

         

          Set oExcel = CreateObject("Excel.Application")

          Set oBook = oExcel.Workbooks.Add

         

          'Add data to cells of the first worksheet in the new workbook

         Set oSheet = oBook.Worksheets(1)

        

         oSheet.Range("B2").Value = txtField1.Value

         oSheet.Range("C2").Value = txtField2.Value

       

       

         'Save the Workbook and Quit Excel

         oBook.SaveAs "H:\File.xlsx"

         Call updateWorkSheet

         oExcel.Quit

       

       

      Private Sub updateWorkSheet()

      Dim obj As OLEObject

          For Each obj In ThisDisplay.OLEObjects

              If TypeName(obj.Object) = "Workbook" Then

                  Call obj.Object.RefreshAll

              End If

          Next

       

       

      End Sub

       

       

      Thanks and Regards,

       

      Mark

        • Re: Link and update an excel to Process book
          jru

          Hi Mark,

           

          I did some tests with your code and I believe that this is expected.  In the code, you are opening a blank workbook with: Set oBook = oExcel.Workbooks.Add.  After the cells have been updated by objects in Processbook, you Save the workbook using the SaveAs Function.  The SaveAs function checks the file path to see if there is an existing spreadsheet and will prompt you to see if you'd like to overwrite the file (AKA delete and create a new file)

           

          I made some modifications to the code.  Please give this a try:

           

              Dim oExcel As Object

              Dim oBook As Object

              Dim oSheet As Object

             

              Set oExcel = CreateObject("Excel.Application")

              Set oBook = oExcel.Workbooks.Open("H:\File.xlsx")

             

              'Add data to cells of the first worksheet in the new workbook

             Set oSheet = oBook.Worksheets(1)

            

             oSheet.Range("B2").Value = txtField1.Value

             oSheet.Range("C2").Value = txtField2.Value

           

           

             'Save the Workbook and Quit Excel

             oBook.Save

             Call updateWorkSheet

             oExcel.Quit

           

          This should open the existing file and save it.

           

          Please let me know if this works.

           

          ~Jesse

          2 of 2 people found this helpful