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

D

dyurlova

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!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
Back
Top