Pass variables from Access to Excel

[WhatTheFrick]

Using Office 2007, mdb in 2003 format, Excel workbooks in 2003 format.

I have been surprised to see no solution to this besides taking control of the Windows Clipboard.

I'm trying to use a variable in Excel whose value was SET in Access. So in Access, I have code to open a workbook, and in Excel I have code to use values on a specific sheet, but the sheet name was set in an Access form.

So, in Access I have a string variable (declared as Public) set to a form control's value (the following code might not be exactly right as I'm wrking from memory, but the SheetName variable IS getting set and the Excel Workbook IS opening and running my Excel code):

SheetName = Forms!FormName!Control value
xlapp.open (Excel workbook)

And in the Excel workbook I have code and a reference to the sheetname:

activeworkbook.sheets(SheetName).do something

In Access, I can debug/print the SheetName variable and see it. In Excel, though, the SheetName variable is empty.
How can I send this SheetName variable, with its value, to Excel? I tried using Global to declare it in Access and that didn't work either.

This example is not my only need for this; I'm going to need to send several variables to Excel besides just sheetnames, so workarounds where I don't need to send this particular variable won't solve my issue.



Any help on this would be appreciated. Thanks for reading.

[June7]

You can populate a cell in Excel with the value of the variable then the Excel code references the cell.