Results 1 to 7 of 7

Thread: Copy Sheet from export file named by date

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Sheet from export file named by date

    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.

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy Sheet from export file named by date

    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.)

    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.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Sheet from export file named by date

    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

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Sheet from export file named by date

    Sorry rookie mistake, code below
    [Code]
    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
    [Code]

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Sheet from export file named by date

    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

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy Sheet from export file named by date

    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.

    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
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Sheet from export file named by date

    Worked like a charm!
    Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •