Options
  • Locked Locked
  • Replies 13 replies
  • Subscribers 9 subscribers
  • Views 671 views
  • Users 0 members are here
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

(Workflow Script) Edit Excel File within the Object

Former Member
Former Member
Sorry to bother you again, but I need another help from you..

I want to set a Workflow Action, where it should open a File within the Object where Workflow is set. Then it should write something into the Excel Sheet.

I have put some Code together but it doesn't work.. Before I execute the script, I manually check out the object (I didn't manage to check it out via VB).. When I want to check it in again it doesn't do it, I have to undo the Checkout.. Also I don't think the changes were actually made while checked out..


Dim objFiles : Set objFiles = Vault.ObjectFileOperations.GetFiles(objVer)

Set XLS = CreateObject("Excel.Application")
Set WB = XLS.Workbooks.Open(objFiles(1))
Set WS = WB.Sheets(1)
WS.Range("A1").Formula="TEST"

XLS.DisplayAlerts = False
WB.Save
XLS.DisplayAlerts = True
XLS.Quit
  • Former Member
    Former Member
    Now I tried to edit a related Document with Excel but I still don't get it :( Maybe you can look at it again, I think it is only a small Problem..


    Dim related : Set related = Vault.ObjectOperations.GetRelationships(objVer,MFRelationshipsModeAll)
    Dim obj : Set obj = CreateObject("MFilesAPI.ObjVer")

    For Each obj in related.GetAsObjVers
          If obj.type = 0 Then
    					 Set XLS = CreateObject("Excel.Application")
    					 Set WB = XLS.Workbooks.Open(obj)
    					 Set WS = WB.Sheets(1)
    				 	 WS.Range("A1").Formula="TEST"
          End if
    Next


    It won't even open the Excel File..
  • Former Member
    Former Member
    you're trying to open an ObjectVersion in Excel not a file
  • Not sure that you can open and edit like that. If you could, you should at least get an error in the cell.
    Once you figure out how to open and edit the spreadsheet, I believe the the formula line should be

    WS.Range("A1").Formula="="TEST""

    See msdn.microsoft.com/.../range-formula-property-excel
  • Former Member
    Former Member
    @James Carter

    I know, but I don't know how to get the file!

    @bright-ideas.dk

    Okay, I thought this would be some easy task.. ::)

    Maybe it helps if I describe what I want to achieve.. I have an Object which represents a Recurring Task (like in Compliance Kit) and I want to have a Journal with an entry on every Status Change to "done".

    I know I can look into the History of the Object, but I want to have it in a printable protocol..
  • Former Member
    Former Member
    I think that you'll need to download the file to a temporary location and open it from there, then if you make changes upload it back
  • Former Member
    Former Member

    I think that you'll need to download the file to a temporary location and open it from there, then if you make changes upload it back


    I tried that, but then I get another Error:


    Microsoft Excel cannot access the file "...". There are several possible reasons:

    -The file name or path does not exist.
    -The file is being used by another program.
    -The workbook you are trying to save has the same name as a currently open workbook.


    Here's the Code:


    For Each obj in related.GetAsObjVers
          If obj.type = 0 Then
    				 	 Dim objFiles : Set objFiles = Vault.ObjectFileOperations.GetFiles(obj)
    					 Dim sFilePath : sFilePath = "D:\Sortieren\" & objFiles(1).GetNameForFileSystem()
    					 Call Vault.ObjectFileOperations.DownloadFile(objFiles(1).ID, objFiles(1).Version, sFilePath)
    					 Set XLS = CreateObject("Excel.Application")
    					 Set WB = XLS.Workbooks.Open(sFilePath)
    					 Set WS = WB.Sheets(1)
    				 	 WS.Range("A1").Formula="TEST"
    					 XLS.DisplayAlerts = False
    					 WB.Save
    					 XLS.DisplayAlerts = True
    					 XLS.Quit
          End if
    Next


    Path & Filename are correct!

  • XLS.DisplayAlerts = False
    wb.Close savechanges:=True
    XLS.DisplayAlerts = True
    XLS.Quit
    Set wb = Nothing
  • Former Member
    Former Member
    Thank you, but I cannot even open the Workbook in Excel!
  • Former Member
    Former Member
    Sorry I have a problem seems yours but I have another error :

    El componente ActiveX no puede crear el objeto: 'Excel.Application'
    ID de referencia de error: 441d8592-f394-4045-9fa3-5056686bea7a

    Maybe I have to link some library or something like that, do you do something else?
    Thanks
  • Sorry,

    I think you need to tell Excel what to do. Something like:

    WS.Select.Range("A1")
    Or
    WS.Range("A1").Select
M-Files
M-Files Support


© 2025 M-Files, All Rights Reserved.