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 SettingsjammilDesktopAutoFinanceProjectControlData" & Format(Date, "yyyymm") & "DB" & ".xlsx",   leFormat:=51
Application.DisplayAlerts = True
End Sub

还有错误处理方面的问题，我知道我出错了，但我更感兴趣的是在我开始之前修复关闭函数.这是需要一些工作的错误处理代码

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 SettingsjammilDesktopAutoFinanceProjectControlData"
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

这是您的测试代码加上我对文件路径和名称的更改

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 SettingsjammilDesktopAutoFinanceProjectControlData"

    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屋！