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

Hi,

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

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

You will have to start up Excel to run code in an Excel workbook, VBA is hosted.

Afert you Export the tabel to Excel ....
Run the pFmtExcelSheet(dbXLInput) function ...it should work.

*******
Function pFmtExcelSheet(dbXLInput As String)

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlapp = CreateObject("Excel.Application")
Set xlbook = GetObject(dbXLInput)
Set xlsheet = xlbook.Worksheets(1)

xlbook.Application.Visible = False ' Do not Open Excel Application
xlbook.Windows(1).Visible = True ' Opens the Excel Filename (Unhides)

Do your stuff here.......as you listed it.

End Function