I'm hoping someone can point a novice in the right direction. Basically I have a speadsheet with rows of info relating to each day of the month. Currently these rows are updated manually from separate corresponding daily sheets and ideally I'd like to automate this process. One idea I've had is trying to recreate the file name for each daily sheet from with Excel but I just get REF! errors. The daily files start with dd-mm-yy and I was hoping to somehow add this date from a cell within Excel and attach it to [dd-mm-yy] worksheet file path ] to populate the current sheet with external sheet but so far not having much luck. Any help much appreciated. Thanks.
Have a look at the data tab and then "connections". Press Add and then Browse (at the bottom). Then you can select your worksheets and perhaps get a macro to update them.
Say we need to extract data from a variety of workbooks. To get the data we need to know:
- the path to the workbook
- the name of the workbook
- the tab in the workbook containing the data
- the cell in the tab containing the data
We put the necessary info into a tab as follows:
Lets assume that the information is on sheet x
running this macro:
Dim wbPath As String, WorkbookName As String
Dim WorksheetName As String, CellRef As String
Dim Ret As String, i As Long, N As Long
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
wbPath = "'" & .Cells(i, 1).Value
WorkbookName = "[" & .Cells(i, 2).Value & "]"
WorksheetName = .Cells(i, 3).Value & "'!"
CellRef = .Cells(i, 4).Value
Ret = wbPath & WorkbookName & WorksheetName & Range(CellRef).Address(True, True, -4150)
.Cells(i, 5).Value = ExecuteExcel4Macro(Ret)
will fill column E with the results.
You can run the macro whenever you need to or arrange to run the macro each time the workbook is opened.