是否有VBA函数可以将文件名拉入代码中？

Question

我设置了一个宏来运行股票提要报告分析；但是，我将其特定于一个文件。我正在使用文件名，并提取一部分来创建列标题。目前，代码使用特定的文件路径/文件名来完成此任务，但我想让它成为一个通用函数，可以操作多个文件(每个文件使用相同的结构)。

我甚至不确定从哪里开始引用文件名才能继续。

Dim MyPath As String, mps As Variant, mps_temp As String, mydate As Date, IntroDate As Date, i As Integer

MyPath = "C:\Users\Kirank\Documents\Stock Feed Analysis\HVL_Available_to_Sell_Report_with_Headers 2019.01.01"
mps = Split(MyPath, " ")

For i = LBound(mps) To UBound(mps)
    mps_temp = mps(UBound(mps) - i)
    If mps_temp Like "####.##.##" Then
        mydate = DateSerial(Mid(mps_temp, 1, 4), Mid(mps_temp, 6, 2), Mid(mps_temp, 9, 2))
        IntroDate = mydate - 181
        Exit For
    End If

我希望有代码可以将文件路径拉到"MyPath“字段中，这样我就可以使用拆分函数从文件名中拉出日期。

Answer 1

希望下面的代码能对有用！

我使用了Do循环和Dir函数，以便循环遍历存在于硬编码文件夹StockAnalysis中的所有excel文件，而无需硬编码文件名。

Dim Mypath As String, fileName As String, mps As Variant, mps_temp As String, mydate As Date, IntroDate As Date, i as integer

Application.ScreenUpdating = False

Mypath = "C:\Users\Kirank\Documents\Stock Feed Analysis\"

fileName = Dir(Mypath & "*.xl?")    'Dir functions support use of wildcards character * and ? used to look for all types of excel files in the folder.


Do While fileName <> ""

Count = Count + 1

mps = Split(fileName, " ")

For i = LBound(mps) To UBound(mps)
  mps_temp = mps(UBound(mps) - i)
    If mps_temp Like "####.##.##.xlsx" Then
      mydate = DateSerial(Mid(mps_temp, 1, 4), Mid(mps_temp, 6, 2),Mid(mps_temp, 9,2))
        IntroDate = mydate - 181      

        Cells(Count, 1).Value = IntroDate    'Saving Introdate in excel, feel free to change the destination.
        Exit For
    End If
Next i

fileName = Dir()

Loop

Answer 2

很抱歉，请忽略此答案，因为我没有理解问题

您可以使用以下命令获取文件路径：

MyPath=thisworkbook.path

这对你有用吗？