excel - 在我打开的任何 CSV 文件上运行宏，而不仅仅是我有文件名的文件

Question

我设法使用 Excel 中的“记录宏”功能创建了一个宏。

问题是我有多个 CSV 文件，并且录制的宏已硬编码 ActiveSource。如何在任何 CSV 上运行宏而不对工作表的标题进行硬编码？

此外，我不想使用固定列，如下例所示。我想根据列的标题选择列。例如，如果 A1 中的列标题是 volts ，然后我想要列 A在图中。

Sub CreateThermalPlot()
'
' CreateThermalPlot Macro
' Create new thermal plot of .csv that contains Reported CPU temp, Diode Temp, and Td.
'
' Keyboard Shortcut: Ctrl+j
'
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
        , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
        (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
        Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1)), _
        TrailingMinusNumbers:=True
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Range("A:A,T:T,U:U,W:W").Select
    Range("W1").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range( _
        "monitor_ENGtrevorSBTSITND_4910_!$A:$A,monitor_ENGtrevorSBTSITND_4910_!$T:$T,monitor_ENGtrevorSBTSITND_4910_!$U:$U,monitor_ENGtrevorSBTSITND_4910_!$W:$W" _
        )
    ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub

Answer 1

您也可以动态打开所有要使用的文件。这里无需在 VBA 或工作表中硬编码任何内容，而且您可以拥有一个可以同时处理大量文件的主 xlsm 文件。

Function SelectFilesToWorkWith() As Collection
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim files As New Collection

    With fd
        .AllowMultiSelect = True
        .Show
        For Each file In .SelectedItems
            files.Add (file)
        Next file
    End With
 Set SelectFilesToWorkWith = files
End Function

Sub work()

    Dim filesToWorkWith As New Collection
    Set filesToWorkWith = SelectFilesToWorkWith

    Dim CSVfile As String

    For Each CSVfile In filesToWorkWith

        Workbooks.Open (CSVfile)
        '...do work with file
    Next CSVfile

End Sub