Sorting an Excel object in Visio using a macro (VBA) - Stack Overflow

admin管理员组

文章数量:1343904

Currently trying to use the following function to sort some data in a Visio Excel OLE Object. I have confirmed that it can Select the Range (the line before sort), and it is correctly selecting all the data I an trying to sort. As soon as the next line runs (xlSheet.Range.Sort) I get a "Error 1004: Sort method of Range class failed".

I have tried:

1. Checking with Copilot
2. Checking Stack (I have spelt Ascending correctly heh)
3. Opening the excel object from Visio, which opens Excel, then adding a macro to sort the data, which works fine
4. Tried sorting just column B (B2:B17) with the same error
5. Tried including headers, didn't make a diff.

But I can't get this error to resolve

Dim xlApp As Object
Dim xlSheet As Object
Dim visPage As Visio.Page
Dim CList As Object

' Get the currently selected Visio page
Set visPage = Visio.ActiveWindow.Page

' Get the Excel object from the Visio page
Set CList = ActivePage.OLEObjects("Cable List").Object

' Get the Excel application and sheet
Set xlApp = CList.Application
Set xlSheet = CList.Sheets(1)

' Sort the range B2:E17 by column B
xlSheet.Range("B2:E17").Select
xlSheet.Range("B2:E17").Sort Key1:=xlSheet.Range("B2"), Order1:=xlAscending, Header:=xlNo

' Do something with the Visio page (e.g., refresh or update)
visPage.Refresh

' Clean up
Set xlSheet = Nothing
Set xlApp = Nothing
Set CList = Nothing

本文标签： Sorting an Excel object in Visio using a macro (VBA)Stack Overflow