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

[Anonymous]

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

[jerrysvensson]

Use:

obj.Export tmpFile instead of

obj.ExportBiff tmpFile

ExportBiff export to Excel format.

Download the APIguide.qvw from the forum.

[Anonymous]

Hi Jerry Svensson

Thanks for your reply.

It is not working if i use Export tmpFile

[jerrysvensson · Accepted solution]

From APIguide

set tb = ActiveDocument.Sheets("Main").CreateTableBox

tb.AddField "Class"

tb.AddField "Member"

tb.Export "C:\test.skv",";"

Try:

tb.Export "C:\test.txt"," "

don't know if " " will work though.

[Anonymous]

Thank You very much,

Yes it is working (aslo "C:\test.txt"," "). This is the final script which i used and it is giving the out put as required.

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\"

set obj = ActiveDocument.GetSheetObject("CH01")

obj.Export tmpFile & "Activity_" & DatePart("YYYY", Now()) & right( "0" & DatePart("m", Now()) ,2) & right( "0" & DatePart("d", Now()) ,2) & ".txt"," "

end sub