Speed issue when controlling Excel from Access VBA

Question

I have a very hard time figuring out how to proceed. I built an Access DataBase which I use to process data in order to build several weekly and monthly reports. I wrote VBA codes in Excel and in Access.

Basically, for now, Access VBA processes data, exports tables on temporary workbooks then runs macros on Excel to compile everything and make nice presentations.

Having to carry over two files (one Access, one Excel, each with its own VBA codes) does not bother me at all, but the end users being my colleagues, and them having to be able to debug those when I am not here, I am starting to consider merging my codes and controlling Excel directly from Access VBA. But when I tried to do so, everything became really slow!

Just so you get what I mean. Here is some useless code to illustrate what happens (the real code being too long).

First, the version in which Access VBA tells Excel to run VBA.

Access VBA:

Option Explicit
Sub SpeedTest_Access_And_Excel()

'Starting time
Dim startTime As Double
startTime = Round(timer)


'Variables
Dim xl As Excel.Application 'Excel
Dim xlBot As Workbook 'The workbook with VBA code to run


'Run Excel
Set xl = New Excel.Application


With xl
    Set xlBot = xl.Workbooks.Open(CurrentProject.Path & "\SpeedTest.xlsm") 'Open Workbook
    .Run "SpeedTest_In_Excel" 'Run Excel code
End With

xlBot.Close False 'Close Excel workbook



'End message
MsgBox (Round(timer) - startTime) \ 60 & "'" & Format((Round(timer) - startTime) Mod 60, "00") & "''"

'Quit Excel
xl.Quit
Set xl = Nothing

End Sub

Excel VBA (in a "SpeedTest.xlsm" file in the same folder as the Access DB):

Option Explicit
Sub SpeedTest_In_Excel()

    'Variables
    Dim wb As Workbook 'A new workbook
    Dim ws As Worksheet 'That new workbook's sheet
    Dim i As Integer, j As Integer 'Lines and columns

    'Excel optimisation
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    'Create new workbook
    Set wb = Workbooks.Add

    'Disable auto-calculation
    Application.Calculation = xlCalculationManual

    'Write useless stuff
    Set ws = wb.Sheets(1)
    i = 1
    Do Until i = 500
        j = 1
        Do Until j = 500
            ws.Cells(i, j) = "Useless stuff"
            j = j + 1
        Loop
        i = i + 1
    Loop

    'Close workbook
    wb.Close False

End Sub

Now the Access only version, that does exactly the same thing:

Sub SpeedTest_Access_Only()

'Starting time
Dim startTime As Double
startTime = Round(timer)


'Variables
Dim xl As Excel.Application
Dim wb As Workbook 'A new workbook
Dim ws As Worksheet 'That new workbook's sheet
Dim i As Integer, j As Integer 'Lines and columns

'Run Excel
Set xl = New Excel.Application



With xl
    'Excel optimisation
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False

    'Create New Workbook
    Set wb = .Workbooks.Add

    'Disable auto-calculation
    .Calculation = xlCalculationManual

End With



'Write useless stuff
Set ws = wb.Sheets(1)
i = 1
Do Until i = 500
    j = 1
    Do Until j = 500
        ws.Cells(i, j) = "Useless stuff"
        j = j + 1
    Loop
    i = i + 1
Loop


'Close workbook
wb.Close False


'End message
MsgBox (Round(timer) - startTime) \ 60 & "'" & Format((Round(timer) - startTime) Mod 60, "00") & "''"

'Quit Excel
xl.Quit
Set xl = Nothing

End Sub

My issue :

• The 1st version (Access + Excel) takes 0 minutes and 8 seconds
• The 2nd version (Access only) takes 1 minute and 25 seconds at best (up to 2 minutes and 20 seconds if I do something else at the same time)

Does anybody know anything I could do to speed this up? Is that even possible? Or should I keep my Access+Excel configuration that works quite well beside being a little more complex (and not enabling me to use class variables)?