Excel中VBA打开的工作簿，执行操作，另存为，关闭 [英] Excel VBA Open workbook, perform actions, save as, close

问题描述

此问题已被编辑，由于从建议的答案很长的注释和更新。

This question has been edited due to lengthy comments and updates from proposed answers.

由于这里是要求模块13;

As requested here is module 13;

Sub SaveInFormat()
Application.DisplayAlerts = False
Workbooks.Application.ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data\" & Format(Date, "yyyymm") & "DB" & ".xlsx",   leFormat:=51
Application.DisplayAlerts = True
End Sub

还有一些与ErrorHandling中的问题，我知道我错了它，但我更感兴趣的是，此刻固定关闭功能之前，我进去。以下是错误处理code，它需要一些工作

Also there are issues with the errorhandling, I know I've gone wrong with it but I'm more interested in fixing the close function at the moment before I get into it. Here is the error handling code that needs some work

Sub test()

Dim wk As String, yr As String, fname As String, fpath As String
Dim owb As Workbook

wk = ComboBox1.Value
yr = ComboBox2.Value
fname = yr & "W" & wk
fpath = "C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data"
owb = Application.Workbooks.Open(fpath & "\" & fname)
On Error GoTo ErrorHandler:
ErrorHandler:
If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel Then Exit Sub Else Call Clear

'Do Some Stuff

Call Module13.SaveInFormat

owb.Close

这是你的测试code加上我的文件路径和名称

this is your test code plus my changing of the file path and name

推荐答案

讨论帖子更新的答案后：

After discussion posting updated answer:

Option Explicit
Sub test()

    Dim wk As String, yr As String
    Dim fname As String, fpath As String
    Dim owb As Workbook

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    wk = ComboBox1.Value
    yr = ComboBox2.Value
    fname = yr & "W" & wk
    fpath = "C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data"

    On Error GoTo ErrorHandler
    Set owb = Application.Workbooks.Open(fpath & "\" & fname)

    'Do Some Stuff

    With owb
        .SaveAs fpath & Format(Date, "yyyymm") & "DB" & ".xlsx", 51
        .Close
    End With

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
    End With

Exit Sub
ErrorHandler: If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel Then

Else: Call Clear

End Sub

错误处理：

您可以尝试这样的事情捕捉特定的错误：

You could try something like this to catch a specific error:

    On Error Resume Next
    Set owb = Application.Workbooks.Open(fpath & "\" & fname)
    If Err.Number = 1004 Then
    GoTo FileNotFound
    Else
    End If

    ...
    Exit Sub
    FileNotFound: If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel Then

    Else: Call Clear

这篇关于Excel中VBA打开的工作簿，执行操作，另存为，关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！