Excel VBA 检查目录是否存在错误 [英] Excel VBA Check if directory exists error

问题描述

我有一个电子表格，单击按钮后，它会通过将所有内容复制/粘贴到新工作簿来复制自身，并使用依赖于某些变量值(取自电子表格中的单元格)的名称保存文件.我目前的目标是让它根据客户端名称的名称(保存在变量中的单元格值)将工作表保存在不同的文件夹中，虽然这在第一次运行时有效，但之后出现错误.

I have a spreadsheet that upon clicking a button will duplicate itself by copying/pasting everything to a new workbook and save the file with a name that is dependent upon some variable values (taken from cells on the spreadsheet). My current goal is to get it to save the sheet in different folders depending on the name of client name (cell value held in variable), while this works on the first run, I get an error after.

代码检查目录是否存在，如果不存在则创建它.这有效，但在创建后，再次运行它会引发错误:

The code checks if the directory exists and creates it if not. This works, but after it is created, running it a second time throws the error:

运行时错误 75 - 路径/文件访问错误.

Runtime Error 75 - path/file access error.

我的代码:

Sub Pastefile()

Dim client As String
Dim site As String
Dim screeningdate As Date
screeningdate = Range("b7").Value
Dim screeningdate_text As String
screeningdate_text = Format$(screeningdate, "yyyy-mm-dd")
client = Range("B3").Value
site = Range("B23").Value

Dim SrceFile
Dim DestFile

If Dir("C:2013 Recieved Schedules" & "" & client) = Empty Then
    MkDir "C:2013 Recieved Schedules" & "" & client
End If

SrceFile = "C:2013 Recieved Schedulesschedule template.xlsx"
DestFile = "C:2013 Recieved Schedules" & client & "" & client & " " & site & " " & screeningdate_text & ".xlsx"

FileCopy SrceFile, DestFile

Range("A1:I37").Select
Selection.Copy
Workbooks.Open Filename:= _
    "C:2013 Recieved Schedules" & client & "" & client & " " & site & " " & screeningdate_text & ".xlsx", UpdateLinks:= _
    0
Range("A1:I37").PasteSpecial Paste:=xlPasteValues
Range("C6").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

你必须原谅我在这方面缺乏知识，我还在学习.我有一种非常强烈的感觉，它与目录检查逻辑有关，因为当抛出错误时，MkDir 行会突出显示.

You'll have to excuse my lack of knowledge in this area, I am still learning. I have a very strong feeling it has something to do with the directory checking logic, as when the error is thrown the MkDir line is highlighted.

推荐答案

要使用 Dir 检查目录是否存在，您需要指定 vbDirectory 作为第二个参数，例如:

To check for the existence of a directory using Dir, you need to specify vbDirectory as the second argument, as in something like:

If Dir("C:2013 Recieved Schedules" & "" & client, vbDirectory) = "" Then

注意，对于vbDirectory，如果指定的路径已经作为目录或文件Dir将返回一个非空字符串>(前提是文件没有任何只读、隐藏或系统属性).您可以使用 GetAttr 来确定它是一个目录而不是一个文件.

Note that, with vbDirectory, Dir will return a non-empty string if the specified path already exists as a directory or as a file (provided the file doesn't have any of the read-only, hidden, or system attributes). You could use GetAttr to be certain it's a directory and not a file.

这篇关于Excel VBA 检查目录是否存在错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！