CODEWITHSUNDEEP
vbaexcelvbscript
joey1986
joey1986

Reputation: 1

VBS + Error : Microsoft Excel: Sort method of Range class failed

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.

Upvotes: 0

Views: 3424

Answers (2)

Martin Fridrich
Martin Fridrich

Reputation: 344

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

Const xlAscending = 1
Const xlYes = 1

Upvotes: 3

smagnan
smagnan

Reputation: 1257

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)

Upvotes: -1

Related Questions