I need to VBA Assistance to pass an Access variable as a parameter

[Guest]

Good morning,

I am using this Access module to control Excel as well. I am using an object
variable named OBJ declared as an Excel Application. I use this object
variable to call certain Excel macros, but I need to know if there is a way
to pass a variable that I obtain from an Access user form, and then run Excel
macros based on that input. What would be the syntax to use in my Acess
module to pass a variable, or so that my Excel macros can input the Access
variable as a parameter? Could the Excel macros see a global Access
variable? Thanks.

 

[Guest]

hi,
without seeing the xl macro code and your access code, i
can only guess this might work. put the variable on an xl
sheet somewhere then have the xl macro reference the cell
for use as a parameter.
to put data from access form on xl sheet
OBJ.sheets("Data").range("IV1") = Forms!Yourform.text1
this would go in your access code where you are creating
OBJ(xlapp).
As to the xl macro, i would have to see it to make
suggestions.
Repost if you have questions. I leave work in 20 min. so
if i don't post right back, i will tommorrow.

 

[David C. Holley]

No certain about the answer, but since Access can control Excel, the
Excel macros can exist within Access and operate on the appropriate
Workbook/Worksheets/Cells. From there its just a matter of passing the
variable from one function/sub to another.

David H

 

[Guest]

Thanks guys, I really appreciate your assistance. I am still not sure what to
try. These are samples of my Access and Excel procedures:
My Excel sub looks similar to:
Sub GET_CURR_YEAR_FROM_ACCESS()
Dim CURR_YEAR As Integer
Sheets.Add
Sheets("Sheet1").Name = "REFERENCE"
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("REFERENCE").Range( _
"A2"), PlotBy:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"={""JAN"","" FEB"","" MAR"","" APR"","" MAY"","" JUN"","" JUL"",""
AUG"","" SEP"","" OCT"","" NOV"","" DEC""}"
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="SCR NET INCOME %"

'This is where I need to use CURRENT YEAR value entered
' by a user on Access form
'Title
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "SCR Sales Offices Current Pendings "
& CURR_YEAR
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

My Access sub looks similar to:
Sub PASS_CURRENT_YEAR()
Dim CURR_YEAR As Integer
Dim OBJ As Object
Set OBJ = GetObject(, "EXCEL.APPLICATION")

CURR_YEAR = Forms!FORMNAME.Box0
‘I need to figure out a way to pass CURR_YEAR value from form to following
excel macro
OBJ.Application.Run "PERSONAL.XLS!GET_CURR_YEAR_FROM_ACCESS"
End Sub

 

[Guest]

I forgot to mention, I know I could probably program graphs in Access, but I
do not know how to program graphs in Access very well and I know Excel
better. I also use Excel because I can record a macro and Excel writes code
for me. That is why I am using Excel.

 

[Guest]

TRY THIRD PARTY VENDOR, MS DOES NOT SUPPORT THIS FEATURE

 

[John Nurick]

Hi Brent,

1) In your Access application, go to Tools|References and set a refernce
to the Microsoft Excel X.X Object Library. This makes the VBA
intellisense system recognise Excel objects Then change

Dim OBJ As Object
Set OBJ = GetObject(, "EXCEL.APPLICATION")

to something like:

Dim objXL As Excel.Application
Dim wbkW As Excel.Workbook
Dim wksW As Excel.Worksheet
Dim xlChart As Excel.Chart
Dim intCurrYear As Integer

Set objXL = CreateObject("Excel.Application")
Set wbkW = objXL.Workbooks.Open("location of your file")

Next paste your existing Excel code into the Access VB editor, and
modify it so it will run in Access while controlling Excel. For
instance, in Excel you've used things like
Sheets.Add
while here you need to be more explicit, e.g.

Set wksW = wbkW.Worksheets.Add
wksW.Name = "REFERENCE"
Set xlChart = wbkW.Charts.Add
With xlChart
.ChartType = xlLine
.SetSourceData blah blah
...
End With

...

'next line works because this code is running in Access
intCurrYear = CInt(Forms("FORMNAME").Controls("Box0").Value)

...

wbkW.Save
wbkW.Close
objXL.Quit False

 

[Guest]

Terrific, Thanks John.

Hi Brent,

1) In your Access application, go to Tools|References and set a refernce
to the Microsoft Excel X.X Object Library. This makes the VBA
intellisense system recognise Excel objects Then change


to something like:

Dim objXL As Excel.Application
Dim wbkW As Excel.Workbook
Dim wksW As Excel.Worksheet
Dim xlChart As Excel.Chart
Dim intCurrYear As Integer

Set objXL = CreateObject("Excel.Application")
Set wbkW = objXL.Workbooks.Open("location of your file")

Next paste your existing Excel code into the Access VB editor, and
modify it so it will run in Access while controlling Excel. For
instance, in Excel you've used things like
Sheets.Add
while here you need to be more explicit, e.g.

Set wksW = wbkW.Worksheets.Add
wksW.Name = "REFERENCE"
Set xlChart = wbkW.Charts.Add
With xlChart
.ChartType = xlLine
.SetSourceData blah blah
...
End With

...

'next line works because this code is running in Access
intCurrYear = CInt(Forms("FORMNAME").Controls("Box0").Value)

...

wbkW.Save
wbkW.Close
objXL.Quit False