Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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.
Use:
obj.Export tmpFile instead of
obj.ExportBiff tmpFile
ExportBiff export to Excel format.
Download the APIguide.qvw from the forum.
Hi Jerry Svensson
Thanks for your reply.
It is not working if i use Export tmpFile
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.
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