Get In Here! Importing Data into an Excel Sheet using VBA

Remember this guy?

Pikotaro had an apple and a pen. And in 2016, he demonstrated his ingenious way of leveraging the two to create the Apple-Pen.

Now while that's impressive, have you ever wondered how you could take Data in one Excel Sheet and put it into another Excel Sheet?

I'll do you one better, how a button that does it all for you??

Using Excel VBA, we can automate the process:

First we'll need a button, so from the Insert Tab, let's create a Shape and make it look fancy.

2. Next, we'll right click to Assign a Macro

3. We'll make a New Macro and use the following Code:

sFile = Application.GetOpenFilename()

Set wb = Workbooks.Open(sFile)

wb.Worksheets(1).Range("A1").CurrentRegion.Copy

ThisWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues

wb.Close

Code Explained Below.

And that's it! Now you can click the button and you will be prompted to select a file!

Simply select your file and the data will be imported!

It's like magic! I'd even say almost as magical as the Apple-Pen.

Code Explained for Nerds:

sFile = Application.GetOpenFilename()

First, we'll use GetOpenFileName from the Application to present a file dialog that will return the path to the selected file.

Set wb = Workbooks.Open(sFile)

Next, we'll open the Workbook using the path we obtained above.

wb.Worksheets(1).Range("A1").CurrentRegion.Copy

Then, we'll copy all data from the first Worksheet in the File starting from A1

ThisWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues

wb.Close

Finally, we'll paste the data into A1 of the first Worksheet in our Workbook and close the file we opened.

Recent Posts