How to reference a range or series of cells in Excel

[meg99]

using 2003. Is it possible to reference a range or a particular cell
in Excel without opening Excel or can Excel be opened it in the
background making it invisible to the user?

Problem: I have a macro that creates an ID number based on the WBS.
I need to create a 2 letter designator in the ID from the WBS.
Example:

WBS 2.1.1 = Systems Engineering - the 2 letter ID would be SE
consequently I want the number to be xxxSExxxxx (the xxx prtion I
have already coded)

The WBS dictionary is in Excel and quite large and I don't want to
replicate that in an array inside the macro. I would like to be able
to capture that directly from the Excel file. That would also mean
that any changes to the WBS dictionary would be automaticlly captured
in Project without any changes to the macro.

meg99

 

[meg99]

using 2003.  Is it possible to reference a range or a particular cell
in Excel without opening Excel or can Excel be opened it in the
background making it invisible to the user?

Problem:  I have a macro that creates an ID number based on the WBS.
I need to create a 2 letter designator in the ID from the WBS.
Example:

WBS 2.1.1 = Systems Engineering - the 2 letter ID would be SE
consequently I want the number to be xxxSExxxxx  (the xxx prtion I
have already coded)

The WBS dictionary is in Excel and quite large and I don't want to
replicate that in an array inside the macro.  I would like to be able
to capture that directly from the Excel file.  That would also mean
that any changes to the WBS dictionary would be automaticlly captured
in Project without any changes to the macro.

meg99

This is solved - I created an import mask for the Excel spreadsheet
and populated a text field with the appropriate ID based on the WBS
code.

meg99

 

[John]

using 2003. Is it possible to reference a range or a particular cell
in Excel without opening Excel or can Excel be opened it in the
background making it invisible to the user?

Problem: I have a macro that creates an ID number based on the WBS.
I need to create a 2 letter designator in the ID from the WBS.
Example:

WBS 2.1.1 = Systems Engineering - the 2 letter ID would be SE
consequently I want the number to be xxxSExxxxx (the xxx prtion I
have already coded)

The WBS dictionary is in Excel and quite large and I don't want to
replicate that in an array inside the macro. I would like to be able
to capture that directly from the Excel file. That would also mean
that any changes to the WBS dictionary would be automaticlly captured
in Project without any changes to the macro.

meg99

meg99,
What you want to do is very feasible. Many of my macros export Project
data to Excel. In order to speed up the transfer Excel is opened in the
background, data is transferred and then when everything is formatted
Excel is made visible just before the macro finishes. What you want to
do is essentially the same thing except you don't need to make Excel
visible. I generally use the following code to open Excel in the
background:
Set xl = GetObject(, "Excel.application")
If Err <> 0 Then
On Error GoTo 0
Set xl = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Excel application is not available on this workstation" _
& Chr(13) & "Install Excel or check network connection",
vbCritical, _
"Accouting month Export - Fatal Error"
Set xl = Nothing
On Error GoTo 0 'clear error function
Exit Sub
End If
End If
On Error GoTo 0

'Keep Excel in the background and minimized until spreadsheet is done
(speeds transfer)
'NOTE: Items with a 'Reference annotation will not work without a
reference to the Excel object library
xl.Visible = False
xl.ScreenUpdating = False
xl.WindowState = xlminimized 'Reference
xl.DisplayAlerts = False

There are many ways to reference Excel cells or a range of cells. I
often set up an object for the desired workbook and worksheet using the
following:
Set s = xl.Workbooks(BookNam).Worksheets(TaskTitle)

Then I reference a cell or range with:
s.Range("A3").Value
or
s.Cells(1,3).Value

Hopefully this will get you started.

John
Project MVP