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

问题描述

我有一个电子表格，点击一个按钮将复制/粘贴所有的东西到一个新的工作簿，并保存该文件的名称依赖于一些变量值（从电子表格中的单元格）复制自己。
我目前的目标是根据客户端名称（保存在变量中的单元格值）将其保存在不同的文件夹中，而在第一次运行时，我会收到错误。

代码检查目录是否存在，如果不存在则创建它。
这是有效的，但创建后，再次运行它会抛出错误：

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

我的代码：

  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 =格式$（screeningdate，yyyy\-mm\-dd）
 client =范围（B3）值
 =范围（B23）值
 
 
 Dim SrceFile 
 Dim DestFile 
 
如果Dir（C：\2013收到的计划 ;\& client）=空然后
 MkDirC：\2013收到的时间表& \&客户端
结束如果
 
 SrceFile =C：\2013收到的计划\schedule template.xlsx
 DestFile =C：\2013收到的计划表\ ;客户& \&客户& &现场& & screeningdate_text& .xlsx
 
 
 FileCopy SrceFile，DestFile 
 
范围（A1：I37）。选择
 Selection.Copy 
工作簿。打开文件名：= _ 
C：\2013收到的日程表\&客户& \&客户& &现场& & screeningdate_text& .xlsx，UpdateLinks：= _ 
 0 
范围（A1：I37）PasteSpecial Paste：= xlPasteValues 
范围（C6）选择
应用程序。 CutCopyMode = False 
 ActiveWorkbook.Save 
 ActiveWindow.Close 
 
 End Sub 
  

你将不得不在这方面缺乏知识，我还在学习。
我有一个非常强烈的感觉，它与目录检查逻辑有关，因为当错误被抛出时， MkDir 行被突出显示。

解决方案

要使用目录检查目录的存在，您需要指定 vbDirectory 作为第二个参数，如下所示：

 如果Dir C：\2013收到的时间表&\& client，vbDirectory）=然后
  

请注意，如果指定的路径已存在，则使用 vbDirectory ， Dir 将返回非空字符串作为目录或作为文件（只要文件没有任何只读，隐藏或系统属性）。您可以使用 GetAttr 确定它是一个目录而不是文件。

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:

Runtime Error 75 - path/file access error.

My code:

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 Schedules\schedule 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

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.

解决方案

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

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