Excel VBA 打开工作簿、执行操作、另存为、关闭

Excel VBA Open workbook, perform actions, save as, close

提问by JamesDev

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

由于冗长的评论和建议答案的更新，此问题已被编辑。

As requested here is module 13;

这里要求的是模块 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

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

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

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

采纳答案by Alistair Weir

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

Error Handling:

错误处理：

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

回答by Mike Kellogg

I'll try and answer several different things, however my contribution may not cover all of your questions. Maybe several of us can take different chunks out of this. However, this info should be helpful for you. Here we go..

我会尝试回答几个不同的问题，但是我的贡献可能无法涵盖您的所有问题。也许我们中的几个人可以从中取出不同的部分。但是，此信息应该对您有所帮助。开始了..

Opening A Seperate File:

打开一个单独的文件：

ChDir "[Path here]"                          'get into the right folder here
Workbooks.Open Filename:= "[Path here]"      'include the filename in this path

'copy data into current workbook or whatever you want here

ActiveWindow.Close                          'closes out the file

---

---

Opening A File With Specified Date If It Exists:

打开具有指定日期的文件（如果存在）：

I'm not sure how to search your directory to see if a file exists, but in my case I wouldn't bother to search for it, I'd just try to open it and put in some error checking so that if it doesn't exist then display this message or do xyz.

我不确定如何搜索您的目录以查看文件是否存在，但在我的情况下，我不会费心搜索它，我只是尝试打开它并进行一些错误检查，以便如果它不存在't 存在然后显示此消息或执行 xyz。

Some common error checking statements:

一些常见的错误检查语句：

On Error Resume Next   'if error occurs continues on to the next line (ignores it)

ChDir "[Path here]"                         
Workbooks.Open Filename:= "[Path here]"      'try to open file here

Or (better option):

或者（更好的选择）：

if one doesn't exist then bring up either a message box or dialogue box to say "the file does not exist, would you like to create a new one?

如果不存在，则弹出一个消息框或对话框，说“该文件不存在，您想创建一个新的吗？

you would most likely want to use the GoTo ErrorHandlershown below to achieve this

您很可能希望使用GoTo ErrorHandler下面显示的方法来实现此目的

On Error GoTo ErrorHandler:

ChDir "[Path here]"                         
Workbooks.Open Filename:= "[Path here]"      'try to open file here

ErrorHandler:
'Display error message or any code you want to run on error here

---

---

Much more info on Error handling here: http://www.cpearson.com/excel/errorhandling.htm

关于错误处理的更多信息：http: //www.cpearson.com/excel/errorhandling.htm

---

---

Also if you want to learn more or need to know more generally in VBA I would recommend Siddharth Rout's site, he has lots of tutorials and example code here: http://www.siddharthrout.com/vb-dot-net-and-excel/

此外，如果您想了解更多或需要更广泛地了解 VBA，我会推荐 Siddharth Rout 的网站，他在这里有很多教程和示例代码：http: //www.siddharthrout.com/vb-dot-net-and-优秀/

Hope this helps!

希望这可以帮助！

---

---

Example on how to ensure error code doesn't run EVERYtime:

关于如何确保错误代码不会每次都运行的示例：

if you debug through the code without the Exit SubBEFORE the error handler you'll soon realize the error handler will be run everytime regarldess of if there is an error or not. The link below the code example shows a previous answer to this question.

如果您在没有Exit SubBEFORE 错误处理程序的情况下调试代码，您很快就会意识到错误处理程序每​​次都会运行，无论是否有错误。代码示例下方的链接显示了此问题的先前答案。

  Sub Macro

    On Error GoTo ErrorHandler:

    ChDir "[Path here]"                         
    Workbooks.Open Filename:= "[Path here]"      'try to open file here

    Exit Sub      'Code will exit BEFORE ErrorHandler if everything goes smoothly
                  'Otherwise, on error, ErrorHandler will be run

    ErrorHandler:
    'Display error message or any code you want to run on error here

  End Sub

Also, look at this other question in you need more reference to how this works: goto block not working VBA

另外，看看另一个问题，你需要更多参考它是如何工作的： goto block not working VBA

---

---