topic Re: How toexport a single Chart from QLV to Excel in QlikView

How toexport a single Chart from QLV to Excel

Mon, 17 Mar 2014 11:21:05 GMT

Good morning everyone!

I'm a new user of VB and it's the first time I write a macro in QLV. I have (as you will see the attachment) a chart, that I would like to export in an excel file. When used, the macro must to export the chart and to update the already existing file rather than delete it and create a new one. That's what I have done so far:

sub Excel

nomefile = "C:\E_APPOGGIO\PROVAPROVA.xlsx"

set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FileExists(nomefile) = True Then

FSO.DeleteFile nomefile

End If

Set FSO = Nothing

set XLApp = CreateObject("Excel.Application") 'since a few days, though, I could not run this macro anymore as it gives me an error on this line when I debug

XLapp.Visible = true

set XLDOC = XLApp.Workbooks.Add

XLDoc.Sheets(1).name = "Export"

set XLSheet = XLDoc.Worksheets(1)

set myTable = ActiveDocument.GetSheetObject("CH01")

set XLSheet = XLDoc.Worksheets(1)

Mytable.CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Cells.Entirerow.AutoFit

XLSheet.saveas nomefile

set XLSheet = nothing

XLDOC.close

set XLDOC = nothing

XLApp.quit

Set XLApp = nothing

end sub

I hope I have been enough clear and that someone could help me!

Many thanks!!

Re: How toexport a single Chart from QLV to Excel

manojkulkarni — Mon, 17 Mar 2014 11:25:24 GMT

check this link, might solve your issue.

Qlikview to excel export macro

Re: How toexport a single Chart from QLV to Excel

manojkulkarni — Mon, 17 Mar 2014 11:27:18 GMT

one more to look at it... http://luciancotea.wordpress.com/2013/07/18/qlikview-macros-useful-collection/

Re: How toexport a single Chart from QLV to Excel

Mon, 17 Mar 2014 11:31:01 GMT

I have already seen this post and I have tried to use it for my case, but I dont' think it's what I need. 1. I don't have any other data on the excel sheet where I want to export 2. I need also the headers of the chart I need to export

Re: How toexport a single Chart from QLV to Excel

Mon, 17 Mar 2014 15:23:11 GMT

Sorry to bother you Manoj K but you are proposing me data that I have already found in the web! It's 2 days I'm looking for a piece of code that could suit me. The only solution I always find to my problem is the application.displayalerts=false and True.

They don't solve my problem! as the message ok, doesn't appear, but a new excel file is created! I need to overwrite my PROVAPROVA file!

Re: How toexport a single Chart from QLV to Excel

Greg_Hood — Mon, 17 Mar 2014 15:38:22 GMT

You can try the following syntax, which uses the built in API to export the file in BIFF format. It is the older xls format, but might help you. It doesn't rename the standard "sheet1" name though, so if that is important this won't help you. Make sure that your macro security settings allow the required permissions to access system resources.

sub SendToBiff

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportBiff "C:\test.xls"

end sub