How to automatically update a spreadsheet from various external spreadsheets?

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.

-------------Problems Reply------------

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:

  1. the path to the workbook
  2. the name of the workbook
  3. the tab in the workbook containing the data
  4. the cell in the tab containing the data

We put the necessary info into a tab as follows:

How to automatically update a spreadsheet from various external spreadsheets?

Lets assume that the information is on sheet x

running this macro:

Sub GetExternalData()

Dim wbPath As String, WorkbookName As String
Dim WorksheetName As String, CellRef As String
Dim Ret As String, i As Long, N As Long

With Sheets("x")
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)
Next i
End With
End Sub

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.

Category:excel Views:844 Time:2015-03-19

Related post

Copyright (C), All Rights Reserved.

processed in 0.188 (s). 11 q(s)