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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brent E - 01 Mar 2005 19:26 GMT

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.

Reply to this Message

anonymous@discussions.microsoft.com - 01 Mar 2005 20:37 GMT

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.

>-----Original Message-----
>Good morning,
[quoted text clipped - 9 lines]
>
>.

Reply to this Message

Brent E - 02 Mar 2005 00:21 GMT

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

> hi,
> without seeing the xl macro code and your access code, i
[quoted text clipped - 30 lines]
> >
> >.

Reply to this Message

John Nurick - 02 Mar 2005 07:26 GMT

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

>Thanks guys, I really appreciate your assistance. I am still not sure what to
>try. These are samples of my Access and Excel procedures:
[quoted text clipped - 73 lines]
>> >
>> >.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Brent E - 02 Mar 2005 16:45 GMT

Terrific, Thanks John.

> Hi Brent,
>
[quoted text clipped - 125 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

Brent E - 02 Mar 2005 00:29 GMT

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.

> hi,
> without seeing the xl macro code and your access code, i
[quoted text clipped - 30 lines]
> >
> >.

Reply to this Message

David C. Holley - 01 Mar 2005 20:37 GMT

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

> Good morning,
>
[quoted text clipped - 6 lines]
> variable as a parameter? Could the Excel macros see a global Access
> variable? Thanks.

Reply to this Message

anonymous@discussions.microsoft.com - 02 Mar 2005 04:06 GMT

TRY THIRD PARTY VENDOR, MS DOES NOT SUPPORT THIS FEATURE

>-----Original Message-----
>Good morning,
[quoted text clipped - 9 lines]
>
>.

Reply to this Message