Importing csv file with variable name

[AndyEd]

Is it possible to code for data within a csv file to be copied into a worksheet of a named workbook, where the csv file has a variable name, i.e. where the csv file is exported from a 3rd party application and the first file exported has the name "Export", then "Export(1), then "Export(2), etc.

[Richard Buttrey]

Something like the following

Sub Latest()

    Dim fso, ofileItem, oSource, stDirectory As String, stLatest As String, dtLatest As Date, stExt As String
    
    stDirectory = "C:\users\temp\" ' Change folder path as necessary
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oSource = fso.GetFolder(stDirectory)
    
    For Each ofileItem In oSource.Files
        stExt = fso.getextensionname(ofileItem)
        If stExt = "xlsx" Then
            If FileDateTime(stDirectory & ofileItem.Name) > dtLatest Then
                stLatest = ofileItem.Name
                dtLatest = FileDateTime(stDirectory & ofileItem.Name)
            End If
        End If
        
    Next ofileItem
    
    Set fso = Nothing    
    
    Workbooks.Open FileName:=stDirectory & stLatest
    ' add code to copy the csv sheet
    ActiveWorkBook.Close
    ;add code to paste to the relevant sheet in the current workbook

[AndyEd]

Hi Richard

That works a treat for selecting the latest file. Is there a way that the user could select the csv file they require? Would it be via a dialogue box?

[AndyEd]

Richard

Sorry. I was given incorrect information. It seems that the csv files are opened one after the other and remain open, so the user would have to select the relevant csv file to copy and close it once that's been done.

There could be a number of csv files open at any one time.

I think it would be easier for them to simply "Alt + Tab" to select the relevant csv file and then manually select all the data on the single sheet that is displayed, before pasting it into the relevant destination workbook & sheet.

Unless you can suggest if there is another way....I've got no clue!

[Richard Buttrey]

So are you saying that the user doesn't want to open just the csv file with the latest date/time stamp, they want to pick a file and open that?

And if so, and having done that for one file do they want to open another file leaving the first one also open in memory?

[AndyEd]

They have the destination workbook open, run two reports from a 3rd party application which exports them as csv files, e.g. "reportA.csv" & "reportB.csv", each containing a single sheet of data.

They copy each sheet of data, pasting it into a particular worksheet on the destination workbook before running a macro to manipulate the pasted data, creating a new workbook. The two csv files are then closed, the data in the destination workbook cleared, and the process repeated, where the exported csv files are named "reportA(1)" & "reportB(1)" and so on until the 3rd party application is closed and reopened when the csv names revert to "reportA" etc.

I've written code to manipulate the data and run the necessary report but was looking at whether there's a way to import the csv files(s) data in to the relevant worksheets.

But as the csv files are a)Open, & b)Contain a variable name, i.e. "filename", then "filename(1)", then "filename(2)" I'm struggling to work out whether it could be done.

Additionally the user ideally should be able to see which csv file they're selecting, to prevent them from entering the wrong one into the relevant worksheets in the destination workbook.

Does that make sense?