Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013
Sorting Excel range from within VB2013 Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Go Back  Xtreme Visual Basic Talk > > > Sorting Excel range from within VB2013


Reply
 
Thread Tools Display Modes
  #1  
Old 07-24-2014, 12:24 PM
RajaP RajaP is offline
Newcomer
 
Join Date: Jul 2005
Posts: 11
Default 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.

Last edited by Cerian Knight; 07-25-2014 at 03:50 AM. Reason: Added [code]...[/code] tags
Reply With Quote
  #2  
Old 07-25-2014, 03:36 AM
Cerian Knight's Avatar
Cerian KnightSorting Excel range from within VB2013 Cerian Knight is offline
Polymath (in disciplina)

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 4,191
Arrow

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).
__________________
I got all the answers wrong on the GLAT, apparently even #9 (where I put a period in the middle of the box and labeled it 'singularity ripe for rapid inflation').

Last edited by Cerian Knight; 07-25-2014 at 04:17 AM.
Reply With Quote
  #3  
Old 07-25-2014, 04:36 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 383
Default

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.
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #4  
Old 07-25-2014, 02:52 PM
RajaP RajaP is offline
Newcomer
 
Join Date: Jul 2005
Posts: 11
Default

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.
Reply With Quote
  #5  
Old 07-25-2014, 02:54 PM
RajaP RajaP is offline
Newcomer
 
Join Date: Jul 2005
Posts: 11
Default

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.
Reply With Quote
  #6  
Old 07-26-2014, 08:16 PM
Cerian Knight's Avatar
Cerian KnightSorting Excel range from within VB2013 Cerian Knight is offline
Polymath (in disciplina)

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 4,191
Default

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.
__________________
I got all the answers wrong on the GLAT, apparently even #9 (where I put a period in the middle of the box and labeled it 'singularity ripe for rapid inflation').
Reply With Quote
  #7  
Old 07-28-2014, 03:44 PM
Cerian Knight's Avatar
Cerian KnightSorting Excel range from within VB2013 Cerian Knight is offline
Polymath (in disciplina)

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 4,191
Default

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.
__________________
I got all the answers wrong on the GLAT, apparently even #9 (where I put a period in the middle of the box and labeled it 'singularity ripe for rapid inflation').
Reply With Quote
Reply

Tags
excel sorting in vb


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013 Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013 Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
 
Sorting Excel range from within VB2013
Sorting Excel range from within VB2013
 
-->