如何防止Excel中的窗口闪烁/切换? [英] How to prevent window flickering/switching in Excel?

问题描述

我正在从一个文件复制到另一个文件，并且可以看到Excel在源文件和目标文件之间切换(例如闪烁).我希望宏可以从源复制并粘贴到目标，而无需在文件之间切换(我不想闪烁).

I am copying from one file to an other file, and I can see Excel switching between the source and the target file (like flickering). I want the macro to copy from the source and paste to target WITHOUT switching between the files (I DON'T want flickering).

所以在这里，我得到了我的Excel VBA代码.我设置了一个按钮来运行2个宏.第一个宏是openfile对话框，我可以在其中选择要打开的文件.这是必需的，因为所需的文件始终具有不同的名称，并且位于不同的路径中.

So here I got my Excel VBA code. I set up a Button to run 2 macros. The first macro is an openfiledialog where I can choose a file to be opened. That is required because the needed file always got a different name and is in a different path.

打开文件后，我的第二个宏开始(CopyPasteValues)，这是我在下面发布的那个宏.首先，我设置了源工作簿和目标工作簿/工作表，并创建了一个包含16个帐号的数组.

After opening the file my second macro starts (CopyPasteValues), the one I posted below. At first I set my source and target Workbooks/Worksheets and create an array with 16 account numbers.

然后，我使用find方法在每个文件(源和目标)中搜索帐号. find方法的结果用于在src文件中创建偏移并将其复制到目标文件中的偏移.

Then I use the find method to search for the account number in each file (source and target). The result of the find method is used to create an offset in the src file and copy it to an offset in the target file.

Sub CopyPasteValues()
    Dim srcWb As Workbook    'source Wb
    Dim srcWs As Worksheet   'source Ws
    Dim trgWb As Workbook    'target Wb
    Dim trgWs As Worksheet   'target Ws

    Set trgWb = ActiveWorkbook
    Set trgWs = trgWb.Sheets("Entry Sheet 20004100")
    Set srcWb = Workbooks.Open(Filename:=openedFile, UpdateLinks:=False, ReadOnly:=True, Editable:=False)
    Set srcWs = srcWb.Sheets("20004100") 

    Dim GLAccountField
    'Array of 16 Account numbers
    GLAccountField = Array(430000, 446030, 477030, 474210, 446075, 472700, 472710, 476000, 476100, 476610, 452200, 454700, 471300, 473110, 490000, 490710)

    Dim srcFinder As Range, trgFinder As Range
    Dim searchGL As Long
    Dim srcRng As Range, trgRng As Range
    Dim i As Integer

    For i = LBound(GLAccountField) To UBound(GLAccountField)

        'The range where GL Accounts will be searched
        Set srcRng = srcWs.Range("A1:A100")    'source file
        Set trgRng = trgWs.Range("B10:B900")   'target file

        'search for the account number(i) in source and target sheets
        searchGL = GLAccountField(i) 
        Set srcFinder = srcRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
        Set trgFinder = trgRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)


        'If finder value equals searched Account Number, then paste to target
        If srcFinder Is Nothing Then
            MsgBox "GL Account: " & searchGL & " NOT found in 'Accounting Input' file"
        Else
            'copy from source
            srcFinder.Offset(0, 15).Resize(1, 12).Copy
            'paste to target from source
            trgFinder.Offset(1, 4).Resize(1, 12).PasteSpecial xlPasteValues  

        End If
    Next i
    srcWb.Close
End Sub

推荐答案

(在Andy G的评论中答复):

(Answered in comments by Andy G):

在子标题的开头使用Application.ScreenUpdating = False.请记住，最后将其设置回True(在错误处理程序中执行此操作也是一种好习惯，这样即使发生错误也可以将其重置):

Use Application.ScreenUpdating = False at the start of your sub. Remember to set it back to True at the end (also good practice to do this in an error handler so it gets reset even in the event of an error):

Sub foo()
  On Error Goto errHandler
  Application.ScreenUpdating = False

  'Your code here

  Application.ScreenUpdating = True
errHandler:
  Application.ScreenUpdating = True
End Sub

这篇关于如何防止Excel中的窗口闪烁/切换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！