vba 如何使用VBA中单元格的文件路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21877684/
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
How to use file path from a cell in VBA?
提问by Ale
I'm running a VBA script in order to count number of rows in each file in a selected folder and then to display it in an active Workbook.
我正在运行一个 VBA 脚本，以便计算选定文件夹中每个文件中的行数，然后将其显示在活动工作簿中。
Option Explicit
Sub CountRows()
Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet
Dim strFolder As String, strFile As String
Dim lngNextRow As Long, lngRowCount As Long
Application.ScreenUpdating = False
Set wbDest = ActiveWorkbook
Set wsDest = wbDest.ActiveSheet
strFolder = Dir(Range("C7").Value)
strFile = Dir(strFolder & "*.xlsx")
lngNextRow = 11
Do While Len(strFile) > 0
Set wbSource = Workbooks.Open(Filename:=strFolder & strFile)
Set wsSource = wbSource.Worksheets(1)
lngRowCount = wsSource.UsedRange.Rows.Count
wsDest.Cells(lngNextRow, "F").Value = lngRowCount
wbSource.Close savechanges:=False
lngNextRow = lngNextRow + 1
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
Chooing a folder, I would like to use the directory that is inserted in an active WorkBook cell "C7" instead of writing a directory in a script. I tried to substitute:
选择一个文件夹，我想使用插入到活动工作簿单元格“C7”中的目录，而不是在脚本中编写目录。我试图替代：
strFolder = "C:\Users\user\Desktop\"
with
和
strFolder = Dir(Range("C7").Value)
but it does not work. Maybe someone has any ideas? Thanks!
但它不起作用。也许有人有任何想法？谢谢！
采纳答案by Dmitry Pavliv
This line strFolder = Dir(Range("C7").Value)finds firts file in directory (from C7) and then writes path of this fileinto variable strFolder(say, C:\temp\somefile.txt).
这一行在strFolder = Dir(Range("C7").Value)目录（来自C7）中找到第一个文件，然后将此文件的路径写入变量strFolder（例如，C:\temp\somefile.txt）。
Next line of your code: strFile = Dir(strFolder & "*.xlsx")takes this path and adds *.xlsx. In result you would get strFile = Dir("C:\temp\somefile.txt*.xlsx")and that's wrong.
代码的下一行：strFile = Dir(strFolder & "*.xlsx")采用此路径并添加*.xlsx. 结果你会得到strFile = Dir("C:\temp\somefile.txt*.xlsx")，这是错误的。
So, change this code:
因此，更改此代码：
strFolder = Dir(Range("C7").Value)
strFile = Dir(strFolder & "*.xlsx")
to next one:
到下一个：
strFolder = Range("C7").Value
strFile = Dir(strFolder & "*.xlsx")
Btw, I'd recommend you to specify sheet for Range("C7")like this: wsDest.Range("C7")
顺便说一句，我建议您指定这样的工作表Range("C7")：wsDest.Range("C7")
回答by Pedrumj
Try this
尝试这个
dim strPath as string
strPath = CurDir + "NameofFile.xls"
