Hi Excel Forum,

I'm on the verge of finishing a project, and just want to add this last thing to make the whole thing a little bit more user friendly.

My project involves retrieving and matching data from two separate date-coded reports (formatted differently, which is something I can do nothing about). At the moment, the macros I've written (which work perfectly), retrieve the previous days data and do everything I need them to. However, I want to add a functionality to access historical data (for comparison purposes), and currently the only way to do this is to hardcode in the date, which is obviously not as polished as it could be!

My idea is that the function would ask the user if they wanted to automatically select the files to process (and then run the existing code that sets the variables TodaysDate1, TodaysDate2 & TodaysDate3), but if they click no, displays an input box that prompts them to input the date (as dd/mm/yyyy or a calendar or whatever), and then sets the above variables accordingly.)

I read something about returning it as an array and then splitting it, but I'm not quite sure whether that would work and how.

I've posted the portion of the code that would call this function below (its part of a much larger sub, but this is the only bit I would have thought was relevant):

Private Sub ML_And_Tradar_File_Importer()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    '.Calculation = xlCalculationManual
End With
Dim TodaysDate1, TodaysDate2, TodaysDate3 As String

Dim ImportWbk As Workbook
Dim Import As String

    TodaysDate1 = Format(Date, "yyyymmdd") 'Used to open UBS report
    TodaysDate2 = Format(Date, "dd_mm_yy") 'Used to open Tradar report
    TodaysDate3 = Format(Date, "dd-mm-yy") 'Used to save file later in code

'These next blocks of code import the UBS and Tradar Reports to the main reconciliation file
    
    

Debug.Print "H:\FTP\UBS\Incoming\" & TodaysDate1 & ".PRTNetOpenPositions-TradarRec.GRPTIRHK.csv"
    Set ImportWbk = Workbooks.Open("H:\FTP\UBS\Incoming\" & TodaysDate1 & ".PRTNetOpenPositions-TradarRec.GRPTIRHK.csv")
     
    
    
    ImportWbk.Sheets(1).Copy After:=Workbooks("CFD Reconciliation File.xlsm").Sheets(2)
    ImportWbk.Close SaveChanges:=False
    Sheets(3).Name = "UBS Report"
    
    

    Set ImportWbk = Workbooks.Open("I:\MIDDLE OFFICE\Reconciliations\Tradar Files\Equity Swaps Positions\UBS\" & _
                                    TodaysDate2 & "UBS Position MTM Equity Swaps Last Business Day.csv")

                                    
    ImportWbk.Sheets(1).Copy After:=Workbooks("CFD Reconciliation File.xlsm").Sheets(3)
    ImportWbk.Close SaveChanges:=False
    Sheets(4).Name = "Tradar Report"

Exit Sub
MissingUBSFile:
    MsgBox ("The following essential file is missing:" & vbNewLine & _
    "H:\FTP\UBS\Incoming\" & TodaysDate1 & ".PRTNetOpenPositions-TradarRec.GRPTIRHK.csv")
    Exit Sub
MissingTradarFile:
    MsgBox ("The following essential file is missing:" & vbNewLine & _
    "I:\MIDDLE OFFICE\Reconciliations\Tradar Files\Equity Swaps Positions\UBS\" & _
    TodaysDate2 & "UBS Position MTM Equity Swaps Last Business Day.csv")
    Exit Sub

End Sub
Thanks in advance for your help.

Jonathan

---------- Post added at 07:04 AM ---------- Previous post was at 06:17 AM ----------

Hi all, realised the solution had been staring me in the face.

Have ammended the code as follows:

Private Sub ML_And_Tradar_File_Importer()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    '.Calculation = xlCalculationManual
End With
Dim day As String
Dim month As String
Dim year As String
Dim Msg As String
Dim Style, Response
Dim Column As Integer
Dim TodaysDate1, TodaysDate2, TodaysDate3 As String
Dim LastRow, LastCol As Long
Dim TradarStartCell, TradarFinishCell, TradarCopyStartCell, UBSFinishCell, Rng, Autofill, ValidationStart As Variant
Dim ImportWbk As Workbook
Dim Import As String
Dim RowNum As Long
Dim ReplaceVal As String
Dim I, J As Long
Dim TargetRange, QCell As String
Dim DateSelect As String
Dim DateArray() As String
Msg = "Would you like to automatically" & vbNewLine & "select the files to process?"
Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
Response = MsgBox(Msg, Style, "Data Source Choice")
Select Case Response
    Case vbYes


    TodaysDate1 = Format(Date, "yyyymmdd")
    TodaysDate2 = Format(Date, "dd_mm_yy")
    TodaysDate3 = Format(Date, "dd-mm-yy")

    Case vbNo
DateChoose:
    DateSelect = "/" & Application.InputBox("Please input a date as ""dd/mm/yy"":" & vbNewLine & vbNewLine & "N.B. Reports are saved with the date following the period to which " & vbNewLine & _
                                            "the report refers to.", "Report Selection", Format(Date, "dd/mm/yy"), Type:=2)
    If DateSelect = "/False" Then Exit Sub 'User clicked cancel
    DateArray = Split(DateSelect, "/")
        day = DateArray(1)
        month = DateArray(2)
        year = DateArray(3)
        If Len(year) = 2 Then
        year = Left(Format(Date, "yyyy"), 2) & year
        ElseIf Len(year) <> 2 Then
            MsgBox "You did not input the year correctly!" & vbNewLine & "Please try again."
            GoTo DateChoose
        End If
    If CInt(month) > 12 Then
        MsgBox "You did not input the date correctly!" & vbNewLine & "Please try again."
        GoTo DateChoose
    End If

    TodaysDate1 = year & month & day
    TodaysDate2 = day & "_" & month & "_" & Right(year, 2)
    TodaysDate3 = day & "-" & month & "_" & year
    Case vbCancel
Exit Sub
'These next blocks of code import the UBS and Tradar Reports to the main reconciliation file
End Select
    

Debug.Print "H:\FTP\UBS\Incoming\" & TodaysDate1 & ".PRTNetOpenPositions-TradarRec.GRPTIRHK.csv"
    Set ImportWbk = Workbooks.Open("H:\FTP\UBS\Incoming\" & TodaysDate1 & ".PRTNetOpenPositions-TradarRec.GRPTIRHK.csv")
     
    
    
    ImportWbk.Sheets(1).Copy After:=Workbooks("CFD Reconciliation File - Working Version.xlsm").Sheets(2)
    ImportWbk.Close SaveChanges:=False
    Sheets(3).Name = "UBS Report"
    
    
    Debug.Print ""
    Set ImportWbk = Workbooks.Open("I:\MIDDLE OFFICE\Reconciliations\Tradar Files\Equity Swaps Positions\UBS\" & _
                                    TodaysDate2 & "UBS Position MTM Equity Swaps Last Business Day.csv")

                                    
    ImportWbk.Sheets(1).Copy After:=Workbooks("CFD Reconciliation File - Working Version.xlsm").Sheets(3)
    ImportWbk.Close SaveChanges:=False
    Sheets(4).Name = "Tradar Report"
Exit Sub
MissingUBSFile:
    MsgBox ("The following essential file is missing:" & vbNewLine & _
    "H:\FTP\UBS\Incoming\" & TodaysDate1 & ".PRTNetOpenPositions-TradarRec.GRPTIRHK.csv")
    Exit Sub
MissingTradarFile:
    MsgBox ("The following essential file is missing:" & vbNewLine & _
    "I:\MIDDLE OFFICE\Reconciliations\Tradar Files\Equity Swaps Positions\UBS\" & _
    TodaysDate2 & "UBS Position MTM Equity Swaps Last Business Day.csv")
    Exit Sub

End Sub