Macro Copy Help

Question

Hey Everyone,I need your help again. I have Sales data that I need to be copied and pasted into a Hot Spot workbook (both are excel) via Macro. The only problem is the name of the Sales data workbook changes each day. However, it is in the same file location/folder.&nbsp;7-23-2021.csv7-22-2021.csv&nbsp;7-21-2021.csv&nbsp; and so on.&nbsp;How do I write my VBA code to open a set folder then let me open a different file each time to copy and paste into my Hot Spot workbook?

hansvogelaar · Accepted Answer

catherine9910&nbsp;
I hope you can use the code below as starting point.
Sub ProcessCSV()
    ' Change path as needed but keep the \ at the end
    Const strPath = "C:\MyFolder\"
    Dim strFile As String
    Dim wbkSales As Workbook
    Dim wbkCSV As Workbook
    With Application.FileDialog(1) ' msoFileDialogOpen
        .Filters.Clear
        .Filters.Add "CSV Files (*.csv)", "*.csv"
        .InitialFileName = strPath &amp; "*.csv"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    Set wbkSales = ActiveWorkbook
    Set wbkCSV = Workbooks.Open(Filename:=strFile)
    ' Code to copy goes below
    ' ...
    wbkCSV.Close SaveChanges:=False
End Sub

Forum Discussion

Macro Copy Help

2 Replies

Resources