topic Re: Export image to excel using macro in QlikView

Export image to excel using macro

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

Hello All,

Can we export image wise report in excel using macro?

I have a straight table containing each item image,Item code and sale amount, when using macro send this table into excel, I found the following scenario.

This means in Location field that image location is printed. Not original image. Please help.

Code for send to excel:

sub ExcelFile

strDate = CDate(Date)

strDay = DatePart("d", strDate)

strMonth = DatePart("m", strDate)

strYear = DatePart("yyyy", strDate)

If strDay < 10 Then

strDay = "0" & strDay

End If

If strMonth < 10 Then

strMonth = "0" & strMonth

End If

GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

Path = "G:\@_Qlik_Test\Work\Output\"

FileName = "Sales_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Columns("A:D").EntireColumn.AutoFit

XLDoc.Sheets(1).Columns("A:A").ColumnWidth = 15.57

XLDoc.Sheets(1).Columns("B:B").ColumnWidth = 12.43

XLDoc.Sheets(1).Columns("C:C").ColumnWidth = 15.29

XLDoc.Sheets(1).Columns("D:D").ColumnWidth = 15.57

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

XLDoc.SaveAs Path & FileName

End Sub

Re: Export image to excel using macro

beck_bakytbek — Thu, 23 Jun 2016 09:30:37 GMT

Hi Prabir,

try this :

public function ExportImage

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.GetSheetByID("yourTableName").Activate

vFolder = ActiveDocument.GetVariable("vExportFolder").GetContent().String

set obj = ActiveDocument.GetSheetByID("yourTableName")

ActiveDocument.GetApplication.WaitForIdle

fileName = "ExportScreen_" & replace(replace(replace(date() & "_" & time(), "/", ""), ".", ""), ":", "") & ".png"

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile vFolder & fileName

set obj = ActiveDocument.GetSheetObject("Imageexport")

ActiveDocument.GetApplication.WaitForIdle

fileName = "ExportScreen_" & replace(replace(replace(date() & "_" & time(), "/", ""), ".", ""), ":", "") & ".png"

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile vFolder & fileName
end function

i hope that helps you

beck

Re: Export image to excel using macro

avinashelite — Thu, 23 Jun 2016 09:47:08 GMT

check this thread , this might help you

Export Straight Table to Excel - Images issue

Re: Export image to excel using macro

prabir_c — Thu, 23 Jun 2016 09:55:28 GMT

hello beck,

If the Image is stored in Database as 'BLOB' type, it is possible to export image in excel.

But here, I have to fetch the Itemcode from database,and in an Folder Images are stored as Itemcode.jpg.

in Image folder all images are maintained externally such as KAY456419.jpg, KAY479579.jpg.

I am just copy the data of that table and paste in excel

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

I am trying just copy the image also and paste it in excel.

Re: Export image to excel using macro

beck_bakytbek — Tue, 05 Jul 2016 09:21:16 GMT

Hi Prabir,

sorry for my late responce, yes, it is possible, if you did not solve you proble, let me please know

beck

Re: Export image to excel using macro

Tue, 05 Jul 2016 09:27:16 GMT

The macro allow us to sent the object image to excel, not images in data table format. It does not allow us to do some sorting or filtering in excels. I think the future Qlik should develop some imaging features.

Re: Export image to excel using macro

prabir_c — Thu, 04 Aug 2016 10:11:21 GMT

Is there any way to copy each image and data also? I have tried many times, but failed.

Re: Export image to excel using macro

Anonymous — Wed, 06 Sep 2017 05:52:14 GMT

THIS I HAVE TO WRITE IN QIKVIEW AFER PRESSING CNTRL + M