topic Re: Need Macro to export multiple text-boxes into single excel sheet in QlikView

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

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

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

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

beck_bakytbek — Tue, 28 Mar 2017 11:45:54 GMT

Hi Abhinav,

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

i hope that helps

beck

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

Anonymous — Tue, 28 Mar 2017 11:53:27 GMT

Hey Beck,

The Link you have provided is not working.

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

beck_bakytbek — Tue, 28 Mar 2017 13:20:11 GMT

check this : Category Archives: Macros

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

tamilarasu — Wed, 29 Mar 2017 07:50:45 GMT

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.

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

Anonymous — Fri, 31 Mar 2017 13:29:08 GMT

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

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

Anonymous — Fri, 31 Mar 2017 13:47:51 GMT

@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

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

tamilarasu — Fri, 31 Mar 2017 15:09:33 GMT

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.

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

Anonymous — Sat, 01 Apr 2017 12:38:30 GMT

Hi Nagaraj,

I am eagerly waiting for your reply...

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

tamilarasu — Sun, 02 Apr 2017 08:18:23 GMT

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

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

Anonymous — Tue, 04 Apr 2017 16:07:42 GMT

Hello Nagaraj,

You have written the code so nicely which fulfills all of my requirements. Many-many thanks for your kind help.

You made my day best !

GOD Bless You !!

Thanks,
Abhinav

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

tamilarasu — Wed, 05 Apr 2017 07:47:38 GMT

It's all my pleasure. No need to say thanks and all. Your happiness is enough my friend!!

Thanks for your kind words!! Have a good day!!