AnsweredAssumed Answered

Link and update an excel to Process book

Question asked by Mark.Marquez on Sep 29, 2016
Latest reply on Sep 30, 2016 by 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




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




End Sub



Thanks and Regards,