Equivalent object references

[Barry M. Elson]

Hi all:

Does anyone know if there is an Access equivalent to the
Excel application.name method? I am trying to translate
some code from Excel and at this point I'm not having any
luck. Any help would be greatly appreciated.

Thanks,

Barry

 

[Ken Snell [MVP]]

If you use Application.Name in ACCESS, you get
Microsoft Access

Is that what you want?

 

[Barry M. Elson]

The Application.Name in Excel gives a reference to a
named range of cells or worksheet as I understand it.
The code looks like this:

Private Sub define_cmName(cmName As String, _
cmValue As String)

CurrentProject.AllMacros cmName = cmValue
Application.Names.Add Name:=cmName, _
RefersTo:=cmValue, _
visible:=False, _
MacroType:=3
End Sub

I am trying to create a modulel that will accomplish the
same in Access. Other subs and functions create errors
when some of these variables are passed.
Particularly, "the object doesn't contain the automation
object".

Thanks for getting back to me on this.

Barry

 

[Ken Snell [MVP]]

ACCESS does not use any such type of property, as it doesn't contain
spreadsheets?

What are you wanting ACCESS to do?

 

[Barry M. Elson]

Ken:
Thanks again for getting back. I am trying to mimic the
excel code to make variables passed to the listed code
available. I understand that Access does not contain
spreadsheets however, I would like to create a procedure
in Access that will make these variables available to
other procedures in Access. The variables being passed
in the Excel procedure are used as Optional arguments in
other procedures. When trying to use these in Access I
get errors as described in the previous message. I hope
that this helps.

Thanks again,

Barry

 

[John Nurick]

Hi Barry,

Application.Name and Application.Names are two quite different things.
Access has the former (as does Excel), but it doesn't have a concept of
"named ranges" any more than it has a concept of "cells". The nearest
equivalent is probably a select query that returns a subset of the data
from a table.

(On the other hand Access** can link or import data in named ranges in
Excel workbooks.)

**Note for pedants: for "Access", read "Jet"<g>.

 

[Douglas J. Steele]

You'll need to use Automation to instantiate an instance of Excel, and then
you can use essentially the same code as you would in Excel. (This does, of
course, require you to have Excel installed on the workstation along with
Access)

Something like the following should do it:

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Application.Workbooks.Open "C:\MyFolder\MySheet.xls"
objExcel.Application.Names.Add Name:=cmName, _
RefersTo:=cmValue, _
visible:=False, _
MacroType:=3

objExcel.Application.ActiveWorkbook.Close savechanges:=True
objExcel.Application.Quit
Set objExcel = Nothing


Afraid I don't understand what CurrentProject.AllMacros cmName = cmValue is
supposed to be doing, since I'm not aware of a CurrentProject object in
Excel.

 

[Barry M. Elson]

I have looked at something like this, but would like the
Access application to be stand alone. I am just curious
if I can delclare these (cmName and cmValue) as a Global
Constant so that when they are called from other sub
routines in the application as optional arguments that
they would be available and recognized. I'm sorry if I
can't be more clear as I am a little confused myself as
to how to implement and/or completely understand the
concept. Thanks everyones help and comments

Barry

-----Original Message-----
You'll need to use Automation to instantiate an instance of Excel, and then
you can use essentially the same code as you would in Excel. (This does, of
course, require you to have Excel installed on the workstation along with
Access)

Something like the following should do it:

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Application.Workbooks.Open "C:\MyFolder\MySheet.x
ls"

 

[Douglas J. Steele]

Yes, is possible to define Global Constants.

Public Const constname [As type] = expression

in a module. However, this means that you need to know the variables and
their values in advance: you can't do this dynamically.

If you need to be able to do it dynamically, you might consider a public
collection.

Global MyData As Collection

Sub SetValue(cmName As String, cmValue As String)
MyData.Add item:=cmValue, Key:=cmName
End Sub

Function GetValue(cmName) As String
GetValue = MyData.item(cmName)
End Function

Sub Example1()
Set MyData = New Collection
SetValue "FirstName", "Barry"
SetValue "LastName", "Elson"
End Sub

Sub Example2()
Debug.Print "FirstName = " & GetValue("FirstName")
Debug.Print "LastName = " & GetValue("LastName")
End Sub

If you call Example1, then Example 2 in the Immediate window, you'll get the
following

Call Example1
Call Example2
FirstName = Barry
LastName = Elson

Of course, we could probably give you better advice if we knew what you were
trying to do...

 

[Barry M. Elson]

Sorry for the delay in getting back to you. I have found
the answer I needed. I greatly appreciate all of your
expertise, it was most helpful. Thanks again.

Barry M. Elson

-----Original Message-----
Yes, is possible to define Global Constants.

Public Const constname [As type] = expression

in a module. However, this means that you need to know the variables and
their values in advance: you can't do this dynamically.

If you need to be able to do it dynamically, you might consider a public
collection.

Global MyData As Collection

Sub SetValue(cmName As String, cmValue As String)
MyData.Add item:=cmValue, Key:=cmName
End Sub

Function GetValue(cmName) As String
GetValue = MyData.item(cmName)
End Function

Sub Example1()
Set MyData = New Collection
SetValue "FirstName", "Barry"
SetValue "LastName", "Elson"
End Sub

Sub Example2()
Debug.Print "FirstName = " & GetValue("FirstName")
Debug.Print "LastName = " & GetValue("LastName")
End Sub

If you call Example1, then Example 2 in the Immediate window, you'll get the
following

Call Example1
Call Example2
FirstName = Barry
LastName = Elson

Of course, we could probably give you better advice if we knew what you were
trying to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have looked at something like this, but would like the
Access application to be stand alone. I am just curious
if I can delclare these (cmName and cmValue) as a Global
Constant so that when they are called from other sub
routines in the application as optional arguments that
they would be available and recognized. I'm sorry if I
can't be more clear as I am a little confused myself as
to how to implement and/or completely understand the
concept. Thanks everyones help and comments

Barry instance
of Excel, and then
objExcel.Application.Workbooks.Open "C:\MyFolder\MySheet.x
ls" CurrentProject.AllMacros
cmName = cmValue is arguments
in

.