Excel VBA使用Workbook.Open，结果为Dir(Directory) [英] Excel VBA using Workbook.Open with results of Dir(Directory)

问题描述

这看起来很简单，我已经使它工作了好多次，但是在Dir调用(循环访问目录)和打开当前文件之间一直存在中断.以下是相关代码:

This seems so simple and I've had it working multiple times, but something keeps breaking between my Dir call (to iterate through a directory) and opening the current file. Here's the pertinent code:

SourceLoc = "C:\ExcelWIP\TestSource\"
SourceCurrentFile = Dir(SourceLoc)

'Start looping through directory
While (SourceCurrentFile <> "")
Application.Workbooks.Open (SourceCurrentFile)

我得到的是一个文件访问错误，因为Application.Workbooks.Open试图打开"C:\ ExcelWIP \ TestSource \\ FILENAME"(请注意额外的斜杠)

What I get with this is a file access error as the Application.Workbooks.Open is trying to open "C:\ExcelWIP\TestSource\\FILENAME" (note extra slash)

但是，当我从SourceLoc中删除最后一个斜杠时，Dir(SourceLoc)的结果为"(它不搜索目录).

However when I take the final slash out of SourceLoc, the results of Dir(SourceLoc) are "" (it doesn't search the directory).

令人沮丧的是，当我以其他方式编辑sub时，这段代码的功能来了又去了.我已经按原样工作，并且我已经从目录路径中删除了'/'使其正常工作，此刻，我只是无法使它们一起正常工作.

The frustrating thing is that as I've edited the sub in other ways, the functionality of this code has come and gone. I've had it work as-is, and I've had taking the '/' out of the directory path make it work, and at the moment, I just can't get these to work right together.

我已经搜索了联机帮助和ms文章，但是似乎没有任何迹象表明为什么这种情况会不断上升和下降(除非它停止工作，否则不进行编辑)，以及为什么目录路径的格式有时会起作用的原因带最后的"/"，有时不带.

I've scoured online help and ms articles but nothing seems to point to a reason why this would keep going up and down (without being edited except for when it stops working) and why the format of the directory path will sometimes work with the final '/' and sometimes without.

有什么想法吗?

推荐答案

这将打开该目录子目录中的所有.xlxs文件.

This would open all .xlxs files in that directory son.

    Sub OpenFiles()
    Dim SourceCurrentFile As String
    Dim FileExtension as String: FileExtension = "*.xlxs"
    SourceLoc = "C:\ExcelWIP\TestSource\"
    SourceCurrentFile = Dir(SourceLoc)
    SourceCurrentFile = Dir()
    'Start looping through directory
    Do While (SourceCurrentFile <> "")
    Application.Workbooks.Open (SourceLoc &"\"& SourceCurrentFile)
    SourceCurrentFile = Dir(FileExtension)
    Loop
    End Sub

这篇关于Excel VBA使用Workbook.Open，结果为Dir(Directory)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！