Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
Need one help !!
I want to use a Macro which should select every Time-frame from multi-box automatically and export all text-boxes
from qlikview into single excel sheet one after another according to the time-frame selected by macro.
Please refer the attached sample Qlikview file with output in excel sheet and guide me how to achieve this task.
Thanks,
Abhinav
Hello Abhinav,
Sorry for the delay in response. I had some work yesterday, so couldn't open my laptop. I have attached my solution with the expected output (Exactly same as attached in excel ). Happy weekend!!
Sub ExportExcel
'Excel Creation
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True 'Visible set as true
set XLDoc = XLApp.Workbooks.Add
set XLSheet = XLDoc.ActiveSheet
'================================================================
ActiveDocument.Fields("TIME").Clear
set val=ActiveDocument.Fields("TIME").GetPossibleValues
for i=0 to val.Count -1
ValueToSelect = val.Item(i).Text
ActiveDocument.Fields("TIME").Select ValueToSelect
'LastRow = XLSheet.UsedRange.Rows.Count
LastRow = XLSheet.Cells(XLSheet.Rows.Count, "B").End(-4162).Row
With XLSheet
.Range("B" & LastRow+2).Select
.Range("B" & LastRow+2).Value = ValueToSelect
.Range("B" & LastRow+2 & ":D" & LastRow+2).MergeCells=True
.Range("B" & LastRow+3).VALUE = ActiveDocument.GetSheetObject("TX14").GetText()
.Range("C" & LastRow+3).VALUE = ActiveDocument.GetSheetObject("TX12").GetText()
.Range("D" & LastRow+3).VALUE = ActiveDocument.GetSheetObject("TX13").GetText()
.Range("B" & LastRow+4).VALUE = ActiveDocument.GetSheetObject("TX08").GetText()
.Range("C" & LastRow+4).VALUE = ActiveDocument.GetSheetObject("TX01").GetText()
.Range("D" & LastRow+4).VALUE = ActiveDocument.GetSheetObject("TX02").GetText()
.Range("B" & LastRow+5).VALUE = ActiveDocument.GetSheetObject("TX09").GetText()
.Range("C" & LastRow+5).VALUE = ActiveDocument.GetSheetObject("TX03").GetText()
.Range("D" & LastRow+5).VALUE = ActiveDocument.GetSheetObject("TX04").GetText()
.Range("B" & LastRow+6).VALUE = ActiveDocument.GetSheetObject("TX10").GetText()
.Range("C" & LastRow+6).VALUE = ActiveDocument.GetSheetObject("TX05").GetText()
.Range("D" & LastRow+6).VALUE = ActiveDocument.GetSheetObject("TX06").GetText()
End With
'==================================================================
'Formatting cells
With XLSheet.Range("B" & LastRow+2 & ":D" & LastRow+6).Borders
.LineStyle = 1
.Weight = 3
End With
With XLSheet.Range("B" & LastRow+2 & ":D" & LastRow+6).Interior
.ThemeColor = 10
.TintAndShade = 0.399975585192419
End With
With XLSheet.Range("C" & LastRow+4 & ":D" & LastRow+6).Interior
.ThemeColor = 10
.TintAndShade = 0.599993896298105
End With
'==================================================================
next
With XLSheet
.Columns("A").ColumnWidth = 1.29
.Columns("B:D").AutoFit
.Rows("1:2").EntireRow.Delete
.Name = "Summary"
.Range("A1").Select
End With
Msgbox "Export done!!"
End sub
Output
Hi Abhinav,
check this blog / issue: http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/
i hope that helps
beck
Hey Beck,
The Link you have provided is not working.
check this : Category Archives: Macros
Hi Abhinav,
You have to write your own macro in this case.
1) Loop through listbox values.
Qlikview Macro Loop through Items in List Box - BuffaloBI.com
2) Copy objects into single excel. You need to tweak the below code little bit to fit your need.
Macro to export multiple tables in to single excel
If you need any help, let me know.
Hi Nagaraj,
I have come up with a macro which is generating separate excel sheets for each "Timeframe" but I want to generate a single excel sheet as Dummy_Excel.xlsx with cell colors as per Qlikview app i.e. Dummy.qvw
Can you help me here!!
Another thing I forgot to add in my above post is the columns width should be auto-fit as per data in excel....
It will be helpful for me if anyone make changes in the Macro which fulfills my requirements and attach it...
Thanks,
Abhinav
Hi Abhinav,
Great! I can see you have tried something . I will provide you the full code after I reach my home. Check this thread tomorrow.
Hi Nagaraj,
I am eagerly waiting for your reply...
Hello Abhinav,
Sorry for the delay in response. I had some work yesterday, so couldn't open my laptop. I have attached my solution with the expected output (Exactly same as attached in excel ). Happy weekend!!
Sub ExportExcel
'Excel Creation
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True 'Visible set as true
set XLDoc = XLApp.Workbooks.Add
set XLSheet = XLDoc.ActiveSheet
'================================================================
ActiveDocument.Fields("TIME").Clear
set val=ActiveDocument.Fields("TIME").GetPossibleValues
for i=0 to val.Count -1
ValueToSelect = val.Item(i).Text
ActiveDocument.Fields("TIME").Select ValueToSelect
'LastRow = XLSheet.UsedRange.Rows.Count
LastRow = XLSheet.Cells(XLSheet.Rows.Count, "B").End(-4162).Row
With XLSheet
.Range("B" & LastRow+2).Select
.Range("B" & LastRow+2).Value = ValueToSelect
.Range("B" & LastRow+2 & ":D" & LastRow+2).MergeCells=True
.Range("B" & LastRow+3).VALUE = ActiveDocument.GetSheetObject("TX14").GetText()
.Range("C" & LastRow+3).VALUE = ActiveDocument.GetSheetObject("TX12").GetText()
.Range("D" & LastRow+3).VALUE = ActiveDocument.GetSheetObject("TX13").GetText()
.Range("B" & LastRow+4).VALUE = ActiveDocument.GetSheetObject("TX08").GetText()
.Range("C" & LastRow+4).VALUE = ActiveDocument.GetSheetObject("TX01").GetText()
.Range("D" & LastRow+4).VALUE = ActiveDocument.GetSheetObject("TX02").GetText()
.Range("B" & LastRow+5).VALUE = ActiveDocument.GetSheetObject("TX09").GetText()
.Range("C" & LastRow+5).VALUE = ActiveDocument.GetSheetObject("TX03").GetText()
.Range("D" & LastRow+5).VALUE = ActiveDocument.GetSheetObject("TX04").GetText()
.Range("B" & LastRow+6).VALUE = ActiveDocument.GetSheetObject("TX10").GetText()
.Range("C" & LastRow+6).VALUE = ActiveDocument.GetSheetObject("TX05").GetText()
.Range("D" & LastRow+6).VALUE = ActiveDocument.GetSheetObject("TX06").GetText()
End With
'==================================================================
'Formatting cells
With XLSheet.Range("B" & LastRow+2 & ":D" & LastRow+6).Borders
.LineStyle = 1
.Weight = 3
End With
With XLSheet.Range("B" & LastRow+2 & ":D" & LastRow+6).Interior
.ThemeColor = 10
.TintAndShade = 0.399975585192419
End With
With XLSheet.Range("C" & LastRow+4 & ":D" & LastRow+6).Interior
.ThemeColor = 10
.TintAndShade = 0.599993896298105
End With
'==================================================================
next
With XLSheet
.Columns("A").ColumnWidth = 1.29
.Columns("B:D").AutoFit
.Rows("1:2").EntireRow.Delete
.Name = "Summary"
.Range("A1").Select
End With
Msgbox "Export done!!"
End sub
Output