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
[VBA]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[/VBA]
I now need the following sub routine to run in that exported excel file (preferrably without opening it) and do all calculations:
CODE
[VBA]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
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
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[/VBA]
How do I accomplish my task? I am really new to automation so not sure how things are done.
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!!