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

   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

Outcomes