Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Newbie
    Joined
    Aug 2011
    Posts
    1
    Location

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

    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!!!!!!!!!!!!!!!!!!!!!!!!!!!
    Last edited by Bob Phillips; 09-06-2011 at 01:23 AM. Reason: Added VBA tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You will have to start up Excel to run code in an Excel workbook, VBA is hosted.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie mallp's Avatar
    Joined
    Sep 2011
    Posts
    1
    Location

    Thumbs up Solution: How To Run Sub In Exported From Access To Excel Table?

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •