Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

Question

We need to write an Excel spreadsheet with VBA code in it; the code reads and performs operations on the data in the first worksheet.

The user will be receiving spreadsheets containing data but that do not contain the VBA code. We need to be able to import the data from the spreadsheets containing the data into the spreadsheet containing the VBA code automatically. The worksheets containing the data have the same column format and datatypes as the worksheet of the spreadsheet containing the data.

Ideally, you would open the spreadsheet containing the VBA code, be presented with a UI allowing the user to navigate to the spreadsheet containing the data, click OK and the data will be imported.

How would you go about doing this? It has to be done using VBA in Excel spreadsheets.

Many thanks.

User · Answer

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

User · Answer

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 Workbook Dim filter As String Dim caption As String Dim customerFilename As String Dim customerWorkbook As Workbook Dim targetWorkbook As Workbook    make weak assumption that active workbook is the target Set targetWorkbook   Application ActiveWorkbook    get the customer workbook filter    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 workbook Dim targetSheet As Worksheet Set targetSheet   targetWorkbook Worksheets 1  Dim sourceSheet As Worksheet Set sourceSheet   customerWorkbook Worksheets 1   targetSheet Range  A1    C10   Value   sourceSheet Range  A1    C10   Value    Close customer workbook customerWorkbook Close

[excel] Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

The answer is

Examples related to excel

Examples related to vba

Examples related to import

Examples related to spreadsheet

Tags