topic Re: Export report in QlikView

Export report

arethaking — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I want to export this report to C:\Users\arethaking\Qlikview folder.

How to write the macro for this?

Thanks in advance.

Re: Export report

Anil_Babu_Samineni — Wed, 12 Oct 2016 07:38:15 GMT

I didn't open your file, As i assume you might require this, Here i added one and more Sheet objects. If you want one then you can take one. Hope this helps

Ctrl + M and then copy paste this on your VBA and then create Button - Action is Macro From External tab - Give Name ppt then run

Sub ppt

'An array with the objects you want to export to powerpoint

Dim myArray(4)

myArray(0) = "YourObjectId"

myArray(1) = "YourObjectId"

myArray(2) = "YourObjectId"

myArray(3) = "YourObjectId"

myArray(4) = "YourObjectId"

'Create ppt object

Set objPPT = CreateObject("PowerPoint.Application")

objPPT.Visible = True

Set objPresentation = objPPT.Presentations.Add

'Apply a template if you want one

objPresentation.ApplyTemplate _

("C:\Users\arethaking\Qlikview\mytemplate.potx")

'For each object in the array create a new slide and paste it.

For each item in myArray

Set PPSlide = objPresentation.Slides.Add(1,11)

ActiveDocument.GetSheetObject(item).CopyBitmapToClipboard

PPSlide.Shapes.Paste

'Clean up

Set PPSlide = Nothing

Set PPPres = Nothing

Set PPApp = Nothing

End Sub

Re: Export report

arethaking — Wed, 12 Oct 2016 07:44:18 GMT

Sorry. I missed something, want to export to xlsx.

Re: Export report

Anil_Babu_Samineni — Wed, 12 Oct 2016 08:36:43 GMT

Try this code to send the reports to excel using Macro

sub test

filePath = "C:\Users\arethaking\Qlikview\Test.xls"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

'first chart object

Set curSheet = curWorkBook.WorkSheets(1)

'loop

chartArray = Array("CH06") ' give the name of your object instead of CH06

usedRows=0

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

curSheet.Cells(usedRows+1, 1)=chartCaption

i.CopyTableToClipboard true

curSheet.Cells(usedRows+3, 1).Select

curSheet.Paste

usedRows=curSheet.UsedRange.Rows.Count+3

'loop end

excelFile.Visible = true

'Save the file and quit excel

curWorkBook.SaveAs filePath

curWorkBook.Close

excelFile.Quit

'Cleanup

Set curWorkBook = nothing

Set excelFile = nothing

end sub

Re: Export report

arethaking — Wed, 12 Oct 2016 09:21:50 GMT

I am getting the below error message.

Re: Export report

Anil_Babu_Samineni — Wed, 12 Oct 2016 09:26:05 GMT

Aretha, Why not simple this. Try and let me know

SUB EXPORT

set sObject1 = ActiveDocument.GetSheetProperties("CH_EXPORT")

sObject1.Export "C:\Users\arethaking\Qlikview\MyDoc.csv", ", "

end sub

Re: Export report

Anil_Babu_Samineni — Wed, 12 Oct 2016 09:27:15 GMT

Sorry, My Miss understnd

SUB EXPORT

set sObject1 = ActiveDocument.GetSheetObject("CH01")

sObject1.Export "C:\Users\arethaking\Qlikview\MyDoc.csv", ", "

end sub

Re: Export report

tamilarasu — Wed, 12 Oct 2016 10:27:13 GMT

Hi Aretha,

Have a look at the attachment and let me know.

Sub Test()
 
 Dim XLApp, XLDoc
 
 FileName = "Test.xlsx" 
 FilePath = ActiveDocument.GetVariable("vPath").GetContent.String
 
 'FilePath = C:\Users\arethaking\Qlikview\
 
 '***************************************
 If Right(FilePath,1)<> "\" then
 FilePath = FilePath & "\"
 End If
 
 File = FilePath & FileName
 '***************************************
 
 Set XLApp = CreateObject("Excel.Application")  
 XLApp.Visible = False 'True to display the excel
 Set XLDoc = XLApp.Workbooks.Add
 
 ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard True
 XLDoc.Worksheets("Sheet1").Paste
 XLDoc.Worksheets("Sheet1").Range("A1:F60000").ColumnWidth = 13.5
 
 XLDoc.SaveAs File
 XLApp.Application.quit  
 
 Set XLApp = Nothing  
 Set XLDoc = Nothing  
 
 Msgbox "Chart Exported" & VbNewline & vbNewline & "File path: " & File, vbInformation , "Export"
 
 End Sub

Re: Export report

beck_bakytbek — Wed, 12 Oct 2016 10:35:38 GMT

Hi Aretha,

check this : QlikTip #32: Exporting multiple QV objects to a single Excel document

i find a necessary Information with whose assistance you resolve your issue

beck