Activate open workbook from range in another workbook

Hi Guys,

I am working in a workbook (let's call it "Master workbook"). When I run a macro other workbooks are opened and data is collated from them and pasted into the Master Workbook. One of the workbooks that the macro opens is a workbook that is replaced daily. The title of the workbook is a date e.g. OtherWorkbook 01-06-2016.xlsx Because the name of the file changes the macro uses a date in C10 and data in cell D10 of my master workbook to enable me to open the file.

Cell C10 has the date 01/06/2016 and cell D10 has the formula ="OtherWorkBook "&TEXT(C10,"dd-mm-yyyy")&".xlsx" resulting in D10 containing OtherWorkbook 01-06-2016.xlsx

When in my Master Workbook the following code opens OtherWorkbook 01-06-2016.xlsx

Application.Workbooks.Open ("C:\My\File\Path\" & Range("D10")) 

    Sheets("Sheet 1").Select
    Cells.Select
    Selection.Copy
    Windows("Master Workbook.xlsm").Activate
    Sheets("Sheet 1").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

Once the information from OtherWorkbook 01-06-2016.xlsx has been copied into my Mater Workbook I want some code to activate OtherWorkbook 01-06-2016.xlsx and close the workbook. How do I write this as VBA code when the name of the file is a date that changes i.e. the name of the file will not always be the same.

Any help you can give me is much appreciated.

Thanks

Hi there,

See if the following code does what you need:

Option Explicit


Sub CollectDailyData()

    Const sSHEETNAME_MASTER As String = "My Master Worksheet Name"
    Const sSHEETNAME_DATA   As String = "Sheet 1"
    Const sFILENAME_CELL    As String = "D10"
    Const sFILEPATH         As String = "C:\My\File\Path\"

    Dim sFullName           As String
    Dim wksMaster           As Worksheet
    Dim wbkDaily            As Workbook

    Set wksMaster = ThisWorkbook.Worksheets(sSHEETNAME_MASTER)

'   Get the FileName of the daily workbook
    sFullName = sFILEPATH & wksMaster.Range(sFILENAME_CELL)

'   Open the daily workbook
    On Error Resume Next
        Set wbkDaily = Application.Workbooks.Open(Filename:=sFullName)
    On Error GoTo 0

'   Proceed only if the daily workbook has been successfully opened
    If Not wbkDaily Is Nothing Then

'         Copy the data from the daily workbook to the ClipBoard
          wbkDaily.Worksheets(sSHEETNAME_DATA).Cells.Copy

'         Paste the data from the ClipBoard to the Master workbook
          With ThisWorkbook.Worksheets(sSHEETNAME_DATA)
              .PasteSpecial xlPasteValuesAndNumberFormats
          End With

'         Before closing the daily workbook, temporarily disable Alerts to avoid
'         the "There is a lot of data on the ClipBoard . . . " message
          Application.DisplayAlerts = False

              wbkDaily.Close SaveChanges:=False

'         Re-enable Alerts now that the daily workbook has been closed
          Application.DisplayAlerts = True

    Else: MsgBox "The workbook """ & sFullName & """ could not be opened", vbExclamation

    End If

End Sub

The highlighted values may be altered to suit your own requirements.

The My Master Worksheet Name worksheet is the worksheet which contains the FileName prefix and the Date cells.


Hope this helps - please let me know how you get on.

Regards,

Greg M