excel - VBA - 加速 Access 发送的 Excel 指令

Question

为了构建报告，我使用 Access 计算一些数据，然后使用 VBA 宏导出多个表，然后该宏启动 Excel 并从 Excel 中运行另一个宏来编译所有内容。

一切都已经相当快了，但是除了 Access VBA 之外还需要一个包含 VBA 代码的 Excel 工作表，这让我很困扰。此外，我最近向我的数据库添加了许多新功能，可以选择生成多个报告，并在 Access Forms 上提供大量自定义功能。

对我来说，在这两个文件之间导航变得有些复杂，因此我尝试将 Excel 代码放入 Access 中，这样我的同事(最终用户)只需保持 1 个文件的最新状态即可计算机，并且它也会使调试变得更容易。

报告按预期构建，但过程慢了 5 倍。我尝试测量每个步骤所花费的时间，比率保持不变(除了最后构建 PowerPoint 演示文稿的部分)。

所以我决定尝试这段无用的代码:

Sub test()

Dim t As Double 'Starting time
t = Round(Timer)

Dim b As Workbook
Dim s As Worksheet
Dim i As Integer, j As Integer 'Loop variables


'Create workbook
Set b = Workbooks.Add
'Get worksheet
Set s = b.Sheets(1)

'Double Loop
For i = 1 To 100
    For j = 1 To 100
        s.Cells(i, j) = "Hi!" 'Write some useless comment
    Next
Next

b.Close False 'Close without saving

'Message
MsgBox (Round(Timer) - t) \ 60 & "'" & Format((Round(Timer) - t) Mod 60, "00") & "''"

End Sub

我没有故意设置任何 ScreenUpdating=False、EnableEvents=False 等。

从 Excel 运行代码时，需要 2 到 3 秒。

从 Access 运行代码时，需要 7 到 10 秒!

据我所知，Access 必须向另一个应用程序发送指令，这可能会减慢该过程。因此，我尝试要求 Excel 在 Excel 的另一个实例中运行我的脚本(因此我创建了一个对象“Excel.Application”，将我的工作簿放入其中，这正是 Access 在后台执行的操作)，并且我获得了与我相同的性能从 Access 运行代码。

有什么办法可以加快速度吗？更改 VB 脚本与其创建的应用程序对象通信方式的指令可能？

预先感谢大家的支持。

编辑:根据 ASH 的要求，这是我在 Access 中运行的完整代码(我不会放置我的报告代码，因为它非常庞大，有很多子、函数、自定义类等)。评论是法语，但这里有一个摘要:

• 第一个脚本是创建或打开 Excel 的函数工作簿，进行了一些优化(Excel 不可见，无屏幕更新、不计算等)

• 第二个脚本是关闭 Excel 工作簿的子脚本，其中保存/另存为以及放弃优化的选项第一个函数

• 第三个脚本是我之前放置的测试代码的替代版本。

现在来看看代码本身:

Option Explicit

Function Ouvrir_Classeur_Excel(Optional Fichier As String, Optional Optimiser As Boolean = False) As Workbook

'*********************************** Descriptif
'Ouvre (ou crée) un classeur Excel en appliquant une optimisation si demandé par l'utilisateur.
'L'optimisation cache et désactive le rafraîchissement visuel d'Excel, les messages d'alerte, les événements et les calculs automatiques.

'------------------ Paramètres
'Fichier : Le chemin du classeur à ouvrir. S'il est vide, un classeur est créé
'Optimiser : Indique si les scripts d'optimisation doivent être exécutés. Par défaut, cette option est désactivée



'*********************************** Exécution
With Excel.Application


    If Optimiser Then
        'Excel invisible
        .Visible = False
        'Désactivation du rafraîchissement
        .ScreenUpdating = False
        'Désactivation des messages d'alerte
        .DisplayAlerts = False
        'Désactivation des événements
        .EnableEvents = False
    End If

    'Ouverture/Création du classeur
    If Fichier <> "" Then Set Ouvrir_Classeur_Excel = Workbooks.Open(Fichier) 'Si on a spécifié un fichier, il est ouvert
    If Fichier = "" Then Set Ouvrir_Classeur_Excel = Workbooks.Add 'Si on n'a pas spécifié de fichier, on en crée un

    'Désactivation des calculs automatiques
    If Optimiser Then .Calculation = xlCalculationManual

End With

End Function




Sub Fermer_Classeur_Excel(Classeur As Workbook, Optional Enregistrer As Boolean = False, _
    Optional Emplacement As String, Optional Fin_Optimisation As Boolean = False)

'*********************************** Descriptif
'Ferme le classeur Excel spécifié.
'Si l'utilisateur le demande, le classeur peut être enregistré, ou enregistré sous.
'Si l'utilisateur le demande, les optimisations appliquées par la fonction Ouvrir_Classeur_Excel peuvent être annulées.

'------------------ Paramètres
'Classeur : Le classeur à fermer
'Enregistrer : Indique si le classeur doit être enregistré. Par défaut, cette option est désactivée
'Emplacement : Indique l'emplacement où enregistrer le classeur. Si vide, l'enregistrement sera simple.
'Fin_Optimisation : Indique si les optimisations doivent être annulées. Par défaut, cette option est désactivée



'*********************************** Exécution
With Excel.Application

    'Enregistrement du classeur
    If Enregistrer Then
        If Emplacement = "" Then Classeur.Save
        If Emplacement <> "" Then Classeur.SaveAs Emplacement
    End If

    'Réactivation des calculs automatiques
    If Fin_Optimisation Then .Calculation = xlCalculationAutomatic

    'Fermeture du classeur
    Classeur.Close False


    If Fin_Optimisation Then
        'Réactivation du rafraîchissement
        .ScreenUpdating = True
        'Réactivation des messages d'alerte
        .DisplayAlerts = True
        'Réactivation des événements
        .EnableEvents = False
    End If

End With

End Sub




Sub testA()

'------------Relève de l'heure de début
Dim tGlo As Double 'Heure de début d'exécution du script
Dim infoFin As String
tGlo = Round(Timer)

Dim x As Excel.Application
Dim c As Workbook
Dim f As Worksheet
Dim i As Integer, j As Integer

Set c = Ouvrir_Classeur_Excel(, True)

Set f = c.Sheets(1)
i = 1
Do Until i = 100
    j = 1
    Do Until j = 100
        f.Cells(i, j) = "Coucou"
        j = j + 1
    Loop
    i = i + 1
Loop

Fermer_Classeur_Excel c, , , True


'------------Message de fin
infoFin = infoFin & Chr(10) & Chr(10) & "DUREE DE TRAITEMENT : " & (Round(Timer) - tGlo) \ 60 & "'" & Format((Round(Timer) - tGlo) Mod 60, "00") & "''"
MsgBox infoFin, , title:="** FIN DU TRAITEMENT **"


End Sub

像这样，需要 2 到 7 秒(Excel 已经运行)，与具有相同优化的 Excel 中不到 1 秒相比，这是巨大的。

问题是，它在这里似乎并不那么重要，但在 Excel 中通常需要 40 秒的另一个过程，当我将其迁移到 Access 时花了 3 分钟多。我还有另一个(尚未优化)在 Excel 中需要超过 3:30，我什至不敢尝试在 Access 中运行它。

Answer 1

我知道您提到您在 Access 中有很多自定义过程来生成各种报告，但是有什么原因您不能将该代码移至 Excel 吗？我已经构建了几个在 Excel 中运行代码但使用 Access 作为数据源的解决方案，并且它们非常快 - 您在 Access 中构建的用于自定义报告的用户表单必须在 Excel 中重新构建，但可以解决 Excel 之间的性能问题/Access ...

希望这有帮助，丝绸密码