topic Re: Sorting excel export through Macro in QlikView

Sorting excel export through Macro

reshmakala — Wed, 17 Feb 2016 08:26:48 GMT

Hi all,

I have a macro that exports two straight tables and concatenates them in the excel. I need to sort this data based on a column. Can someone help me with the syntax (as in QlikView macro) ?

Following is the script example that I have tried, but it doesn't work.

Sheet.Range("A:Z").Sort Key1 = Sheet.Range("E2"), Order1 =2

I have data in column A through Z, I need to sort the data in descending order with values in column E.

Re: Sorting excel export through Macro

tamilarasu — Wed, 17 Feb 2016 08:35:22 GMT

Hi,

Try,

Sheet.Range("A:Z").Sort Key1 := Sheet.Range("E2"), Order1 := xlDescending

Re: Sorting excel export through Macro

jagan — Wed, 17 Feb 2016 08:46:44 GMT

Hi,

Check this link for sample piece of code

Excel - Sort Multiple Sheets - Help - I have a workbook... - Free Excel Help

Instead of = give :=

Sheet.Range("A:Z").Sort Key1 := Sheet.Range("E2"), Order1 :=2

Re: Sorting excel export through Macro

reshmakala — Wed, 17 Feb 2016 15:00:31 GMT

Hi Nagraj,

:= gives me a "Macro Parse Failed" error in QlikView. It works in Excel thought. I also tried xlDescending but that doesn't work either.

Re: Sorting excel export through Macro

reshmakala — Wed, 17 Feb 2016 15:02:46 GMT

Hi Jagan,

I tried := it works in excel, but doesn't work in QlikView, give me a "Macro Parse Failed" error.

Re: Sorting excel export through Macro

marcus_sommer — Wed, 17 Feb 2016 15:19:45 GMT

It's a recent example to append data to already existing data in excel a with vbs-macro which might be itself useful in your case - but more important is to look on the right syntax. This meant you need to add always the created objects to the statements (in opposite to vba in excel) and you couldn't use excel-constants (I think it would be possible to activbate the appropriate libraries but this makes the things more complex) else you need to use the numeric equivalents.

sub AppendDataToExcelFile

dim doc, ex, xlApp, xlDoc, xlSheet, LastRow

Const xlUp = -4162

set doc = ActiveDocument

Set xlApp = CreateObject("Excel.Application")

Set xlDoc = xlapp.Workbooks.Open("D:\YourPath\YourFile.xls")

xlapp.Visible = true

Set xlSheet = xlDoc.Worksheets("YourSheet")

xlSheet.Activate

LastRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row

msgbox LastRow

xlSheet.Cells(LastRow + 1, 1).Select

'doc.fields("Jahr").clear 'optional to set or clear selections

'doc.GetApplication.WaitForIdle

doc.GetSheetObject("CH10021").CopyTableToClipboard true

xlSheet.Paste

xlDoc.Save

xlDoc.Close

xlApp.Quit

end sub

- Marcus