运行时错误VBS/VBA [英] Runtime Error VBS/VBA

问题描述

在我尝试添加错误处理之前(这段代码在VBA中的webquery没有拉回任何数据之前)，此代码才能正常工作.现在它仍然可以运行，但是出现以下错误:

This code was working perfectly until I tried to add error handling (for when the webquery in the VBA didn't pull back any data). Now it still runs, but I get the following error:

Script: C:\Test\test.vbs
Line: 8
Char: 1
Error: Cannot access 'Test.xlsm'.
Code: 800A9C68
Source: Microsoft Excel

这是我的VBScript，它实际上只是在.xlsm工作簿中调用我的VBA

This is my VBScript, which essentially just calls my VBA inside of the .xlsm workbook

Set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")

Set myxlApplication = CreateObject("Excel.Application")
myxlApplication.Visible = False
Set myWorkBook = myxlApplication.Workbooks.Open( "C:\Test\Test.xlsm" ) 'Change to the actual workbook that has the Macro
myWorkBook.Application.Run "Module1.Mail_ActiveSheet" 'Change to the Module and Macro that contains your macro
myxlApplication.Quit

以下是我的VBA代码，它刷新了Web查询，重新格式化了一些小的格式化错误，然后将工作表另存为.csv在当前目录中.

The following is my VBA code that refreshes the webquery, re-formats some small formatting errors then saves the sheet as .csv in the current directory.

Private Declare Function GetActiveWindow Lib "user32" () As Long

Sub Mail_ActiveSheet()
    ' Error Handling
    On Error GoTo Errhandler
    ' Refreshes webquery
    Application.Worksheets("Test").Range("A1").QueryTable.Refresh BackgroundQuery:=False

    ' Enters Title Comments in Cell M2
    Range("$M$2").Value = "Notes"
    ' Enters formula in column M
    Range("$M$3").Formula = Range("G3") & (":") & Range("L3")

    Dim Lastrow As Long

    Application.ScreenUpdating = False

    Lastrow = Range("L" & Rows.Count).End(xlUp).Row
    Range("M3:M" & Lastrow).Formula = "=""TT""&G3&"":""&L3"
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

    ' Replaces comma's with periods
    Cells.Replace What:=",", Replacement:=".", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    ' Formats column H as text
    Range("E:E").NumberFormat = "General"
    Range("H:H").NumberFormat = "@"

    ' Fixes formatting adding leading zeros to site codes
    Columns("H").Replace What:="808", LookAt:=xlWhole, Replacement:="'0808", SearchOrder:=xlByColumns
    Columns("H").Replace What:="650", LookAt:=xlWhole, Replacement:="'65E1", SearchOrder:=xlByColumns
    Columns("H").Replace What:="941", LookAt:=xlWhole, Replacement:="'0941", SearchOrder:=xlByColumns
    Columns("H").Replace What:="17", LookAt:=xlWhole, Replacement:="'0017", SearchOrder:=xlByColumns
    Columns("H").Replace What:="168", LookAt:=xlWhole, Replacement:="'0168", SearchOrder:=xlByColumns
    Columns("H").Replace What:="420", LookAt:=xlWhole, Replacement:="'0420", SearchOrder:=xlByColumns
    Columns("H").Replace What:="535", LookAt:=xlWhole, Replacement:="'0535", SearchOrder:=xlByColumns
    Columns("H").Replace What:="560", LookAt:=xlWhole, Replacement:="'0560", SearchOrder:=xlByColumns
    Columns("H").Replace What:="572", LookAt:=xlWhole, Replacement:="'0572", SearchOrder:=xlByColumns
    Columns("H").Replace What:="575", LookAt:=xlWhole, Replacement:="'0575", SearchOrder:=xlByColumns
    Columns("H").Replace What:="750", LookAt:=xlWhole, Replacement:="'0750", SearchOrder:=xlByColumns
    Columns("H").Replace What:="760", LookAt:=xlWhole, Replacement:="'0760", SearchOrder:=xlByColumns
    Columns("H").Replace What:="815", LookAt:=xlWhole, Replacement:="'0815", SearchOrder:=xlByColumns
    Columns("H").Replace What:="822", LookAt:=xlWhole, Replacement:="'0822", SearchOrder:=xlByColumns
    Columns("H").Replace What:="823", LookAt:=xlWhole, Replacement:="'0823", SearchOrder:=xlByColumns
    Columns("H").Replace What:="824", LookAt:=xlWhole, Replacement:="'0824", SearchOrder:=xlByColumns
    Columns("H").Replace What:="886", LookAt:=xlWhole, Replacement:="'0886", SearchOrder:=xlByColumns

Lable1:
    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String

    Dim CurrentWorkbook As String
    Dim CurrentFormat As Long

    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
    ' Store current details for the workbook
    SaveToDirectory = "C:\Test\"
    For Each WS In ThisWorkbook.Worksheets
        Sheets(WS.Name).Copy
        ActiveWorkbook.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", FileFormat:=xlCSV
        ActiveWorkbook.Close savechanges:=False
        ThisWorkbook.Activate
    Next

    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True
    ' Temporarily turn alerts off to prevent the user being prompted
    '  about overwriting the original file.
    End

Errhandler:
    Sheet1.Cells.Clear
    Resume Label1 'Lable1 is placed before the place the workbook is saved
End Sub

我试图做的错误处理是沿着这些思路:

The error handling that I was trying to do, was something along these lines:

'This was placed above the webquery portion of the script
On Error GoTo Errhandler
Errhandler:
    Sheet1.Cells.Clear
    Resume Label1 'Lable1 is placed before the place the workbook is saved

推荐答案

好吧，终于得到了....由于某种原因，如果我在Excel中拆分了VBA代码的一部分，将工作簿保存到一个新的宏中，不再收到错误.

Alright, finally got it.... For some reason, if I split the portion of the VBA code in Excel that saves the workbook to a new macro I no longer get the error.

所以我最终得到了3个宏. Lable1上方的部分，然后是Lable1，以及另一个宏，它们按应该运行的顺序调用这两个宏.

So I ended up with 3 Macros. The portion above Lable1, then Lable1 and another macro that calls both of those macros in the order they are supposed to run.

为进行错误处理，我还缺少Exit Sub命令，以在没有错误时停止执行它.

Also for the error handling, I was missing the Exit Sub command to stop it from executing when there is no error.

感谢所有帮助！

这篇关于运行时错误VBS/VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！