Global Variables Between Access and Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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 "TransferSpreadsheet"
command?

I'm waiting anxiously for an reply.

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

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

If you need more detail, please let me know.
 
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 spreadsheet 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 worksheet to enter the
values?

Another question: I was trying to use the TransferSpreadsheet to IMPORT some
data and I got the run-time error 424:Object Required. How do I solve that?
 
There is a little more to it than that, but you are on the right track:

Dim xlApp as Ojbect
Dim xlBook As Object
Dim xlSheet as Object

period = "12/15/2006 - 1/1/2007"
Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Open("C:\Folder\example.xls", 0, True)
Set xlSheet = xlBook.Worksheets("SheetNameHere")
xlSheet.Cells(1,3).Value = period
xlBook.Close
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing

I could look at the syntax of your TranferSpreadsheet if you post it.
 
Yes! Thanks Klatuu, that's exactly what I was looking for. You're the best!
I'll even name my Sub 'Klatuu' (kidding).
Regarding that other problem... I've already solved it.

Thanks Again.

Bruno.
 
Back
Top