asked on

Combine two Macros into one using a command button in Excel

I have two macro that I created that I would like to combine them into one Macro using a command button in Excel from the Mod Cost Calculator tab in Excel. The first macro comes from the Excel Tab titled, "Database Tab", and the second Macro reference excel tab Data position Tab. Please help me!

Sub ADOFromExYoucelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, R As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=R:\Modular Cost Tables\FY 2018A\RCA Database Prep\Terry's Updates\FY 2018 AOP RCA Database_062717_SCOPS Locations.accdb;"
 ' open a recordset
Set rs = New ADODB.Recordset
rs.Open "[FY18 Fee Review Mod Cost Table Data]", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
R = 2 ' the start row in the worksheet
Do While Len(Range("A" & R).Value) > 0
    ' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Project_ID") = Range("A" & R).Value
        .Fields("Item") = Range("B" & R).Text
        .Fields("Object_Class") = Range("C" & R).Text
        .Fields("OC_Name") = Range("D" & R).Text
        .Fields("ProjectTask") = Range("E" & R).Text
        .Fields("Fund") = Range("F" & R).Text
        .Fields("Prog") = Range("G" & R).Text
        .Fields("Object") = Range("H" & R).Text
        .Fields("FeeReviewOrgDash") = Range("I" & R).Text
        .Fields("FeeReviewOrgName") = Range("J" & R).Text
        .Fields("Request_Category") = Range("K" & R).Text
        .Fields("Cost_Type") = Range("L" & R).Text
        .Fields("Obj_Type") = Range("M" & R).Text
        .Fields("FY_2018_Total") = Range("N" & R).Value
        .Fields("FY_2019_Total") = Range("O" & R).Value
        .Fields("FY_2020_Total") = Range("P" & R).Value
        .Fields("Locality") = Range("Q" & R).Text
        .Fields("Office") = Range("R" & R).Text
        ' add more fields if necessary…
        .Update ' stores the new record
    End With
    R = R + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox "Data has been exported into Access."
End Sub

Open in new window

The Data position Tab Macro

Sub ADOFromExYoucelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, R As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=R:\Modular Cost Tables\FY 2018A\RCA Database Prep\Terry's Updates\FY 2018 AOP RCA Database_062717_SCOPS Locations.accdb;"
 ' open a recordset
Set rs = New ADODB.Recordset
rs.Open "[FY18 Position Tab Summary]", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
R = 2 ' the start row in the worksheet
Do While Len(Range("A" & R).Value) > 0
    ' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Project_ID") = Range("A" & R).Value
        .Fields("Grade") = Range("B" & R).Text
        .Fields("Positions") = Range("C" & R).Value
        .Fields("FY_2018_Pay") = Range("D" & R).Value
        .Fields("FY_2018_Nonpay") = Range("E" & R).Value
        .Fields("FY_2019_Pay") = Range("F" & R).Value
        .Fields("FY_2019_Nonpay") = Range("G" & R).Value
        .Fields("FY_2020_Pay") = Range("H" & R).Value
        .Fields("FY_2020_Nonpay") = Range("I" & R).Value
        .Fields("Locality") = Range("J" & R).Text
        .Fields("Office") = Range("K" & R).Text
        .Fields("Request_Category") = Range("L" & R).Text
        ' add more fields if necessary…
        .Update ' stores the new record
    End With
    R = R + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox "Data has been exported into Access."
End Sub

Open in new window

Roy Cox

Are you referring to Custom Tabs in the Ribbon belonging to an excel add-in?

Terry Moore

ASKER

These are normal tabs I created in Excel that I have written VBA code to directly export to Access.

Roy Cox

They look like the same macro to me. They seem to have the same name.

The simplest way would be to simply call the macros in order

Option Explicit

Sub RunMacros()
Call macro1 ''///change name to first macro
Call macro2 ''///change name to second macro
End Sub

Open in new window

Terry Moore

ASKER

How do I change the macros names?

Roy Cox

I'm not sure what you are doing.

Are the two macros in your ribbon tabs that you posted the same macro? They seem to have the same names and will conflict if they are.
Or
Do the ribbon buttons each call the same macro? This is the OnAction part of the xml

Terry Moore

ASKER

I have created command buttons for two excel tabs that directly export/appends data into Access tables once you select the command button in Excel. The problem is that the command button is on the page I am trying to export. I am trying to move the location of the button to another tab in excel that runs both macros at the same time. I am not sure how to proceed.

Roy Cox

Can you attach the workbook for me to look at.

Terry Moore

ASKER

I can't attach the file. It is huge and has sensitive material. I might be able to give you a clean file, but that may take some time for me to change the data
.

Terry Moore

ASKER

I changed the actual data and put dummy Data in the Model. The two Macros are on tab Database Tab and Data Position Tab. I am trying to put a command button on the Mod Cost Calculator titled Export to Access. The two Macros are Module5.ADOFromEXYoucelToAccess and Module6.ADOFromEXYoucelToAccess.
FY-2018-Modular-Cost-Table---Fee-Re.xlsm

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Terry Moore

ASKER

Thanks again for helping me! It works perfectly!

Roy Cox

Author staes that solution works