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...