How to select cells in Excel accessed from VB6

[Lamont]

Is it possible select a range of cells in an Excel spreadsheet under access
from a VB6 program?
The many permutations I have tried shown below fail.
Surely I should be able to Select a range of cells, because I have to do
this to sort the rows.
Thanks for kind assistance. Lamont

Private DataRange As Excel.Range
Private DataSheet As New Excel.Worksheet
Private MyWorkbook As New Excel.Workbook
Private Sub Command1_Click()
Dim strMyXLSBkSpec As String
Form1.Show
strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet
containing data about paper documents
Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my
Excel File
MyWorkbook.Activate 'this works
Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works
Set DataRange = DataSheet.UsedRange 'this works
Debug.Print DataRange.Rows.Count 'this works
DataSheet.Activate 'this works
'DataRange.Select 'Select method of range class failed.
'DataSheet.Range(1, 1).Select 'Method "Range" of object failed
'DataSheet.Cells(1, 1).Select 'Select method of range class failed.
'Set DataRange = Range(1, 1) 'Select method of range class failed.
'Cells.Select 'Select method of range class failed.
'DataSheet.Cells.Select 'Select method of range class failed.
'Set DataRange = Range(A1, A99) 'Select method of range class failed.
'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class
failed.
'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class
failed.
End Sub

 

[Jim Jackson]

I think you will do better to ask your question in the Access forum.

 

[Lamont]

Thanks for kindness to reply. However, I did not mean Microsoft Access.
Possibly the term to describe manipulation of an Excel spreadsheet from
Visual Basic would be "automation".

 

[Lamont]

It struck me that perhaps the "Select" command applies only to a mouse
operation by a user looking at the spreadsheet in Excel. So, since a range
object has a sort method, I tried Myrange.sort, where presumeably the range
implicitly "selects" the desired cells. This gets the error message "Method
'Range' of object '_Global' failed. So, my problem is not only inability to
select, bu to sort when manipulating and Excel spreadhseet by code from VB6.

 

[Jim Jackson]

Lamont,

First, let me apologize for not understanding fully what you were asking.
Sometimes we get so involved with Excel and Access that we forget that
"access" has other meanings beside the database.

Does your UserForm have fields for specifying the Data Range? I do not see
anything in the code that specifies the desired Range. If the user needs to
specify the Range a couple of input boxes could be used for that (the first
asking for the beginning cell and the second asking for the ending cell.

Let me know if any of this makes sense.

 

[Lamont]

Thanks for your continued supprt.
I believe the range is specified by the coordinates in the parentheses.
DataSheet.Range(1, 1).Select
Set DataRange = Range(A1, A99)
The specification of the range should be with program code to ensure
consistent application in each run.

 

[NickHK]

Lamont,
Is you instance of Excel visible ?
You cannot .Select something you cannot see.

That said, it is seldom necessary to select objects before you use them.
Also do not use unqualified objects, remember you are in VB6, not Excel/VBA,
so:
Set DataRange = Range(A1, A99)
will not work, as to VB "Range" means nothings. What you need is:
Set DataRange = DataSheet.Range(A1, A99)

Also avoid using declarations like:
Private DataSheet As New Excel.Worksheet
as this code will never exit:
If DataSheet Is Nothing Then Exit Sub

Better:
Private DataSheet As Excel.Worksheet

Record a macro in Excel to see the correct syntax for specifying a range.

NickHK

 

[Lamont]

Hello Nick, Thank you for kindness to reply.

About: Is your instance of Excel visible ?

The instance of Excel is not visible. A couple of years ago when I operated
on Excel spreadsheets with VB6, the Excel instance did appear in the toolbar
and persisted after the program run. I just tried my old programs and it
does not do it anymore. I don't know if if was some other program now
discarded that caused the instance to appear, or not. Don't understand what
should make it appear or not.

About: it is seldom necessary to select objects before you use them.

I am trying to sort the rows in the spreadsheet. The Excel macro for sort
uses the Select statement to specify which rows to sort.
However, I just found another way to sort in the MSDN library at
MSDN Home MSDN Library\Devel Tools and Languages\Visual Studio\Vis Studio
Tools for Office\
Reference\Mictosoft.Office.TOols.Excel\NamedRange Class

The example code from this reference is inserted after the
=================== line at end of message.

Am loading the car to go out of town for a couple weeks just now, so will
try that means of sort when I get back.

Thank you for very your other very helpful instructions.

Are you Microsoft staff? In Redmond?

Best regards, Lamont
=========================================================================
Syntax

Visual Basic (Declaration)Public Function Sort ( _
<OptionalAttribute> Key1 As Object, _
<OptionalAttribute> Optional Order1 As XlSortOrder =
XlSortOrder.xlAscending, _
<OptionalAttribute> Key2 As Object, _
<OptionalAttribute> Type As Object, _
<OptionalAttribute> Optional Order2 As XlSortOrder =
XlSortOrder.xlAscending, _
<OptionalAttribute> Key3 As Object, _
<OptionalAttribute> Optional Order3 As XlSortOrder =
XlSortOrder.xlAscending, _
<OptionalAttribute> Optional Header As XlYesNoGuess = XlYesNoGuess.xlNo, _
<OptionalAttribute> OrderCustom As Object, _
<OptionalAttribute> MatchCase As Object, _
<OptionalAttribute> Optional Orientation As XlSortOrientation =
XlSortOrientation.xlSortRows, _
<OptionalAttribute> Optional SortMethod As XlSortMethod =
XlSortMethod.xlPinYin, _
<OptionalAttribute> Optional DataOption1 As XlSortDataOption =
XlSortDataOption.xlSortNormal, _
<OptionalAttribute> Optional DataOption2 As XlSortDataOption =
XlSortDataOption.xlSortNormal, _
<OptionalAttribute> Optional DataOption3 As XlSortDataOption =
XlSortDataOption.xlSortNormal _
) As Object

Visual Basic (Usage)Dim instance As NamedRange
Dim Key1 As Object
Dim Order1 As XlSortOrder
Dim Key2 As Object
Dim Type As Object
Dim Order2 As XlSortOrder
Dim Key3 As Object
Dim Order3 As XlSortOrder
Dim Header As XlYesNoGuess
Dim OrderCustom As Object
Dim MatchCase As Object
Dim Orientation As XlSortOrientation
Dim SortMethod As XlSortMethod
Dim DataOption1 As XlSortDataOption
Dim DataOption2 As XlSortDataOption
Dim DataOption3 As XlSortDataOption
Dim returnValue As Object

returnValue = instance.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,
Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1,
DataOption2, DataOption3)


=================
To sort data in a NamedRange control
Call the Sort method of the NamedRange control.

Visual Basic Copy CodeMe.Fruits.Sort( _
Key1:=Me.Fruits.Columns(1), Order1:=Excel.XlSortOrder.xlAscending, _
Key2:=Me.Fruits.Columns(2), Order2:=Excel.XlSortOrder.xlAscending, _
Orientation:=Excel.XlSortOrientation.xlSortColumns, _
Header:=Excel.XlYesNoGuess.xlNo, _
SortMethod:=Excel.XlSortMethod.xlPinYin, _
DataOption1:=Excel.XlSortDataOption.xlSortNormal, _
DataOption2:=Excel.XlSortDataOption.xlSortNormal, _
DataOption3:=Excel.XlSortDataOption.xlSortNormal)