1 Reply Latest reply: May 4, 2012 9:55 AM by beyerch2 RSS

    FDM FileSystemObject Warning/Error

    GavinH
      Hello,

      I am trying to create an AftExportToDat Event script that will perform some location based processing and reorder the columns in the dat file for loading into Essbase. The script in its current form is below. When I try running this in workbench I get the following warning: "5 - Invalid procedure call or argument At Line: 35".

      Line 35 is: Set f1 = fso.OpenTextFile(strFile, 1, False, -1)

      I have tried different combinations of the params but nothing seems to work.

      Can anyone tell me where I am going wrong?

      Thanks


      Script:
      Sub AftExportToDat(strLoc, strCat, strPer, strTCat, strTPer, strFile)
      '------------------------------------------------------------------
      'Oracle Hyperion FDM EVENT Script:
      '
      'Created By:      gavin.hogge
      'Date Created:      2012-05-04 12:11:07
      '
      'Purpose:
      '
      '------------------------------------------------------------------
      'Declare local variables
      Dim strline
      Dim fso
      Dim strfName
      Dim strTname
      Dim f1, f2
      Dim strCust
      Dim strSvc
      Dim strLoc
      Dim strCC
      Dim strICEnt
      Dim strNet
      Dim strSrc
      Dim strEnt
      Dim strVer
      Dim strAcct
      Dim strScn
      Dim srtPrd
      Dim dblAmt

      'Declare file system Object
      Set fso = CreateObject("Scripting.FileSystemObject")

      'Open existing dat file for reading
      Set f1 = fso.OpenTextFile(strFile, 1, False, -1)

      'Create a temp file
      strTname = "C:\Temp\" & fso.GetTempName
      Set f2 = fso.CreateTextFile(strTName)

      'Loop through existing .dat file
      Do While f1.AtEndOfStream <> True
           'Store line In a variable
           strline = f1.ReadLine
           'Parse the account from the file
           strAcct = DW.Utilities.fParseString(strline, 13, 1, ",")
           'Parse the entity from the line
           strEnt = DW.Utilities.fParseString(strline, 13, 2, ",")
           'Parse the source from the file
           strSrc = DW.Utilities.fParseString(strline, 13, 3, ",")
           'Parse the version from the file
           strVer = DW.Utilities.fParseString(strline, 13, 4, ",")
           'Parse the cost centre from the file
           strCC = DW.Utilities.fParseString(strline, 13, 5, ",")
           'Parse the network from the file
           strNet = DW.Utilities.fParseString(strline, 13, 6, ",")
           'Parse the location from the file
           strLoc = DW.Utilities.fParseString(strline, 13, 7, ",")
           'Parse the service from the file
           strSvc = DW.Utilities.fParseString(strline, 13, 8, ",")
           'Parse the ic entity from the file
           strICEnt = DW.Utilities.fParseString(strline, 13, 9, ",")
           'Parse the customer from the file
           strCust = DW.Utilities.fParseString(strline, 13, 10, ",")
           'Parse the source from the file
           strScn = DW.Utilities.fParseString(strline, 13, 11, ",")
           'Parse the source from the file
           strPrd = DW.Utilities.fParseString(strline, 13, 12, ",")
           'Parse the amount from the file
           dblAmt = DW.Utilities.fParseString(strline, 13, 13, ",")
           'Write out line To New .dat file
           f2.writeline strCust & "," & strSvc & "," & strLoc & "," & strCC & "," & strICEnt & "," & strNet & "," & strSrc & "," & strEnt & "," & strVer & "," & strAcct & "," & dblAmt
      Loop

      'Close the files
      f1.Close
      f2.Close

      'Now replace original file with temp
      ' ...

      End Sub
        • 1. Re: FDM FileSystemObject Warning/Error
          beyerch2
          I believe the line is "correct" given you are just trying to read the file (assuming you are using UniCode)
          Set f1 = fso.OpenTextFile(strFile, 1, False, -1)
          Because of that, I would suspect that perhaps the value in strFile is suspect for some reason? Maybe you should print out what you are getting and confirm there is a file there, etc? Perhaps for some reason the file is locked or you don't have permissions to it? (doubt that is the case since you'd probably have other issues as well!)

          With that said, I'd be more skeptical on what looks to be line 36-37.
          strTname = "C:\Temp\" & fso.GetTempName
          Set f2 = fso.CreateTextFile(strTName)
          Couple thoughts here worth verifying:

          - I'm not sure if the FDM account has read/write file access to C:\Temp, so you may want to verify that.
          - While GetTempName creates a 'random' name, there isn't a guarantee that it is not already in the folder.
          - Did you really intend on reading one file as Unicode and writing the other file as ASCII?
          - Maybe try the following on line 37?
          Set f2 = fso.CreateTextFile(strTName, True, True)
          (The First true signifies to overwrite existing files, the second true indicates you are writing in Unicode.