4 Replies Latest reply: Dec 16, 2013 10:56 AM by Vamshi Tataipelli RSS

    Macro to export as a flat file! Also need a tab delimiter

    Vamshi Tataipelli

      Hi, I have written a macro to export a sheet object to a flat file (.csv). I then gave a "OnPostReload" trigger.

      So when the qvw reloads, the macro executes and a .csv file will be exported. Here is the macro


      sub Export

      Dim docProp

      set docProp = ActiveDocument.GetProperties


      Dim tmpFile  'used to create relative filepaths

      tmpFile = docProp.MyWorkingDirectory


      Dim vSpl  'location of final backslash char


      vSpl = InStrRev(tmpFile,"\")


      tmpFile = Left(tmpFile,vSpl-1)


      vSpl = InStrRev(tmpFile,"\")


      tmpFile = Left(tmpFile,vSpl) & "External Data Sources\OperationalReportsOutput\Activity_" & DatePart("YYYY", Now()) & right( "0" & DatePart("m", Now()) ,2) & right( "0" & DatePart("d", Now()) ,2) & ".csv"    


      set obj = ActiveDocument.GetSheetObject("CH01")

      obj.ExportBiff tmpFile


      end sub



      As, i need the date at the end of the name of the export flat file, i used the date function to get the date in the format i need.


      Now, they need a .txt file with space delimiter. I have tried that by just changing ".csv" to ".txt". Then, the output file is forming, but when i open the output file with a wordpad, the data is unreadable. Whereas when i open the same file with excel, it is showing the data.


      My question is how to define the space delimiter? I have tried it by giving ".txt", (txt, delimiter is " ")


      But it is showing the error! Hlep me...