Copy Sheet from export file named by date

SW115

New Member
Joined
Sep 20, 2019
Messages
5
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.)

Rich (BB code):
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.
 
Upvote 0
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
 
Upvote 0
Sorry rookie mistake, code below
Code:
[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]
 
Upvote 0
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
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top