Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   Sorting Excel range from within VB2013 (http://www.xtremevbtalk.com/-net-office-automation/327188-sorting-excel-range-vb2013.html)

RajaP 07-24-2014 12:24 PM

Sorting Excel range from within VB2013
 
I am trying to sort an excel range from within VB and it is not working. Intellisense does not give an error, but trying to run does. Below is the sorting part that is not working. I am using Visual Studio 2013, VB module and Excel 2013. I do some calculations and write to book1.xls and create a table in B51 to H132. I want to sort the table by column G. Below is my code. It stops on Sort command and says "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."
Code:

      Dim r1 As Range
        sht1.Range("B51").Select()
        r1 = sht1.Range("B51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)


        Dim xlSortOnValues As Long
        Dim xlDescending As Long
        Dim xlSortNormal As Long
        Dim xlSortRows As Long
        Dim xlPinYin As Long
        xlSortOnValues = 0
        xlDescending = 1
        xlSortNormal = 0
        xlSortRows = 1
        xlPinYin = 1
        Dim table As Microsoft.Office.Interop.Excel.ListObject
        table = sht1.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, _
        r1, , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlGuess)
        table.Name = "CFMLIST"
        Dim sortBy As Range
        sortBy = sht1.Range("G1").End(XlDirection.xlDown)


        r1.Sort(Key1:=sortBy, _
Order1:=Microsoft.Office.Interop.Excel.XlSortOrder.xlDescending, _
Header:=Microsoft.Office.Interop.Excel.XlYesNoGuess.xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, _
SortMethod:=Microsoft.Office.Interop.Excel.XlSortMethod.xlStroke, _
DataOption1:=Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal)

What is the correct way to sort the range? How can I avoid having to repeatedly type Microsoft.Office.Interop.Excel.....? Thanks in advance for the help.

Cerian Knight 07-25-2014 03:36 AM

I believe you should rewrite to specify Imports to use with Range, etc. That might also fix the Intellisense, but not sure. See here for an example:
http://support.microsoft.com/kb/302094
You also should not have to Dim the xl constant values (xlSortOnValues, etc.) yourself, as they should come with the Imports.

To answer the second question, use With...End With if necessary, but notice how the example avoids that issue.

Do that and then let us see what you have if the Sort is still not working (after Dim using r1 as an Excel.Range).

TheRealTinTin 07-25-2014 04:36 AM

Hi RajaP,

I've not had to interface with Excel from .Net so do not claim to be an expert, but I think you have to sort tables differently to ranges - see below:
Code:

Private Sub SortTable(sheet As Excel.Worksheet, _
    tableName As String, sortyBy As Excel.Range)
 
    sheet.ListObjects(tableName).Sort.SortFields.Clear()
    sheet.ListObjects(tableName).Sort.SortFields.Add(sortyBy, _
        Excel.XlSortOn.xlSortOnValues)
    With sheet.ListObjects(tableName).Sort
        .Header = Excel.XlYesNoGuess.xlYes
        .MatchCase = False
        .SortMethod = Excel.XlSortMethod.xlPinYin
        .Apply()
    End With
End Sub

As for avoiding repeatedly typing Microsoft.Office.Interop.Excel, as Cerian Knight has mentioned, using imports will save you the hassle. Also as Cerian Knight mentioned, you shouldn't have to declare Excel constants if the project reference has been set.

RajaP 07-25-2014 02:52 PM

I tried to do what http://support.microsoft.com/kb/302094 says and things break even further. Even then I still have to type in Microsoft.Office.Interop.Excel every time. Unlike the example says, VB does not like Dim excelapp Excel.Application above the sub buttonClick statement.

I have to leave it below the sub.. statement. I did add the com library for Office 15 excel. but that made no difference. My sort method still fails. The error detail says, "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

My whole code including imports is as follows.

Option Explicit On
Imports Microsoft.Office.Tools.Excel
Imports System
Imports Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles txtXLS.TextChanged

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim excelapp As New Microsoft.Office.Interop.Excel.Application
excelapp = CreateObject("Excel.Application")
Dim sht1 As Microsoft.Office.Interop.Excel.Worksheet
Dim sht2 As Microsoft.Office.Interop.Excel.Worksheet
Dim WB1 As Microsoft.Office.Interop.Excel.Workbook

excelapp.Visible = True
WB1 = excelapp.Workbooks.Open("L:\VS2013\vsto\Book1.xlsx")

sht1 = WB1.Sheets("MAX UFBC (Cell Friction Metric)")
sht2 = WB1.Sheets("Max Node for CFM")
excelapp.Visible = True
sht1.Select()
sht1.Cells(50, 1).Value = "Rod#"
sht1.Cells(50, 2).Value = "GE-i"
sht1.Cells(50, 3).Value = "GE-j"
sht1.Cells(50, 4).Value = "PNPS-xx"
sht1.Cells(50, 5).Value = "PNPS-yy"
sht1.Cells(50, 6).Value = "Site Rod"
sht1.Cells(50, 7).Value = "CFM"
sht1.Cells(50, 8).Value = "Max Node"

Dim Csize As Long

sht1.Range("K1").Value = "max i"
sht1.Range("L1").Value = "=MAX(R[5]C[-11]:R[5]C[42])"
Csize = sht1.Range("L1").Value

Dim GNFi(Csize) As Long
Dim GNFj(Csize) As Long
Dim PNPSxx(Csize) As Long
Dim PNPSyy(Csize) As Long
Dim CFM(Csize, Csize) As Long
Dim max_node(Csize, Csize) As Long
Dim n As Long

n = 0
For i = 1 To Csize
GNFi(i) = sht1.Cells(6, i + 1).Value
PNPSxx(i) = 2 * GNFi(i)

For j = 1 To Csize
GNFj(j) = sht1.Cells(j + 6, 1).Value
PNPSyy(j) = 2 * Csize + 1 - 2 * GNFj(j)
CFM(i, j) = sht1.Cells(j + 6, i + 1).Value
max_node(i, j) = sht2.Cells(j + 6, i + 1).Value
If (CFM(i, j) <> 0) Then
n = n + 1
sht1.Cells(50 + n, 1).Value = n
sht1.Cells(50 + n, 2).Value = GNFi(i)
sht1.Cells(50 + n, 3).Value = GNFj(j)
sht1.Cells(50 + n, 4).Value = PNPSxx(i)
sht1.Cells(50 + n, 5).Value = PNPSyy(j)
sht1.Cells(50 + n, 6).Value = PNPSxx(i) & " " & PNPSyy(j)
sht1.Cells(50 + n, 7).Value = CFM(i, j)
sht1.Cells(50 + n, 8).Value = max_node(i, j)
End If

Next j
Next i
Dim r1 As Range
sht1.Range("B51").Select()
r1 = sht1.Range("B51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDow n).End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)


Dim xlSortOnValues As Long
Dim xlDescending As Long
Dim xlSortNormal As Long
Dim xlSortRows As Long
Dim xlYesNoGuess As Long
Dim xlTopBottom As Long
xlSortOnValues = 0
xlDescending = 1
xlSortNormal = 0
xlSortRows = 1
XlYesNoGuess = 1
XlTopBottom = 1
Dim table As Microsoft.Office.Interop.Excel.ListObject
table = sht1.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSource Type.xlSrcRange, _
r1, , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo)
table.Name = "CFMLIST"
Dim sortBy As Range
sortBy = sht1.Range("G51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDow n)
'


r1.Sort(Key1:=sortBy, Order1:=xlDescending, Header:=XlYesNoGuess, OrderCustom:=1, MatchCase:=False, Orientation:=XlTopBottom)


r1 = sht1.Range("G51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDow n)

r1.FormatConditions.Add(Type:=Microsoft.Office.Interop.Excel.XlFormatC onditionType.xlCellValue, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlG reater, _
Formula1:="=339.5")
r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
With r1.FormatConditions(1).Interior
.PatternColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
.Color = 255
.TintAndShade = 0
End With
r1.FormatConditions(1).StopIfTrue = False
r1.FormatConditions.Add(Type:=Microsoft.Office.Interop.Excel.XlFormatC onditionType.xlCellValue, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlB etween, _
Formula1:="=99.5", Formula2:="=159.5")
r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
With r1.FormatConditions(1).Interior
.PatternColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
.Color = 65535
.TintAndShade = 0
End With
r1.FormatConditions(1).StopIfTrue = False
r1.FormatConditions.Add(Type:=Microsoft.Office.Interop.Excel.XlFormatC onditionType.xlCellValue, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlG reater, _
Formula1:="=159.5")
r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
With r1.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With r1.FormatConditions(1).Interior
.PatternColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
.Color = 13551615
.TintAndShade = 0
End With
r1.FormatConditions(1).StopIfTrue = False

sht1.Range("F51").Select()
r1 = sht1.Range("F51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDow n)
r1.FormatConditions.Delete()
r1 = sht1.Range("G51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDow n)
sht1.Range("F51").Activate()
r1.FormatConditions.Add(Type:=Microsoft.Office.Interop.Excel.XlFormatC onditionType.xlCellValue, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlG reater, _
Formula1:="=339.5")
r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
With r1.FormatConditions(1).Interior
.PatternColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
.Color = 255
.TintAndShade = 0
End With
r1.FormatConditions(1).StopIfTrue = False
r1 = sht1.Range("G51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDow n)
sht1.Range("F51").Activate()
r1.FormatConditions.Add(Type:=Microsoft.Office.Interop.Excel.XlFormatC onditionType.xlCellValue, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlB etween, _
Formula1:="=99.5", Formula2:="=159.5")
r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
With r1.FormatConditions(1).Interior
.PatternColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
.Color = 65535
.TintAndShade = 0
End With
r1.FormatConditions(1).StopIfTrue = False

r1.FormatConditions.Add(Type:=Microsoft.Office.Interop.Excel.XlFormatC onditionType.xlCellValue, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlG reater, _
Formula1:="=159.5")
r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
With r1.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With r1.FormatConditions(1).Interior
.PatternColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
.Color = 13551615
.TintAndShade = 0
End With
r1.FormatConditions(1).StopIfTrue = False


sht1.PageSetup.PrintArea = r1.Select



sht1.ExportAsFixedFormat(Type:=Microsoft.Office.Interop.Excel.XlFixedF ormatType.xlTypePDF, Filename:= _
"make-CFM-list.pdf", Quality:=Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQuality Standard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False)
excelapp.ActiveWorkbook.SaveAs()
excelapp = Nothing




End Sub
End Class

I will appreciate any help. I am a novice. Please be specific with what you would like me to try.

RajaP 07-25-2014 02:54 PM

I have imports statements at the top; but even so, w/o putting Microsoft.Office.Interop.Excel... it does not work. Do i need something more than


Option Explicit On
Imports Microsoft.Office.Tools.Excel


Imports System
Imports Microsoft.Office.Interop.Excel


Also, w/o declaring the sort variables, VB gives errors.

Cerian Knight 07-26-2014 08:16 PM

I was going to install 'VS2013 Express for Desktops' here at home, that is what you have, correct? However my Dell Precision workstation is still on Windows XP (it is an old tired system, so I won't upgrade it), which is not compatible with VS2013, so this will have to wait till Monday when I'm at work and can try it.

Last time I tried this on either VB 2008 or 2010 (I forget which), it worked fine.

Cerian Knight 07-28-2014 03:44 PM

I got the Microsoft code example working as described. But I can see where it wouldn't work using 'Imports Microsoft.Office.Interop.Excel'

Use 'Imports Microsoft.Office.Interop' instead and type 'Excel.' before each Excel Property, etc. that you want to use to avoid namespace ambiguity.


All times are GMT -6. The time now is 01:09 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.