Export all list box columns to csv

[terrusie]

Hi all,

Just looking for a way to program a Macro to export all columns of a list box to a csv file.

I tried:

set ExportObject = ActiveDocument.GetSheetObject(ObjID) 

ExportObject.Export FileName, ", " 

but this seems to only export the first column.

[tamilarasu]

Terry - I tried to reproduce the problem but it is working fine for me. You can attach some sample file like I created. I will provide the code here. You can copy and paste into your application.

[terrusie]

Here's the code.  It's not that complex.  It may have something to do with how I'm configuring the listbox, but I don't know what that could be.

Sub CSVTEST

     set ExportObject = ActiveDocument.GetSheetObject("LB501") 

     ExportObject.Export "C:\...\MyFile.csv", ", " 

End Sub 

[terrusie]

It looks as though you defined the list box solely in the "General" tab.  I think I was not clear with how the data are entered into the listbox.

In my listbox, "Field datum #" are the results of an expression entered in "General" >> "Field" 

"Expression x datum #" are the results of expressions entered on the "Expressions"  tab.

Only the data from the "General" tab show up in the CSV file.

[tamilarasu]

Yes. You are right. I have a workaround which will save the file as xls or xlsx and then we can save it as csv file format. But the problem is your folder will have two files one with csv and xls (or xlsx) format.

@ Marcus Sommer Any suggestions? I have also tried to use sendtoexcel syntax and then save it as csv file format. But it looks like activeworkbook syntax is not working here. So I am not sure how to save the opened excel (saved in temp folder) in a specified path. Thank you.

[terrusie]

Tamil,

As far as I can see this is the only way to do it.

The way I ended up doing it was exporting it as an Excel file and then running a .vbs script to convert the excel file to CSV.  Works like a charm.

[tamilarasu · Accepted solution]

Hi Terry,

Great. Here is the macro code to do the same. Also, If you can post the vbs code here, it may help someone someday.

Sub CSVTEST

   'File path & name 
    vFile = "C:\Users\Tamil\Desktop\MyFile"

   'Export to xls format
   set ExportObj = ActiveDocument.GetSheetObject("LB01")
   ExportObj.Exportbiff vFile & ".xls"

   'Convert from xls to csv format
   Set objExcel = CreateObject("Excel.Application")
   objExcel.Visible = False  
   objExcel.Workbooks.Open(vFile & ".xls")
   objExcel.Activeworkbook.Saveas vFile & ".csv", 6
   objExcel.quit

   'Delete xls file
   Set Filesys = CreateObject("Scripting.FileSystemObject") 
   Filesys.DeleteFile vFile & ".xls"

   Set objExcel = Nothing
   Set Filesys = Nothing

   Msgbox "File has been created!"

End Sub