RE: Global Variables Between Access and Excel

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I'm using VBA code to do all of that... is that what you call automation?
If so, can I open my .xls file and enter values in the spread*** via my
Access module, without calling any Excel modules?
Example(this is an Access module):
Sub test()
Dim wbr As Excel.Workbook
Dim period As String

period = "12/15/2006 - 1/1/2007"

Set wbr = GetObject("C:\Folder\example.xls")
wbr.Application.Cells(1,3) = period
End Sub

Would this work fine? How do I define a specific work*** to enter the
values?

Another question: I was trying to use the TransferSpread*** to IMPORT some
data and I got the run-time error 424:Object Required. How do I solve that?
"Klatuu" wrote:

There are a couple of ways you can accomplish this. The TransferSpread***
could be used to do this. All you would need to do is Identify the *** and
cell you want to put the value in. Help says the Range argument for
TransferSpread*** does not work for export. That is not true, it works.

Another would be to use automation to open the spread*** from Access,
enter the value into the location you want and save the spread*** with the
new value in it.

If you need more detail, please let me know.

"b_bussoloti" wrote:

Klatuu,
I wish to do something that enables the user to type in a desired period on
a form (in my access module i'll do whatever is necessary) and then when I
call the Excel module, I'll show in some cell that period typed.
It's just a I/O procedure where in Access is the 'I' and in Excel is
displayed the 'O'.
To make it clear, I'm just working with modules ... not Macros.
I think, if there's no way to declare this global variable, I could create a
table with just that information and then use the "TransferSpread***"
command?

I'm waiting anxiously for an reply.

Bruno.

"Klatuu" wrote:

No.
Unless you are using Automations. I doubt you are if you are using Macros.
We have to be careful using the word Macro here. A Macro in Excel is a VBA
procedure. In Access, it is not (Yes, I know it creates and executes VBA
code, but it is not the same conceptually).

Descibe what it is you want to accomplish and maybe we can help find a way
to do it.

"b_bussoloti" wrote:

Hi all. Quick Question: Can I declare a variable that gathers some data in
Access and then use it in an Excel Macro? How do I do it?
Note: The Access Module calls the Excel Macro.
.

Quantcast