替换filepath字符串中的某些值，使filepath更加动态。

Question

基本上，我编写了一个VBA代码来更新excel仪表板，以显示“每周KPI数字”。用于填充/刷新仪表板的数据来自我每周下载的另一个excel文件，当我每周运行宏时手动更改文件路径时，我希望使它更加“动态”。

文件路径的示例:C：\Users\John\下载[2022年OPS性能周更新-我们100122.xlsx ]

*请注意每一个新的每周文件，只有日期更改到每周六的一周，例如100822，此后101522的MMDDYY格式。

对于每个填充/刷新仪表板的KPI值，它都来自该周文件中的一个单元格值，例如："='C:\Users\John\Downloads2022 OPS性能周更新-我们100122.xlsxSummary'!R12C68“

*请每周注意到单元格数(列)增加了1，例如R12C69此后的R12C70，它在一年的最后一个星期六在R12C81结束，在年初在R12C30重新开始。

我想我的同伴，所以成员会指向我的循环，因此我需要一些指导开始，因为我是一个VBA初学者。

下面是我为宏编写的代码：

    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R12C68"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R54C68"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R51C68"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R45C68"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R24C68"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R39C68"
    Range("D8").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R66C68"
    Range("D9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R69C68"
    Range("D10").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R27C68"
    Range("D11").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R48C68"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R33C68"
    Range("D14").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\John\Downloads\[2022 OPS Performance Weekly Update - WE 100122.xlsx]Summary'!R30C68"
        
End Sub

Answer 1

尝试以存储数据的文件夹为目标。只需在数据完成后提取数据，然后删除它。创建一个将存储临时数据的帮助工作表和另一个将编译所有数据的工作表。

对于这个示例，我使用的文件名是"ThisFileExtractorName.xlsb“。

将提取"C:\Users\John\Downloads"中以"2022“开头的所有文件。

Sub CheckFolder()
Dim sFilePath As String
Dim sFileName As String
Dim fsoz As String
Dim fso As New FileSystemObject

sFilePath = "C:\Users\John\Downloads\"
sFileName = Dir(sFilePath & "2022" & "*.xlsx")
Application.ScreenUpdating =  False
Do While Len(sFileName) >0
  If Right(sFilename, 4) = "xlsx" Then
     fsoz = fso.GetFileName(sFileName)
     Workbooks.Open sFilePath & sFileName
     Workbooks(fsoz).Worksheets(1).Range("A1:B4").Copy _ 'just change the range based on available data. you can also make this dynamic.
                Workbooks("ThisFileExtractorName.xlsb").Worksheets("SheetDump").Range("A1")
     Workbooks(fsoz).Close SaveChanges:=True
     CopyDataStarts
  End If
  sFileName = Dir
Loop
End Sub

那就换个副本代课。

Sheet1命名为RAW，Sheet2命名为SheetDump

Sub CopyDataStarts()
  Dim s1 As long
  Dim s2 As long

 s1 = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row +1
 s2 = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
 
 Worksheets("SheetDump").Range("A2:B" & s2).Copy  Worksheets("RAW").Range("A" & s1)
 Sheet2.Range("A2:B" & s2).ClearContents  'clearing this part so we can use for the next loop
End Sub

由于您能够将所有数据编译成一个表“原始”，您现在可以使用该表作为您的KPI的参考。