VBA to BROWSE & COPY Data from SELECTED File in Excel

Use this simple Excel VBA macro to allow the user to browse for the file they need to import or copy the data from. They will get Excel's open File Dialog Box and they can browse and select the workbook to copy the data from. We use VBA GetOpenFilename method to Open the workbook selected in the background, copy the data and then close the workbook. The user will not even realize the other workbook was opened in the background.

👩‍💻 What You'll Learn:
- User Flexibility with GetOpenFileName: Understand how to allow users to select files via Excel's Open dialog box.
- Efficient File Handling in VBA: Learn to open, import, and manipulate data from selected files without hardcoding paths.
- Practical Example: Follow a step-by-step guide to open a file, copy a range, and paste it into a designated area in your workbook.
- Multi-Selection Feature: Discover how to handle multiple file selections for bulk data processing.

🔥 Key Takeaways:
- Customizable File Paths: Free yourself from fixed file paths in VBA code or Excel cells.
- Simple Yet Powerful: Leverage the simplicity of the method for complex file handling tasks.
- Error Handling: Understand how to manage user cancellations and incorrect file selections.

You can expand on this Excel macro to copy data from multiple ranges or multiple sheets or create dynamic ranges.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel #ExcelVBA

[ronvds]

Most people I deal with look at spreadsheets as a glorified calculator and are amazed at what I do for them. And then you come along and I am amazed.

[Matt-zpjg]

For those wanting their copied range to be a bit more dynamic use this.

Range(Selection, Selection.End(xlDown)).Select
Range(Selection,
Selection.Copy

The only problem with this is it stops if there are any empty rows. So make sure to check your data!

LastRow = Sheets(1).Cells(Sheets(1).Rows.Count,
LastColumn = Sheets(1).Cells(StartCell.Row,
Sheets(1).Range(StartCell, Sheets(1).Cells(LastRow, LastColumn)).Copy


This code on the other hand will work and count data even if there any empty rows between data. I'm still trying to incorporate this code into her macro to work so far no luck haha!

[leonallen]

I’m getting into excel and love this stuff. Thank you.

[Poland]

Great video, actively using it currently. I only had one question regarding the macro: is there any way to change the default file open location to be context sensitive? For clarity: is it possible running this macro to have the default window open up to where the file is currently located in a dynamic fashion? We are trying to do this because we have many different folders with various data sets. I'm trying to think of a way to have the default window open up to the local folder, but be context sensitive. So if the excel file is moved the default dialogue box will change depending on the location of the workbook. Thanks in advance for anyone that can help.

[djdisasterjames]

These lessons are so awesome, you really dive into every little detail and explain it so clearly :) Dim as String --> False = "False". Dim as Variant --> False = False :D I was baffled by that once, no doubt others also benefited from this!

[Jihad-fe]

Very nice explanation, bless your efforts

[shabbirkanchwala-abwaab]

Very useful hints
B4 I never thought that such can be done in excel
I always gain a lots of practical knowledge from your Vdo
Simply U R Gr8!!!

[merbouni]

Well, in less than a year, you've become the number one as reference ... Everything is useful in your YouTube channel..😍

[buradagunta]

Hi, this is very helpful. would you explain considering the csv file instead xlsx file, copy data from sheet 1 of csv file opened and paste in the current workbook. Thank you

[MrKallemyran]

This is something I'll actually use so thank you. And also You strike me as very pedagogical so I'll have a look-see if your courses are something i can take parallel to my work.. Fascinated by excels power. Mostly becaus it lets me "program" some simple things without the need to upgrade my IT security clearance in the organisation. As long as I'm within excel boarders. I'm allowed to build "smartnes" 💡

[Caan]

Hi Leila and thank you very much for your useful videos!
I have a problem with this code - it doesn`t work on MAC.
Do you have a solution for this ?
Thank you!
Best regards!

[ruteshrudra]

Thanks for this video and it is really helpful but how to create data should select and copy data dynamic

[Thurmanatr]

Never a dull video! 🙂Thank you for sharing your expertise with Analysts across the Corporate world. 😃

[lephterisp]

A whole new(amazing) world hidden behide Excel .Learning VBA is the next target for me.Thanks again for all you give to us .

[matthewmusselwhite]

You are truly amazing and a very gifted teacher. Thank you for all you do!!

[mellowkoopa]

This came up in my Google news feed, like sweet nectar of information magic'd down from the heavens to solve a problem I didn't know I had. Thank you so much for your very clear and concise tutorial. Subscribed.

[alexpspa]

amazing!!! do you have a video on how to import multiple files?! 😊

[andyoverington]

Thank you. Finally someone who explains this stuff very well?

[coffeecuppepsi]

If someone doesn't like spreadsheets its just that they haven't seen this channel

[craiggibson]

I’m doing the VBA course at the 😆moment. Very impressive, well structured and well explained stuff. Great job Leila 👍