Sorting excel export through Macro

[reshmakala]

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.

[tamilarasu]

Hi,

Try,

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

[jagan]

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]

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]

Hi Jagan,

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

[marcus_sommer]

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