VBA Excel代码列出包含历史记录，位置等的文件夹... [英] VBA Excel code to list folders with a history, location, etc...

问题描述

因此，对于我的工作，我正在尝试简化我们在其中工作的文件夹目录，并在其状态文件夹旁边添加注释，例如正在进行中准备QA等等。



我已经查看了所有可能并设法找到使用功能的代码

 = foldercount（x：\ ReadyForQAEvDataAllTagged）

在cmd窗口中使用

 net use x :(文件路径在这里）

使用此宏

函数FolderCount（dirpath As String，可选namematch As String）As Long 
 Application.Volatile 
 Dim fso As Object，mydir As Object，tempdir As Object，i As Long 
 
 Set fso = CreateObject（Scripting.FileSystemObject）
设置mydir = fso.GetFolder（ dirpath）
 
 For each tempdir in mydir.SubFolders 
 If IsMissing（namematch）Then 
i = i + 1 
 Else 
 If InStr（tempdir.Name ，namematch）> 0然后i = i + 1 
结束如果
下一个
 
设置mydir = Nothing 
设置fso = Nothing 
 FolderCount = i 
结束功能

至少要有文件目录，以便刷新Excel工作表中的文件夹数量。我可以刷新文件夹计数的唯一方法是一次按Ctrl + Shift + Alt + F9键来刷新功能，这没关系。我可以使用。



但是，我需要一个目录，而不仅仅是计数。我在VBA代码中需要以下内容：



- 指定路径中的文件夹目录（x：*文件夹路径在这里*）

- 文件夹名称 - 没有文件。我不需要文件和他们的名字。 WAAAAY对我所做的太多了。 JUST FOLDERS。

- 上次修改或访问（无关紧要 - 这也是可选的）

- 我们放置文件夹状态的部分，例如; 正在进行中，准备好QA等。无论是下拉设置还是手动输入，我都不在乎。

- 自动刷新（首选），或使多键按键组合使其以某种方式刷新。这是非常重要的！ （在像这样的共享excel文档中，我知道保存它会使它更新，如果需要的话也很好。）



有没有人有技能或知识写下前面提到的宏/ VBA，以便它适合我的使用？非常感谢所有的帮助。

解决方案

使用 Dir功能 [ ^ ]或 API [ ^ ]用于枚举目录。

看看这里：设置文件的创建，上次访问和上次修改时间 [ ^ ]确定上次访问该目录。



但我最喜欢的是用于管理文件夹的WMI脚本 [ ^ ]



奖金：其他Visual Basic 5.0& 6.0样本 [ ^ ]

So for my job, I am trying to simplify a directory of folders we work within, with notes next to the folders of their status like "In progress" Ready for QA, etcetera.

I have looked through EVERYTHING possible and managed to find a code that used a function

=foldercount("x:\ReadyForQAEvDataAllTagged")

by utilizing in the cmd window

net use x: (file path here)

With this macro

Function FolderCount(dirpath As String, Optional namematch As String) As Long
Application.Volatile
Dim fso As Object, mydir As Object, tempdir As Object, i As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set mydir = fso.GetFolder(dirpath)

For Each tempdir In mydir.SubFolders
    If IsMissing(namematch) Then
        i = i + 1
    Else
        If InStr(tempdir.Name, namematch) > 0 Then i = i + 1
    End If
Next

Set mydir = Nothing
Set fso = Nothing
FolderCount = i
End Function

to at least have the file directory there to make it refresh the folder count within an excel sheet. The only way I can refresh the folder count is the Ctrl + Shift + Alt + F9 keys all at once to refresh the function, which is okay. That I can work with.

However, I need a directory, not just the count. I need the following in a VBA code:

- Folder directory in the specified path (x: *folder path here*)
- Folder names - NO FILES. I don't need the files and their names. WAAAAY too many for what I do. JUST FOLDERS.
- Last modified or accessed (doesn't matter - this is also optional)
- Section for us to put the status of the folder, e.g.; "in progress", "ready for QA", etc. Whether that be a drop down setting or manual input, I don't care.
- Automatically refreshing (preferred), or made so the multi-key press combo makes it refresh somehow. This is very important! (On a shared excel document like this one, I know saving it makes it update which is fine also if need be.)

Does anyone have the skill or knowledge to write up forementioned macro/VBA for it to work for my usage? All assistance is greatly appreciated.

解决方案

Use Dir function[^] or API[^] functions to enumerate directories.
Have a look here: Set a file's creation, last access, and last modified times[^] to determine last access to the directory.

But my favorite is WMI Scripts to manage Folders[^]

Bonus: Additional Visual Basic 5.0 & 6.0 Samples[^]

这篇关于VBA Excel代码列出包含历史记录，位置等的文件夹...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！