Macro to find latest csv file by date and copy to other workbook

[gobits]

Hi,

I have found some answers to somewhat similar questions but none that fits my need and unfortunately I am not experienced enough with VBA to piece together a functioning macro from what I've found searching online so far.

What I need to do is to create a macro that runs automatically once per day. I know how to use the Workbook.open function or a button but I'd rather have the macro run on a timer if possible. If that's not possible (which I'm thinking is probably the case), I'll run it when the workbook opens. Here's what I need the macro to do:

I need the macro in my workbook to go to a remote server directory, look for the latest file by the date in the name of the file (e.g. "MyDataFile_01-01-2014.csv"), there will be many files, same name but different dates in the directory. Once the file with the latest date is identified, I need to copy the data from "sheet 1", then look in my workbook for a worksheet named "MyDataFile"(without the date appended), find the last used row in the only table in this worksheet (lets say it's named "Table1"), whether this is row 2 (below the header) or row 2000, and append the copied data to this table.

Ideally, I'd like to have a message pop up if I have to run the macro on opening the workbook, stating that the latest data is being retrieved AND I would love it if the macro could "know" it the latest file has already been retrieved so I don't duplicate data - since I am appending the data to the bottom of a table - and if the latest available data is already in the workbook, a message stating this would be great as well. Generous commenting would also be appreciated since I am trying to learn how to do this myself.

This is a tall order (for me anyway) if anyone knows how to do this I would be elated because so far I am striking out on this one.

Thank you beforehand to anyone who can help me out.

G

 

[Rosen]

Here is the modified code so it copies and pastes the csv file into the workbook.

Private Sub Workbook_Open()
    ' Variables
    Dim oMetadata As Worksheet
    Dim dtLatestDate As Date
    Dim dtCurrntDate As Date: dtCurrntDate = VBA.Now ' Note that I have declared and assigned a variable in one
                                                     ' line by using : to indicate that these are, for compiling
                                                     ' purpose, two seperate lines of code.
    Dim FilePath As String
    Dim File As Integer
    Dim oInput As Worksheet
    Dim lLine As Long, sLine As String, lColumn As Long, Pos As Long, lRow As Long
    ' Change this to the remote directory you wish to pull the .csv file from.
    Const DIRECTORY As String = "\\networkservername\sharedfolder\subfolder\"
    ' Unique Sheet name for a sheet to store data about the updates
    Const METADATA As String = "METADATA_DATES"
    ' Cell reference in METADATA sheet to the Latest Date
    Const LATESTDATE As String = "A1"
    ' the Files date format, couldn't tell by your post if you use MM-DD-YYYY or DD-MM-YYYY, so I guessed.
    Const DATEFORMAT As String = "MM-DD-YYYY"
    Const SHEET As String = "MyDataSheet"
    ' We need to store and retrieve the last latest date uploaded to ensure we don't duplicate an upload
    ' for this purpose we will create and hide (as it is not needed for any user) a worksheet called
    ' METADATA_DATES
    ' To check to see if this sheet already exists we will need to call it, if it doesn't exist it will
    ' raise an error, as we are expecting this we can bypass the error message and handle it ourselves by
    ' using the Resmue Next, which will ignore the error.
    On Error Resume Next
    Set oMetadata = Sheets(METADATA)
    ' After Assigning Sheets(METADATA) If there is no sheet called METADATA (or the value it contains) will
    ' raise an error which will be ingored but will still store the error in the Err object.  The Err object's
    ' Number field will be set to an value other then 0 if this occures, so to test if we don't have a sheet
    ' called METADATA we simply check if the Err.Number is anything but 0
    If Not Err.Number = 0 Then
        ' So we don't have a METADATA sheet, we need to create one by adding a new sheet to the Sheets
        ' collection
        Set oMetadata = Sheets.Add
        ' Rename it to our METADATA name
        oMetadata.Name = METADATA
        ' Make it invisable to the user (this isn't necessarily required just something us paranoid programers
        ' do because we find users to be untrustworthy... they just want to ruin my program... they are out to
        ' get me... RUN!)
        oMetadata.Visible = xlSheetVeryHidden ' only accessable via code
        ' Now that we have a new sheet, it is assumed that we have been doing this already for some time and will
        ' have already uploaded (in some fastion) csv files to this spreadsheet, to ensure we don't upload one
        ' which is already uploaded, we can prompt the user to advise of the lastest uploaded date.
        dtLatestDate = CDate(InputBox(Prompt:="Please provided the date of the last csv file uplaoded", Title:="Latest Date"))
        ' This last line could have caused another error to occure if something other then a date is entered into
        ' the InputBox, but not to worry, we will simply treate this as a January 0, 1900 date (default), better
        ' cantrols can be used if necessary, but Im far too lazy for something more complicated :P
        oMetadata.Range(LATESTDATE).Value = dtLatestDate
        ' Since we have handled our error, we can clear the Err object
        Err.Clear
    End If
    ' We no longer need to bypass errors
    On Error GoTo 0
    ' Now that we have access to our METADATA sheet through oMetadata we can access our lastest date.
    dtLatestDate = oMetadata.Range(LATESTDATE).Value
    ' Now we will cycle back from the current date till the latest date looking for a valid file.
    Do While dtCurrntDate > dtLatestDate
        ' Now we check if this dtCurrntDate has an associated file by converting it into the file path name and
        ' checking if that file exists.
        FilePath = DIRECTORY & SHEET & "_" & Format(dtCurrntDate, "MM-DD-YYYY") & ".csv"
        ' The Dir function returns the pathname of the first file that matches the parameters provided, or a null string
        ' if the parameters are not matched.
        If Not VBA.Dir(FilePath) = vbNullString Then
            ' Now that we have found our file it is time to import it into our MyDataFile
            Set oInput = Sheets(SHEET)
            ' Find the first empty line (this assumes that there cannot be a blank space in column A)
            lLine = 2
            Do Until oInput.Range("A" & lLine).Value = vbNullString
                lLine = lLine + 1
            Loop
            ' Open our latest file into excel, while we could assign it to a variable we will simply just
            ' attach it to a With statement (which I read somewhere is faster to access)
            With Workbooks.Open(Filename:=FilePath)
                ' To find the last line in the file will employ the same method as above and cycle through each
                ' row until we find a blank one (this of course assumes that we have not blank cells in Column A
                ' until we reach the end of the file).
                lRow = 2
                Do Until .Range("A" & lRow).Value = vbNullString
                    lRow = lRow + 1
                Loop
                ' As we do not need the first empty row, we will deincreament our lRow variable
                lRow = lRow - 1
                ' Copy the entire range
                .Sheets(1).Range("A1:BA" & lRow).Copy
                ' Append what we copied onto our master sheet
                oInput.Range("A" & lLine).PasteSpecial xlPasteValues
            End With
            ' We have found and handled our .csv file, nothing left to do, so we exit the loop
            Exit Do
        End If
        '  Deincrement the date by one day and run through above code again.
        dtCurrntDate = dtCurrntDate - 1 ' Subtracting one from a date reduces the date by one whole day as the Date
                                        ' variable is stored in a double-precision floating-point value where the
                                        ' date is the number of days sinse January 0, 1900 and the time is derived
                                        ' from the value after the decimal
    Loop
End Sub

Always remember to backup your work before running new code.

Hope this helps!

 

[gobits]

Hi Rosen,

Thank you for spending your time helping me.

I am getting an error at the following line in your modified code:

Do Until .Range("A" & lRow).Value = vbNullString

Also, it looks like it is still trying to copy one line at a time. but I can't tell for sure as it does not finish executing.

 

[gobits]

Hi Rosen,

Thank you for spending your time helping me.

I am getting an error at the following line in your modified code:

Do Until .Range("A" & lRow).Value = vbNullString

Also, it looks like it is still trying to copy one line at a time. but I can't tell for sure as it does not finish executing.

So I made a change that doesnt actually break everything... The change in the code is in bold Red.

(I renamed it Sub TestOne() for now):

Sub TestOne()
    ' Variables
    Dim oMetadata As Worksheet
    Dim dtLatestDate As Date
    Dim dtCurrntDate As Date: dtCurrntDate = VBA.Now ' Note that I have declared and assigned a variable in one
                                                     ' line by using : to indicate that these are, for compiling
                                                     ' purpose, two separate lines of code.
    Dim FilePath As String
    Dim File As Integer
    Dim oInput As Worksheet
    Dim lLine As Long, sLine As String, lColumn As Long, Pos As Long, lRow As Long
    ' Change this to the remote directory you wish to pull the .csv file from.
    Const DIRECTORY As String = "\\NYK-PEG-NAS01\Pegasus\Enterprise Model\Perf Gov\CSVC\"
    ' Unique Sheet name for a sheet to store data about the updates
    Const METADATA As String = "METADATA"
    ' Cell reference in METADATA sheet to the Latest Date
    Const LATESTDATE As String = "A1"
    ' the Files date format.
    Const DATEFORMAT As String = "YYYYMMDD"
    Const SHEET As String = "skill-data"
    ' We need to store and retrieve the last latest date uploaded to ensure we don't duplicate an upload
    ' for this purpose we will create and hide (as it is not needed for any user) a worksheet called
    ' METADATA_DATES
    ' To check to see if this sheet already exists we will need to call it, if it doesn't exist it will
    ' raise an error, as we are expecting this we can bypass the error message and handle it ourselves by
    ' using the Resume Next, which will ignore the error.
    On Error Resume Next
    Set oMetadata = Sheets(METADATA)
    ' After Assigning Sheets(METADATA) If there is no sheet called METADATA (or the value it contains) will
    ' raise an error which will be ignored but will still store the error in the Err object.  The Err object's
    ' Number field will be set to an value other then 0 if this occurs, so to test if we don't have a sheet
    ' called METADATA we simply check if the Err.Number is anything but 0
    If Not Err.Number = 0 Then
        ' So we don't have a METADATA sheet, we need to create one by adding a new sheet to the Sheets
        ' collection
        Set oMetadata = Sheets.Add
        ' Rename it to our METADATA name
        oMetadata.Name = METADATA
        ' Make it invisible to the user (this isn't necessarily required just something us paranoid programmers
        ' do because we find users to be untrustworthy... they just want to ruin my program... they are out to
        ' get me... RUN!)
        oMetadata.Visible = xlSheetVeryHidden ' only accessible via code
        ' Now that we have a new sheet, it is assumed that we have been doing this already for some time and will
        ' have already uploaded (in some fashion) csv files to this spreadsheet, to ensure we don't upload one
        ' which is already uploaded, we can prompt the user to advise of the latest uploaded date.
        dtLatestDate = CDate(InputBox(Prompt:="Please provide the date of the last .csv file uploaded", Title:="Latest Date"))
        ' This last line could have caused another error to occur if something other then a date is entered into
        ' the InputBox, but not to worry, we will simply treat this as a January 1, 1900 date (default), better
        ' controls can be used if necessary, but I'm far too lazy for something more complicated :P
        oMetadata.Range(LATESTDATE).Value = dtLatestDate
        ' Since we have handled our error, we can clear the Err object
        Err.Clear
    End If
    ' We no longer need to bypass errors
    On Error GoTo 0
    ' Now that we have access to our METADATA sheet through oMetadata we can access our latest date.
    dtLatestDate = oMetadata.Range(LATESTDATE).Value
    ' Now we will cycle back from the current date till the latest date looking for a valid file.
    Do While dtCurrntDate > dtLatestDate
        ' Now we check if this dtCurrntDate has an associated file by converting it into the file path name and
        ' checking if that file exists.
        FilePath = DIRECTORY & SHEET & "-" & Format(dtCurrntDate, "YYYYMMDD") & ".csv"
        ' The Dir function returns the pathname of the first file that matches the parameters provided, or a null string
        ' if the parameters are not matched.
        If Not VBA.Dir(FilePath) = vbNullString Then
            ' Now that we have found our file it is time to import it into our MyDataFile
            Set oInput = Sheets(SHEET)
            ' Find the first empty line (this assumes that there cannot be a blank space in column A)
            lLine = 2
            Do Until oInput.Range("A" & lLine).Value = vbNullString
                lLine = lLine + 1
            Loop
            ' Open our latest file into excel, while we could assign it to a variable we will simply just
            ' attach it to a With statement (which I read somewhere is faster to access)
            With Workbooks.Open(Filename:=FilePath)
                ' To find the last line in the file will employ the same method as above and cycle through each
                ' row until we find a blank one (this of course assumes that we have not blank cells in Column A
                ' until we reach the end of the file).
 [COLOR=#ff0000]                [B] 'lRow = ws.Rows.Count.End(xlUp)
                'Do Until .Range("A" & lRow).Value = vbNullString
                    lRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
                'Loop[/B][/COLOR]
                ' As we do not need the first empty row, we will decrement our lRow variable
                lRow = lRow - 1
                ' Copy the entire range
                .Sheets(1).Range("A1:BA" & lRow).Copy
                ' Append what we copied onto our master sheet
                oInput.Range("A" & lLine).PasteSpecial xlPasteValues
            End With
            ' We have found and handled our .csv file, nothing left to do, so we exit the loop
            Exit Do
        End If
        '  Decrement the date by one day and run through above code again.
        dtCurrntDate = dtCurrntDate - 1 ' Subtracting one from a date reduces the date by one whole day as the Date
                                        ' variable is stored in a double-precision floating-point value where the
                                        ' date is the number of days since January 0, 1900 and the time is derived
                                        ' from the value after the decimal
    Loop
End Sub

This actually copies the entire block at once AND it will add the data at the bottom of my table which is great. I feel so proud that I actually figured something out on my own

I have a couple of issues still though. Now the sheet I am copying from stays open and in the foreground - I need it to close up and go away. Also, the date doesn't seem to append in the metadata file, it stays at whatever date was typed in the first time, rather than update with the last file copied (Which as we know should be the file with the latest date in the name of all the files available (date format YYYYMMDD).

I haven't yet tested if it will cycle through and copy data from all files with a date later than the last copied date in the metadata file, which is something the macro has to be able to do as I cannot trust the user to open the file every day (there is a new data file generated every day... pretty much...)

 

[Rosen]

Hey Gobits,

Awesome job fixing my error!

Now I have modified the code to (1) cycle though and upload all files (not just the latest) after the latest date, (2) update the latest date after upload and (3) close the workbook after uploading. (some comments were adjusted to account for the changes)

Sub TestOne()
    ' Variables
    Dim oMetadata As Worksheet
    Dim dtLatestDate As Date
    Dim dtCurrntDate As Date: dtCurrntDate = VBA.Now ' Note that I have declared and assigned a variable in one
                                                     ' line by using : to indicate that these are, for compiling
                                                     ' purpose, two separate lines of code.
    Dim FilePath As String
    Dim File As Integer
    Dim oInput As Worksheet
    Dim lLine As Long, sLine As String, lColumn As Long, Pos As Long, lRow As Long
    ' Change this to the remote directory you wish to pull the .csv file from.
    Const DIRECTORY As String = "\\NYK-PEG-NAS01\Pegasus\Enterprise Model\Perf Gov\CSVC\"
    ' Unique Sheet name for a sheet to store data about the updates
    Const METADATA As String = "METADATA"
    ' Cell reference in METADATA sheet to the Latest Date
    Const LATESTDATE As String = "A1"
    ' the Files date format.
    Const DATEFORMAT As String = "YYYYMMDD"
    Const SHEET As String = "skill-data"
    ' We need to store and retrieve the last latest date uploaded to ensure we don't duplicate an upload
    ' for this purpose we will create and hide (as it is not needed for any user) a worksheet called
    ' METADATA_DATES
    ' To check to see if this sheet already exists we will need to call it, if it doesn't exist it will
    ' raise an error, as we are expecting this we can bypass the error message and handle it ourselves by
    ' using the Resume Next, which will ignore the error.
    On Error Resume Next
    Set oMetadata = Sheets(METADATA)
    ' After Assigning Sheets(METADATA) If there is no sheet called METADATA (or the value it contains) will
    ' raise an error which will be ignored but will still store the error in the Err object.  The Err object's
    ' Number field will be set to an value other then 0 if this occurs, so to test if we don't have a sheet
    ' called METADATA we simply check if the Err.Number is anything but 0
    If Not Err.Number = 0 Then
        ' So we don't have a METADATA sheet, we need to create one by adding a new sheet to the Sheets
        ' collection
        Set oMetadata = Sheets.Add
        ' Rename it to our METADATA name
        oMetadata.Name = METADATA
        ' Make it invisible to the user (this isn't necessarily required just something us paranoid programmers
        ' do because we find users to be untrustworthy... they just want to ruin my program... they are out to
        ' get me... RUN!)
        oMetadata.Visible = xlSheetVeryHidden ' only accessible via code
        ' Now that we have a new sheet, it is assumed that we have been doing this already for some time and will
        ' have already uploaded (in some fashion) csv files to this spreadsheet, to ensure we don't upload one
        ' which is already uploaded, we can prompt the user to advise of the latest uploaded date.
        dtLatestDate = CDate(InputBox(Prompt:="Please provide the date of the last .csv file uploaded", Title:="Latest Date"))
        ' This last line could have caused another error to occur if something other then a date is entered into
        ' the InputBox, but not to worry, we will simply treat this as a January 1, 1900 date (default), better
        ' controls can be used if necessary, but I'm far too lazy for something more complicated :P
        oMetadata.Range(LATESTDATE).Value = dtLatestDate
        ' Since we have handled our error, we can clear the Err object
        Err.Clear
    End If
    ' We no longer need to bypass errors
    On Error GoTo 0
    ' Now that we have access to our METADATA sheet through oMetadata we can access our latest date.
    dtLatestDate = oMetadata.Range(LATESTDATE).Value + 1
    ' Now we will cycle through each date from lastest date to current date looking for valid files.
    Do While dtCurrntDate >= dtLatestDate
        ' Now we check if this dtLatestDate has an associated file by converting it into the file path name and
        ' checking if that file exists.
        FilePath = DIRECTORY & SHEET & "-" & Format(dtLatestDate, "YYYYMMDD") & ".csv"
        ' The Dir function returns the pathname of the first file that matches the parameters provided, or a null string
        ' if the parameters are not matched.  That is, if the Dir function returns a null string there is no file by the
        ' path name provided.  (We are only interested if it is NOT null, that is the file exists)
        If Not VBA.Dir(FilePath) = vbNullString Then
            ' Now that we have found our file it is time to import it into our MyDataFile
            Set oInput = Sheets(SHEET)
            ' Find the first empty line (this assumes that there cannot be a blank space in column A)
            lLine = 2
            Do Until oInput.Range("A" & lLine).Value = vbNullString
                lLine = lLine + 1
            Loop
            ' Open our latest file into excel, while we could assign it to a variable we will simply just
            ' attach it to a With statement (which I read somewhere is faster to access)
            With Workbooks.Open(Filename:=FilePath)
                ' To find the last line in the file will employ the same method as above and cycle through each
                ' row until we find a blank one (this of course assumes that we have not blank cells in Column A
                ' until we reach the end of the file).
                  'lRow = ws.Rows.Count.End(xlUp)
                'Do Until Sheet(1).Range("A" & lRow).Value = vbNullString
                    lRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
                'Loop
                ' As we do not need the first empty row, we will decrement our lRow variable
                lRow = lRow - 1
                ' Copy the entire range
                .Sheets(1).Range("A1:BA" & lRow).Copy
                ' Append what we copied onto our master sheet
                oInput.Range("A" & lLine).PasteSpecial xlPasteValues
                ' We are done with the current file, it can be closed, and to ensure we don't get promted to save,
                ' we are explicitly stating we dont want to save the file by passing the SaveChanges parameter as false.
                .Close SaveChanges:=False
            End With
            ' We have found and handled our .csv file, save that it was the latest date uploaded.
            oMetadata.Range(LATESTDATE).Value = dtLatestDate
        End If
        '  Increment the date by one day and run through above code again.
        dtLatestDate = dtLatestDate + 1 ' Adding one to a date increases the date by one whole day as the Date
                                        ' variable is stored in a double-precision floating-point value where the
                                        ' date is the number of days since January 0, 1900 and the time is derived
                                        ' from the value after the decimal
    Loop
End Sub

Changes not tested, let me know if it works.

 

[gobits]

Hey Gobits,

Awesome job fixing my error!

Now I have modified the code to (1) cycle though and upload all files (not just the latest) after the latest date, (2) update the latest date after upload and (3) close the workbook after uploading. (some comments were adjusted to account for the changes)

Sub TestOne()
    ' Variables
    Dim oMetadata As Worksheet
    Dim dtLatestDate As Date
    Dim dtCurrntDate As Date: dtCurrntDate = VBA.Now ' Note that I have declared and assigned a variable in one
                                                     ' line by using : to indicate that these are, for compiling
                                                     ' purpose, two separate lines of code.
    Dim FilePath As String
    Dim File As Integer
    Dim oInput As Worksheet
    Dim lLine As Long, sLine As String, lColumn As Long, Pos As Long, lRow As Long
    ' Change this to the remote directory you wish to pull the .csv file from.
    Const DIRECTORY As String = "\\NYK-PEG-NAS01\Pegasus\Enterprise Model\Perf Gov\CSVC\"
    ' Unique Sheet name for a sheet to store data about the updates
    Const METADATA As String = "METADATA"
    ' Cell reference in METADATA sheet to the Latest Date
    Const LATESTDATE As String = "A1"
    ' the Files date format.
    Const DATEFORMAT As String = "YYYYMMDD"
    Const SHEET As String = "skill-data"
    ' We need to store and retrieve the last latest date uploaded to ensure we don't duplicate an upload
    ' for this purpose we will create and hide (as it is not needed for any user) a worksheet called
    ' METADATA_DATES
    ' To check to see if this sheet already exists we will need to call it, if it doesn't exist it will
    ' raise an error, as we are expecting this we can bypass the error message and handle it ourselves by
    ' using the Resume Next, which will ignore the error.
    On Error Resume Next
    Set oMetadata = Sheets(METADATA)
    ' After Assigning Sheets(METADATA) If there is no sheet called METADATA (or the value it contains) will
    ' raise an error which will be ignored but will still store the error in the Err object.  The Err object's
    ' Number field will be set to an value other then 0 if this occurs, so to test if we don't have a sheet
    ' called METADATA we simply check if the Err.Number is anything but 0
    If Not Err.Number = 0 Then
        ' So we don't have a METADATA sheet, we need to create one by adding a new sheet to the Sheets
        ' collection
        Set oMetadata = Sheets.Add
        ' Rename it to our METADATA name
        oMetadata.Name = METADATA
        ' Make it invisible to the user (this isn't necessarily required just something us paranoid programmers
        ' do because we find users to be untrustworthy... they just want to ruin my program... they are out to
        ' get me... RUN!)
        oMetadata.Visible = xlSheetVeryHidden ' only accessible via code
        ' Now that we have a new sheet, it is assumed that we have been doing this already for some time and will
        ' have already uploaded (in some fashion) csv files to this spreadsheet, to ensure we don't upload one
        ' which is already uploaded, we can prompt the user to advise of the latest uploaded date.
        dtLatestDate = CDate(InputBox(Prompt:="Please provide the date of the last .csv file uploaded", Title:="Latest Date"))
        ' This last line could have caused another error to occur if something other then a date is entered into
        ' the InputBox, but not to worry, we will simply treat this as a January 1, 1900 date (default), better
        ' controls can be used if necessary, but I'm far too lazy for something more complicated :P
        oMetadata.Range(LATESTDATE).Value = dtLatestDate
        ' Since we have handled our error, we can clear the Err object
        Err.Clear
    End If
    ' We no longer need to bypass errors
    On Error GoTo 0
    ' Now that we have access to our METADATA sheet through oMetadata we can access our latest date.
    dtLatestDate = oMetadata.Range(LATESTDATE).Value + 1
    ' Now we will cycle through each date from lastest date to current date looking for valid files.
    Do While dtCurrntDate >= dtLatestDate
        ' Now we check if this dtLatestDate has an associated file by converting it into the file path name and
        ' checking if that file exists.
        FilePath = DIRECTORY & SHEET & "-" & Format(dtLatestDate, "YYYYMMDD") & ".csv"
        ' The Dir function returns the pathname of the first file that matches the parameters provided, or a null string
        ' if the parameters are not matched.  That is, if the Dir function returns a null string there is no file by the
        ' path name provided.  (We are only interested if it is NOT null, that is the file exists)
        If Not VBA.Dir(FilePath) = vbNullString Then
            ' Now that we have found our file it is time to import it into our MyDataFile
            Set oInput = Sheets(SHEET)
            ' Find the first empty line (this assumes that there cannot be a blank space in column A)
            lLine = 2
            Do Until oInput.Range("A" & lLine).Value = vbNullString
                lLine = lLine + 1
            Loop
            ' Open our latest file into excel, while we could assign it to a variable we will simply just
            ' attach it to a With statement (which I read somewhere is faster to access)
            With Workbooks.Open(Filename:=FilePath)
                ' To find the last line in the file will employ the same method as above and cycle through each
                ' row until we find a blank one (this of course assumes that we have not blank cells in Column A
                ' until we reach the end of the file).
                  'lRow = ws.Rows.Count.End(xlUp)
                'Do Until Sheet(1).Range("A" & lRow).Value = vbNullString
                    lRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
                'Loop
                ' As we do not need the first empty row, we will decrement our lRow variable
                lRow = lRow - 1
                ' Copy the entire range
                .Sheets(1).Range("A1:BA" & lRow).Copy
                ' Append what we copied onto our master sheet
                oInput.Range("A" & lLine).PasteSpecial xlPasteValues
                ' We are done with the current file, it can be closed, and to ensure we don't get promted to save,
                ' we are explicitly stating we dont want to save the file by passing the SaveChanges parameter as false.
                .Close SaveChanges:=False
            End With
            ' We have found and handled our .csv file, save that it was the latest date uploaded.
            oMetadata.Range(LATESTDATE).Value = dtLatestDate
        End If
        '  Increment the date by one day and run through above code again.
        dtLatestDate = dtLatestDate + 1 ' Adding one to a date increases the date by one whole day as the Date
                                        ' variable is stored in a double-precision floating-point value where the
                                        ' date is the number of days since January 0, 1900 and the time is derived
                                        ' from the value after the decimal
    Loop
End Sub

Changes not tested, let me know if it works.

WOOHOO!!! This works like a charm. Fast and Furious...ly well. Thank you for all your help. If you're in NY I need to buy you a beverage of your choice.