Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
reshmakala
Creator III
Creator III

Sorting excel export through Macro

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.

5 Replies
tamilarasu
Champion
Champion

Hi,


Try,


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

jagan
MVP
MVP

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

reshmakala
Creator III
Creator III
Author

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.

reshmakala
Creator III
Creator III
Author

Hi Jagan,

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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