Run Access Coding from Excel

[robcarr]

Hi,

I am trying to run the following coding from Excel.

Public Function macrocoding()
LucentData
FormatLogin
DoCmd.RunMacro "macauxdaily"
DoCmd.RunMacro "macsumdaily"
DoCmd.RunMacro "macloginLogout"
OutboundCallData
clearExcelFiles
InsertUpdateNames
Email_Data

End Function

[\code]

I have created a macro  in access but can not get it to work


[COLOR=red][b][i][u]Hope this is of use, Rob.[/u][/i][/b][/color][yoda]

 

[PHV]

How your ExcelVBA code is supposed to launch access and open the relevant database ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[robcarr]

this is the coding I am using in excel to run the coding in the previous post

Sub AccessTest1()
Dim A As Object

Set A = CreateObject("Access.Application")
A.Visible = False

A.OpenCurrentDatabase ("S:\BT Broadband Technical\Performance Tracker\Agent Performance Tracker.mdb")

A.DoCmd.RunMacro "AccessMacro"

End Sub

Hope this is of use, Rob.

 

[robcarr]

before anyone asks I know the docmd wont work as it is not the right macro,

what I am essentially trying to do is run the first post coding from excel, as I have to load data into access each day, and I have automated the creation of the source file for the input I just need to automate the data entry of the source data, (i usually have to go to a form and select a date and run the coding), want to try and avoid this if possible.

Hope this is of use, Rob.

 

[Rofeu]

Hi,

how is your code not working? Do you get an error somewhere? Does it just not fire the Access macro?

Try adding the module name to the macro name in your DoCmd.
i.e. A.DoCmd.RunMacro "Module1.AccessMacro"

Cheers,

Roel

 

[robcarr]

i think I was getting confused, as I was looking for a different command, I thought the domd.runmacro was for the macros within access, didnt realise I could put the module name in front and use it this way, will try this lateer let you know, thanks for input so far.

Hope this is of use, Rob.

 

[robcarr]

have tried this.

Sub AccessTest1()
      Dim A As Object

      Set A = CreateObject("Access.Application")
      A.Visible = False

      A.OpenCurrentDatabase ("S:\BT Broadband Technical\Performance Tracker\Agent Performance Tracker.mdb")

      A.DoCmd.RunMacro "automate.macrocoding"

   End Sub

to run this macro

Sub macrocoding()
LucentData
FormatLogin
DoCmd.RunMacro "macauxdaily"
DoCmd.RunMacro "macsumdaily"
DoCmd.RunMacro "macloginLogout"
OutboundCallData
clearExcelFiles
InsertUpdateNames
Email_Data

End Sub

this coding is stored in automate module in vba in access.

but it says it cant find macro "automate."

it is strange that it is saying cant find 'automate.' - like this, I dont have a "." on any of the coding but it is showing it.

any help on this.


Hope this is of use, Rob.

 

[robcarr]

got it working using this

Set A = CreateObject("Access.Application")
            A.OpenCurrentDatabase ("S:\Performance Tracker\Agent Performance Tracker.mdb")
      A.Visible = False
      A.DoCmd.RunMacro "macros"
      A.Quit

then have a macro in access to call a function that runs the coding that I require.

Hope this is of use, Rob.

 

[mrdod]

You might want to do some research on creating an ADO connection. I have written lots of programs sending information from Excel to Access (Access to Excel) using SQL and an ADO connection. Just make sure you go to Tools- References-and check the Microsoft ActiveX Data Objects 2.0 Library Not sure this helps but there is a lot of information about how to set this up.