topic Re: Re: Export a straight or pivot table to CSV through a button object in QlikView

Export a straight or pivot table to CSV through a button object

brijesh1991 — Fri, 06 Jun 2014 05:42:30 GMT

Hi,

If user wants to export a straight table or pivot table into CSV format, then user have to right click onto the object and then select the file type-CSV and give file name, then user can export table into CSV format.

But on front end, I want to create a button. User will click on it which will enable user to export the table (straight table id: Ch03) into CSV format by just clicking on it.

All ur ideas are warmly welcomed. . .

Regards,

BRIJESH

Re: Export a straight or pivot table to CSV through a button object

ankit777 — Fri, 06 Jun 2014 07:44:54 GMT

Hi,

You can write a macro for that and run the macro on the button click event.

Re: Export a straight or pivot table to CSV through a button object

Fri, 06 Jun 2014 07:52:11 GMT

Search community. I saw ready script in some thread...

Re: Export a straight or pivot table to CSV through a button object

ankit777 — Fri, 06 Jun 2014 08:36:11 GMT

Hi,

The following code creates the Excel sheet in correct format.

sub Export

set obj1 = ActiveDocument.GetSheetObject("TB01")

obj1.ExportBiff "C:\Documents and Settings\596732\Desktop\manasvi\InnerJoin.xls"

msgbox("Done")

end sub

Create a buton and run the macro on click.

Re: Export a straight or pivot table to CSV through a button object

brijesh1991 — Fri, 06 Jun 2014 09:01:25 GMT

Sir,

You are right that I can export table onto some specific path on server.

C:\Documents and Settings\596732\Desktop\manasvi\InnerJoin.xls

I do not want to export onto my server. I had NT authentication; each user should be able to export into CSV format on their local machine by clicking onto it.

Thanks for ideas. . . keep idea'ING', please!!!

Regards,

BRIJESH

Re: Export a straight or pivot table to CSV through a button object

ankit777 — Fri, 06 Jun 2014 09:52:05 GMT

Hi,

Try this

sub Export_To_Excel_View1

Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true

Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")

Set Doc = ActiveDocument

Doc.GetSheetObject("CH89").CopyTableToClipBoard true
xlApp.ActiveSheet.Range("A1").Select
xlApp.ActiveSheet.Paste

end sub

It wil create a excel as soon as run on button click event and the user can save it at desired location.

Re: Export a straight or pivot table to CSV through a button object

brijesh1991 — Mon, 09 Jun 2014 04:27:22 GMT

Hi Mishra,

thanks. . .

Getting error

ActiveX component can't create object: 'Excel.Application'

at this line:

Set xlApp = CreateObject("Excel.Application")

And each user should be able to export into CSV format on their local machine by clicking onto it. Export to Excel icon is already there in Qlikview on right hand corner of each object.

Re: Re: Export a straight or pivot table to CSV through a button object

ashfaq_haseeb — Mon, 09 Jun 2014 07:02:13 GMT

Hi Brijest,

Please find the attached Application.

Code:

sub Export  
    set XLApp = CreateObject("Excel.Application") ' Define Object  
        XLApp.Visible = True 'Visible set as true  
        set XLDoc = XLApp.Workbooks.Add 'Open new workbook      
        set table = ActiveDocument.GetSheetObject("CH01")  
        rem set table = ActiveDocument.GetSheetObject("MB01")  
          
    set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted  
        table.CopyTableToClipboard true 'Copy data to Clipboard  
        XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell  
      
end sub

Regards

ASHFAQ

Re: Export a straight or pivot table to CSV through a button object

Tue, 04 Nov 2014 09:16:23 GMT

Hi Mohammed.

Could you please also the code to save automatically the Excel file in a specified path?

This will help in order to get a whole scenario.

Thanks a lot

Alberto

Re: Re: Export a straight or pivot table to CSV through a button object

ashfaq_haseeb — Tue, 04 Nov 2014 09:29:19 GMT

Hi,

Check this

Sub Test
XLSFile = "C:\Temp\test.xls"
TXTFile= "C:\Temp\test.txt"
Set objExcelApp = CreateObject("Excel.Application")
Set objExcelDoc = objExcelApp.Workbooks.Add
set xlSheet = objExcelDoc.Sheets("Sheet1")
ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true
xlSheet.Range("A1").Select
xlSheet.Paste
objExcelApp.DisplayAlerts = false
objExcelDoc.SaveAs XLSFile         
objExcelDoc.SaveAs TXTFile, -4158 
objExcelDoc.Close
objExcelApp.DisplayAlerts = true
End Sub

Regards

ASHFAQ

Re: Export a straight or pivot table to CSV through a button object

Tue, 04 Nov 2014 10:49:22 GMT

Hi my friend.

Thanks a lot for the update. It is OK.

Regards

Alberto

Re: Export a straight or pivot table to CSV through a button object

ashfaq_haseeb — Tue, 04 Nov 2014 10:53:38 GMT

Welcome

Regards

ASHFAQ

Re: Export a straight or pivot table to CSV through a button object

Tue, 11 Nov 2014 21:06:19 GMT

Hi Mohammed.

Once again me.

the Macro runs correctly on many LapTops. But on someone when the Macro stars this erroror appears:

============

Macro parse failed. Functionality wa lost

Maros Name

Error: ActiveX component can't create object: 'Excel.Application'

============

Do you have any suggestions on what to do to fix the issue?

Thanks a usual & regards

Alberto