I need some help with VBA and macros

[Heather]

Hi, I am having some trouble automating something -- here's what I have to
run it's a macro in Excel VBA from Access -- but I need to add that it puts
my output into the sheet and I'm unsure how to add that logic

Sub RunAccessMacro()

Dim appAcc As Access.Application
'Opens Access or gets reference to app already running

Set appAcc = New Access.Application
Application.DisplayAlerts = False
'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase ("P:\Analytic\Heather\Individual\MemberSpans.mdb")

appAcc.DoCmd.RunMacro "Macro1"

'Close Access
appAcc.Quit

'This will close Access, even w/o the 'Quit' command
Set appAcc = Nothing
Application.DisplayAlerts = True
End Sub

 

[SongBear]

Heather

Some discussion points and questions.

Your local Excel VBA macro is what will put the data retrieved from the DB
into the Excel ranges that you select, therefore this Excel VBA Macro needs
visibility of that Access data.

I do not know exactly what 'Macro1' does in Access so I do not know what
Access objects might be accessed by it or how.

The way I solved this was to open the DB from the Excel VBA code, sort of
like you did; the exact details of how were a little different but may not
be significant.

But I then also opened a table using a Dim'ed recordset object and a simple
'Select' statement. I built tha select statement as a string in the Excel
code.

Then I accessed fields in that table from my Excel code. It helped that I
was familiar with both Excel VBA and Access VBA. Once you establish the
correct references to Microsoft Access your Excel VBA code will recognize and
run Access VBA keywords and commands.

So, to review, I defined an internal Access object; beyond the dim statement
for the database itself i also dimmed a recordset object, and I populated it
with a table in the DataBase that I had opened.
Code Snippet:

stQry = "SELECT " & stTableName & ".* FROM " & stTableName & " ORDER BY
[Session_Stamp] ASC;"
Set rst = db.OpenRecordset(stQry, dbOpenDynaset)

I ordered the records in the retrieved data by the date-time stamp so I
could retrieve the latest entry.
Once the recordset was opened, my Excel VBA code was able to use Acces VBA
commands against it.
I have added records to Access tables this way, but this time I simply used
the 'Movelast' Access VBA command to access the last record in the table (the
one I was interested in). The actual code snippet follows:

With rst
.MoveLast
Range("I23").Value = .Fields("Total_Wins")
Range("I24").Value = .Fields("Total_Losses")
Range("I16").Value = .Fields("Tracking_Wins")
Range("I17").Value = .Fields("Tracking_Losses")
End With

Note: I already knew the field names for the table that I populated the
dim'ed recordset (rst) that I had opened. I also knew the Access table name
(the same as the Excel worksheet name, in this case - handy).

Note: Since I was working from a specific worksheet's code page, I didn't
have to specify which worksheet. If you are working in a general module, you
will need to specify which worksheet you want the data placed in. Better that
way if you are working across multiple sheets.

Note: Your Excel VBA code needs visibility of the data, I solved this by
actually opening a recordset from Excel; not knowing the contents of Macro1,
I do not know how you will specify what data to put into the ranges.

Please let us know if this answers your question, or if you need
clarification or more information.

SongBear