Need Macro to export multiple text-boxes into single excel sheet

[Anonymous]

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

[beck_bakytbek]

Hi Abhinav,

check this blog / issue: http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

i hope that helps

beck

[Anonymous]

Hey Beck,

The Link you have provided is not working.

[beck_bakytbek]

check this : Category Archives: Macros

[tamilarasu]

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.

[Anonymous]

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!!

[Anonymous]

@Tamil Nagaraj

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

[tamilarasu]

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.

[Anonymous]

Hi Nagaraj,

I am eagerly waiting for your reply...

[tamilarasu · Accepted solution]

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