Solved: Set Excel named range using VB?

[andrew93]

Hi

I have a project where I'm using VB6 and I would like to create a named range in an Excel spreadsheet. I have opened the spreadsheet using DAO and copied data into it and it is working fine and dandy but now I am stuck (I've searched here, Google, VB City, MrExcel etc without luck).

How do I set the name for a selected range using VB? I can see the VBA equivalent code (I'm using VB6 with VBA references & Excel references enabled) but the sheet name, the name I want to use for the named range and the range I want to name are all held in variables.

If I use the macro recorder the equivalent code in VBA looks like this :

'ActiveWorkbook.Names.Add Name:="T_Classification", RefersToR1C1:= _
"=Sheet7!R1C1:R18C3"
but how do I code this if the new name, the sheet name and the range values are held in variables? My range variable values are stored as numbers (e.g. Row 1, Column 1 etc.)

TIA, Andrew

[Bob Phillips]

ActiveWorkbook.Names.Add Name:=sRangeName, _
RefersToR1C1:="=" & sSheetName & "!" & _
"R" & iFirstRow & "C" & iFirstCol & ":" & _
"R" & iLastRow & "C" & iLastCol

[mdmackillop]

Hi Andrew,
Here's some code cobbled together from the VB6 Help file


Private Sub Command1_Click()
GetExcel
End Sub
Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
Dim ws As Worksheet

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
' DetectExcel
' Set the object variable to reference the file you want to see.
Set MyXL = GetObject("c:\AAA\MYTEST.XLS")
' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows("MyTest").Visible = True
MyXL.Names.Add Name:="TrialRange", RefersToR1C1:= _
"=Sheet3!R1C3:R15C3"

' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
MyXL.Save
MyXL.Application.Quit
End If
End Sub

[andrew93]

Thanks very much for your replies.

I tried the suggestion from xld and it worked perfectly thanks!

Cheers
Andrew