CODEWITHSUNDEEP
excelvba
user544358
user544358

Reputation: 31

Run macro on any CSV file I open, not just the one I have the file name for

I managed to create a macro using the "record macro" function in Excel.

The problem is I have multiple CSV files and the recorded macro has the ActiveSource hard-coded in. How can I run a macro on any CSV without hard-coding the title of the sheet?

Also I do not want to use fixed columns as in the example below. I want to select columns based on the title of the column. So for example, if the column header in A1 is volts, then I want column A in the graph.

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

Upvotes: 2

Views: 3938

Answers (2)

Brad
Brad

Reputation: 12253

You can open up all the files you want to work with dynamically, too. Here there is no need to hardcode anything in either VBA or in your worksheet plus you can have one master xlsm file which can work with lots of files at once.

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

Upvotes: 1

j boschiero
j boschiero

Reputation: 520

For your first issue, using a filepath/filename selected at run time, i would have a cell on the first sheet of the workbook into which i pasted the file name and path, probably Sheet1 cell A1, then i would use 

ActiveSheet.QueryTables.Add(Worksheets("Sheet1").range("A1").value,Destination:=Range("$A$1")).TextFileCommaDelimiter = True

You may need to adjust more properties as you see fit (try recording a macro that uses the Data → From Text feature if you are unsure of what properties you care about or how to specify them).

For your second issue, if the names of the columns are always going to be exactly the same, no changes in spelling, and they will always been in the top row, then what i tend to do is use a For loop with a Case Select. Here's some of my code:

        'Loop through all the columns to find the ones we want to graph
        For j = 2 To act_cols
            Select Case ws.Cells(1, j).value
                Case "FT410": ft410_col = j
                Case "FT410FlowCheck": ft410check_col = j
                Case "VSD410": vsd410_col = j
                Case "Set Point": sp_col = j
                Case "% Error (Avg)": error_avg_col = j
            End Select
        Next j

Your third issue should probably have its own post with a bit more detail.

Hope that helps a bit.

Upvotes: 0

Related Questions