How To Run Sub In Exported From Access To Excel Table?


Joined
Aug 25, 2011
Messages
1
Reaction score
0
Hi,

I have a rather basic question about Access automation and hope that someone experienced will help me - will be grateful tons!!!!!!!!

I have this automation code, which exports the table from my database into Excel file:

Code:
Private Sub cmdCalculate_Click()
 
    Dim appAccess As Access.Application
    Dim strDatabase As String
 
    strDatabase = CurrentProject.Path & "\IRMDb.accdb"
 
    'Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
 
    'Open database in Access window.
    appAccess.OpenCurrentDatabase strDatabase
 
    'Export table to Excel
    appAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblBarriersToEntryExit", CurrentProject.Path & "\tblBarriersToEntry.xls", True
 
    appAccess.CloseCurrentDatabase
    Set appAccess = Nothing
 
    Exit Sub
 
End Sub


I now need the following sub routine to run in that exported excel file (preferrably without opening it) and do all calculations:

Code:
Sub LaborDependence()
 
Range("G1").Select
ActiveCell.FormulaR1C1 = "Value "
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G155"), Type:=xlFillDefault
Range("G2:G155").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "Score"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]>0,RC[-1]<=0.1),0.5,IF(AND(RC[-1]>0.1,RC[-1]<=0.13),1,IF(AND(RC[-1]>0.13,RC[-1]<=0.16),1.5,IF(AND(RC[-1]>0.16,RC[-1]<=0.201),2,IF(AND(RC[-1]>0.201,RC[-1]<=0.228),2.5,IF(AND(RC[-1]>0.228,RC[-1]<=0.248),3,IF(AND(RC[-1]>0.248,RC[-1]<=0.278),3.5,IF(AND(RC[-1]>0.278,RC[-1]<=0.325),4,IF(AND(RC[-1]>0.325,RC[-1]<=0.39),4.5,IF(AND(RC[-1]>0.39,RC[-1]<=1),5,""n/a""))))))))))"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H155")
Range("H2:H155").Select
End Sub

How do I accomplish my task? I am really new to automation so not sure how things are done.

Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify range in VBA 9
"=TEXT(RC[-1],"MMM")" 4
Control Excel from Access 2
Control Excel from Access 4
R1:C1 format 2
Running Excel Code from Access 9
Running an Excel Macro from Access 6
Macros--user choices 2

Top