使用 VBA 仅抓取文件名/文件路径的一部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15618499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use VBA to Grab Only Part of a Filename/Filepath
提问by ARich
I'm not sure how to tackle this issue. I've done quite a bit of research, but most of the answers I find are a little different than what I need.
我不知道如何解决这个问题。我做了很多研究，但我找到的大多数答案都与我需要的略有不同。
What I'm trying to accomplish is this:
我想要完成的是：
- Open up an existing workbook manually (wbAI),
- Start macro,
- Use msoFileDialogOpen to find and open a file (call this wb2),
- Store partof wb2's file name (there is a date in the file name) as a variable or string. (I'm not sure which is better for this purpose. Maybe I don't need to store it all...),
- Paste part of wb2's filename (the date) in a certain cell in wb1,
- Copy the necessary data from wb2,
- Paste the data in wb1,
- Format the data,
- Use a VLOOKUP on the pasted data,
- Close wb2 without saving
- End the macro.
- 手动打开现有工作簿 (wbAI)，
- 启动宏，
- 使用 msoFileDialogOpen 查找并打开文件（称为 wb2），
- 存储部分WB2的文件名（没有在文件名的日期）作为变量或字符串。（我不确定哪个更适合这个目的。也许我不需要全部存储......），
- 将 wb2 的文件名（日期）的一部分粘贴到 wb1 的某个单元格中，
- 从 wb2 复制必要的数据，
- 将数据粘贴到 wb1 中，
- 格式化数据，
- 对粘贴的数据使用 VLOOKUP，
- 关闭 wb2 而不保存
- 结束宏。
My macro can do every step listed above except for numbers four and five. On one hand, I'm wondering how I need to pursue this, and on the other hand, I'm wondering where this would fit inside my current code.
我的宏可以完成上面列出的每个步骤，除了数字四和五。一方面，我想知道我需要如何追求这一点，另一方面，我想知道这适合我当前的代码。
To give you an example of what I'm talking about: let's say that in step three I open up a workbook that's named "01.31.13 Group Names." And the file path is from a Sharepoint site so it looks like this:
举例说明我在说什么：假设在第三步中，我打开了一个名为“01.31.13 Group Names”的工作簿。文件路径来自 Sharepoint 站点，因此如下所示：
How can I pick out only the date in the filename/filepath?
如何仅选择文件名/文件路径中的日期？
Here's the beginning of my code:
这是我的代码的开头：
Sub Test()
Dim lastRow As Long, i As Long
Dim ws As Worksheet
Dim wbSource As Workbook
Dim wbAI As Workbook
Dim vrtSelectedItem As Variant
Set wbAI = ActiveWorkbook
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = https://company.com/team/teamone & "\"
.AllowMultiSelect = False
.Show
' ****** Is this where the new code could be inserted? *******
For Each vrtSelectedItem In .SelectedItems
Set wbSource = Workbooks.Open(vrtSelectedItem)
Next
End With
' Check if the first cell contains data. If not then close file
If Range("Profile!H9") = "" Then
ActiveWorkbook.Close savechanges:=False
ActiveWorkbook.Saved = False
Any suggestions are welcome! Thank you for your time!
欢迎任何建议！感谢您的时间！
Edit: This is how my code looks after Philip's suggestion:
编辑：这是我的代码在菲利普的建议之后的样子：
Sub Test()
Dim lastRow As Long, i As Long
Dim ws As Worksheet
Dim wbSource As Workbook
Dim wbAI As Workbook
Dim vrtSelectedItem As Variant
Set wbAI = ActiveWorkbook
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = https://company.com/team/teamone & "\"
.AllowMultiSelect = False
.Show
For Each vrtSelectedItem In .SelectedItems
Set wbSource = Workbooks.Open(vrtSelectedItem)
Next
End With
dateVar = Left(wbSource.Name, 8) '<~~~~ New code
' Check if the first cell contains data. If not then close file
If Range("Profile!H9") = "" Then
ActiveWorkbook.Close savechanges:=False
ActiveWorkbook.Saved = False
Else
Sheets("Profile").Activate
Range("H9:I" & Cells(Rows.Count, "H").End(xlUp).Row).Select
Selection.Copy
Windows("wbName").Activate
Sheets("Sheet1").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E1").Value = dateVar '<~~~ New code
回答by Our Man in Bananas
from the filename you would use the LEFTFUNCTION to return the LEFT 8 charsof the date:
从文件名中，您将使用LEFTFUNCTION 返回日期的 LEFT 8 个字符：
dateVar=left(wbSource.name, 8)
then you can put that in your cell:
然后你可以把它放在你的单元格中：
rangeVar.value=dateVar
hope that gets you going
希望能让你继续前进
Philip
菲利普
