如何在VBA中使用单元格中的文件路径？ [英] How to use file path from a cell in VBA?

问题描述

我正在运行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 
 
设置wbDest = ActiveWorkbook 
设置wsDest = wbDest。 ActiveSheet 
 
 strFolder = Dir（Range（C7）。Value）
 strFile = Dir（strFolder&* .xlsx）
 lngNextRow = 11 
 Do While Len（strFile）> 0 
设置wbSource = Workbooks.Open（文件名：= strFolder& strFile）
设置wsSource = wbSource.Worksheets（1）
 lngRowCount = wsSource.UsedRange.Rows.Count 
 wsDest.Cells（lngNextRow，F）。Value = lngRowCount 
 wbSource.Close savechanges：= False 
 lngNextRow = lngNextRow + 1 
 strFile = Dir 
循环
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

选择一个文件夹，我想使用插入到活动的WorkBook单元C7中的目录，而不是在脚本中写入一个目录。
我试图替代：

  strFolder =C：\Users\user\Desktop\ 
  

与

  strFolder = Dir（Range（C7）。Value）
  

但它不工作。也许有人有什么想法？谢谢！

解决方案

此行 strFolder = Dir（Range（C7）。Value） code>在目录中找到firts文件（从 C7 ），然后将此文件的路径写入变量 strFolder （说， C：\temp\somefile.txt ）。

代码的下一行： strFile = Dir（strFolder&* .xlsx）采用此路径并添加 *。xlsx 。因此，您将获得 strFile = Dir（C：\temp\somefile.txt * .xlsx），这是错误的。

因此，更改此代码：

  strFolder = Dir（Range（C7）。Value） 
 strFile = Dir（strFolder&* .xlsx）
  

到下一个：

  strFolder = Range（C7）值
 strFile = Dir（strFolder&* .xlsx Btw，我建议你指定范围（）的表格， C7）像这样： wsDest.Range（C7） 
 
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.
 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:
strFolder = "C:\Users\user\Desktop\"
with
 strFolder = Dir(Range("C7").Value)
but it does not work. Maybe someone has any ideas? Thanks!
解决方案
This line strFolder = Dir(Range("C7").Value) finds firts file in directory (from C7) and then writes path of this file into variable strFolder (say, 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.

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")

                        
这篇关于如何在VBA中使用单元格中的文件路径？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！