Copy Sheet from export file named by date

[SW115]

I am trying to write a macro to automate copying data from an exported CSV Sheet that is dynamically named with the current date and time. For example, the current file name is Portfolio Review20190920_1013. Is there a way to identify this file by the current date and time? And what would be the most efficient way to copy the data into a master workbook? Thank you in advance.

 

[sijpie]

Hi SW115, welcome to the board.

finding / opening a file:
there are several options, but to start I would recommend that you use the file selection method. (Once you get that working together with the import, then you could if necessary try to automate it further by determining the filename in a diferent way and using the workbook.open method.)

Option Explicit

Private Sub CommandButton1_Click()
    ' Create and set the file dialog object.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        .Title = "Select an Excel File"
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .AllowMultiSelect = False
        
        Dim sFile As String
    
        If .Show = True Then
            sFile = .SelectedItems(1)
        End If
    End With
    
    If sFile <> "" Then
        Workbooks. Open sFile    ' Open the Excel file.
    End If
End Sub

In the case above you want to open a .csv file, so you need to change that in the code (.xlsx -> .csv)

If it is a comma delimited file the above will work fine.
If it is a semi-colon delimited file, let me know, you will need to open it in a slightly different way.

 

[SW115]

Thank You. I have ghosted this site for years.
I think I understand what you are suggesting but I am self taught vba with lots of holes in my knowledge.
This code opens a window to select a file. In my case, the CSV file is already opened (exported from an internal site) and always named by the current date and time (

Portfolio Review20191001_1127)​

and I am looking for code to identify and select all the data and paste the values into my master workbook.
My thought was that since the variable in the file name (date and time) matches the current date and time on my computer, that I could identify the file directly with some code like the one with poor syntax below.
Workbooks("

Portfolio Review20191001_1127​

.csv").Worksheets("

Portfolio Review" & (Current year) & (Current month) & (Current time)​

.Range("A:AB").Copy

Workbooks("Master.xlsb").Worksheets("Data2").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[SW115]

Sorry rookie mistake, code below

[LEFT][COLOR=#333333][FONT=Courier]Workbooks("[/FONT][/COLOR][COLOR=#333333][FONT=Courier][COLOR=#333333][FONT=Verdana]Portfolio Review20191001_1127[/FONT][/COLOR][/FONT][/COLOR]
[COLOR=#333333][FONT=Courier].csv").Worksheets("[/FONT][/COLOR][COLOR=#333333][FONT=Courier][COLOR=#333333][FONT=Verdana]Portfolio Review" & (Current year) & (Current month) & (Current time)[/FONT][/COLOR][/FONT][/COLOR]
[COLOR=#333333][FONT=Courier].Range("A:AB").Copy[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]   [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier] Workbooks("Master.xlsb").Worksheets("Data2").Range("A1").PasteSpecial Paste:=xlPasteValues
[Code]
[/FONT][/COLOR][/LEFT]

 

[SW115]

Thank You. I have ghosted this site for years.
I think I understand what you are suggesting but I am self taught vba with lots of holes in my knowledge.
This code opens a window to select a file. In my case, the CSV file is already opened (exported from an internal site) and always named by the current date and time (

Portfolio Review20191001_1127)​

and I am looking for code to identify and select all the data and paste the values into my master workbook.
My thought was that since the variable in the file name (date and time) matches the current date and time on my computer, that I could identify the file directly with some code like the one with poor syntax below.
Workbooks("

Portfolio Review20191001_1127​

.csv").Worksheets("

Portfolio Review" & (Current year) & (Current month) & (Current time)​

.Range("A:AB").Copy

Workbooks("Master.xlsb").Worksheets("Data2").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[sijpie]

OK. Assuming that the csv is the only csv open that starts with the name 'Portfolio review' and assuming that the workbook has only one sheet, then it is easy. Also if you want to transfer the data and it can be done with a simple copy then there is an even faster way since copy-paste is a slow activity.

Sub GetFromCSV()
    Dim wbCSV As Workbook
    Dim wsData As Worksheet
    Dim rInp As Range
    
    'search for the csv file in the opened workbooks
    For Each wbCSV In Workbooks
        If StrComp("Portfolio Review", Left(wbCSV.Name, 16)) = 0 Then 'found the file
            Exit For        'quit the For loop
        End If
    Next wbCSV
    ' check if the file is found, if not the variable will still point to 'Nothing'
    If wbCSV Is Nothing Then
        MsgBox "Cannot find the CSV file Portfolio Reviewyymmdd"
        Exit Sub
    End If
    
    'process the data
    'set a range variable to the input area
    Set rInp = wbCSV.Sheets(1).Range("A1").CurrentRegion
    'now copy fast style
    ThisWorkbook.Sheets("Data2").Range("A1").Resize(rInp.Rows.Count, rInp.Columns.Count).Value = rInp.Value
End Sub

 

[SW115]

Worked like a charm!
Thank you!