Importing Excel spreadsheet data into another Excel spreadsheet containing VBA
This should get you started: Using VBA in your own Excel workbook, have it prompt the user for the filename of their data file,then just copy that fixed range into your target workbook (that could be either the same workbook as your macro enabled one, or a third workbook).Here's a quick vba example of how that works:
' Get customer workbook...Dim customerBook As WorkbookDim filter As StringDim caption As StringDim customerFilename As StringDim customerWorkbook As WorkbookDim targetWorkbook As Workbook' make weak assumption that active workbook is the targetSet targetWorkbook = Application.ActiveWorkbook' get the customer workbookfilter = "Text files (*.xlsx),*.xlsx"caption = "Please Select an input file "customerFilename = Application.GetOpenFilename(filter, , caption)Set customerWorkbook = Application.Workbooks.Open(customerFilename)' assume range is A1 - C10 in sheet1' copy data from customer to target workbookDim targetSheet As WorksheetSet targetSheet = targetWorkbook.Worksheets(1)Dim sourceSheet As WorksheetSet sourceSheet = customerWorkbook.Worksheets(1)targetSheet.Range("A1", "C10").Value = sourceSheet.Range("A1", "C10").Value' Close customer workbookcustomerWorkbook.Close
Data can be pulled into an excel from another excel through Workbook method or External reference or through Data Import facility.
If you want to read or even if you want to update another excel workbook, these methods can be used. We may not depend only on VBA for this.
For more info on these techniques, please click here to refer the article