0

this is my first post, I'm a SysAdmin that recently took over the position, the previous person that had the job has retired and I really don't know how to contact him, so i have to figure everything out myself.

The problem that i have is the following, there's one scheduled task that we have in one of our W2K8 R2 boxes, this task basically just calls a .vbs (script) that queries AD then puts the output in Excel and sends an email to a few people with the excel file as an attachment, we recently upgraded our Office suite to 2013 from 2010 (about 4 months ago).

We were not aware that the report was not being sent out, until one of the people that usually review it, contacted us and ask for the report, I wen to check the Log of the scheduled task but it doesn't log any errors, then while reviewing the log of the script I noticed that after the routine that performs the query to ad has retrived all the information and tries to put all in the excel spreedsheet something happens and it suddently stops, there's just one error logged, see next line : 

Microsoft Excel: Sort method of Range class failed

I honestly think that the recent upgrade to 2013 did something and that's why is not generating the report, but I would like to get a second opinion, from people with more expirence in VB and Excel.

The piece of code that i think adds the info to excel is the following : 

'sortthedetailssheet
'
Set objRange=objSheetDetails.columns("A:D").EntireColumn

Set objRange2=objSheetDetails.Range("C1")
Set objRange3=objSheetDetails.Range("A1")

objRange.Sort objRange2, xlAscending, objRange3, , xlDescending, , , xlYes
'
'sorttheMembershipsheet
'
Set objRange=objSheetMembership.columns("A:C").EntireColumn

Set objRange2=objSheetMembership.Range("A1")
Set objRange3=objSheetMembership.Range("B1")

objRange.Sort objRange2, xlAscending, objRange3, , xlAscending, , , xlYes

I would appreciate any suggestions, since I'm very new to vba and think is going to take me a while get up to speed.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
joey1986
  • 1
  • 1
  • 1
  • Mmmh, don't you have the line where it fails? It sould highlight I think. Because I tested this code with random datas and it seems to work (I mean: no `Sort method of Range class failed` error, and the code seems to run well ...) – smagnan Aug 22 '14 at 06:54
  • Hi Smagnan, thanks for the replied, the full error message is the following one : CountGroupMembership.vbs(313, 1)Microsoft Excel: Sort method of Range class failed I believe that the line where it fails in the one between the parentheses, which would be (313, 1), now if I go to the actual script that line would be the following : objRange.Sort objRange2,xlAscending,objRange3,,xlAscending,,,xlYes – joey1986 Aug 25 '14 at 13:16

2 Answers2

3

The problem lies in use of Excel constants, which are not defined in VB context. Add this snippet.

Const xlAscending = 1
Const xlYes = 1
Martin Fridrich
  • 314
  • 2
  • 8
  • See [How to use excel built-in constant in VBS](https://stackoverflow.com/a/39956808) for a more detailed explanation. – user692942 Dec 21 '22 at 09:35
-1

Your potential problem

After some more tests, I think I found what may cause this error in your case: objSheetDetails refers to a protected (password preotected?) sheet so .sort can't actually sort because it doesn't have the rights.

Example

1./ this works 

Sheets("Feuil3").Protect Password:="pswdtest"
Sheets("Feuil3").Unprotect Password:="pswdtest"

Set objRange = Sheets("Feuil3").Columns("F:H").EntireColumn
Set objRange2 = Sheets("Feuil3").Range("F1")
Set objRange3 = Sheets("Feuil3").Range("G1")

objRange.Sort objRange2, xlAscending, objRange3, , xlAscending, , , xlYes

1./ this fails

Sheets("Feuil3").Protect Password:="pswdtest"

Set objRange = Sheets("Feuil3").Columns("F:H").EntireColumn
Set objRange2 = Sheets("Feuil3").Range("F1")
Set objRange3 = Sheets("Feuil3").Range("G1")

objRange.Sort objRange2, xlAscending, objRange3, , xlAscending, , , xlYes

Sheets("Feuil3").Unprotect Password:="pswdtest"

Solution(s)

I assume the protection need to be kept. (If not, just remove the protection, it should work...)

  1. .Unprotect before the sorting part and .Protect after or
  2. If the code set the protection somewhere, add UserInterFaceOnly:=True so it will protect the sheet for the user but not the code (XXX.Protect UserInterFaceOnly:=True)
smagnan
  • 1,197
  • 15
  • 29
  • The issue is VBScript [doesn't support Named Arguments](https://stackoverflow.com/a/42194325/692942). This is nothing to do with the protection of the workbook. – user692942 Dec 21 '22 at 09:33