Export image to excel using macro

[prabir_c]

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

[beck_bakytbek]

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

[avinashelite]

check this thread , this might help you

Export Straight Table to Excel - Images issue

[prabir_c]

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.

[beck_bakytbek]

Hi Prabir,

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

beck

[#6]

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.

[prabir_c]

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

[Anonymous]

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