VBA to BROWSE & COPY Data from SELECTED File in Excel
Вставка
- Опубліковано 25 вер 2019
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/vba-select-...
I will also show you how to avoid getting the message "method getopenfilename of '_application' failed". This is a common error when using the application.getopenfilename method and it can easily be avoided by declaring the correct variable type.
👩💻 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.
Check out the FULL VBA Playlist: • Excel VBA & Macros Tut...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
Grab the file I used in the video from here 👉 pages.xelplus.com/vba-select-file
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.
I'm glad I can still surprise you :)
I really learn new things every time I watch your videos. Thanks a lot! :D
A whole new(amazing) world hidden behide Excel .Learning VBA is the next target for me.Thanks again for all you give to us .
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!
I'm glad the tutorials are helpful for you James.
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!!!
Never a dull video! 🙂Thank you for sharing your expertise with Analysts across the Corporate world. 😃
I'm glad if my videos are helpful in the corporate world :)
Thank you. Finally someone who explains this stuff very well?
I’m doing the VBA course at the 😆moment. Very impressive, well structured and well explained stuff. Great job Leila 👍
That's great Craig. I'm glad you like the course. Many thanks for the feedback!
You are truly amazing and a very gifted teacher. Thank you for all you do!!
Best 👏 Channel 👏 EVER!!! 👏
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.
You're very welcome. I'm glad to have you here.
Woah! Just what I needed. Thanks! :D
excel-lent as always, Leila is stunning 👍🏻
As always, excellent Top-Notch Pure explanation...
Very useful and practical. Thank you for the great material!
Thanks this really helped me out. Clear and not boring!
Well, in less than a year, you've become the number one as reference ... Everything is useful in your UA-cam channel..😍
Thank you, that makes me very happy :)
Hi Leila. This video has really helped in one of my excel project of my process flow. I sincerely appreciate you for making this video. I would have loved to share my automation made out of this information, but, considering confidentialty, not possible. Thanks again for making and sharing. Will also go through your other videos.
Great!!!
It is exactly what I was looking for!!!
You are the best!!!
Awesome job, Leila! I can see myself using this in the future.
That's good to hear Bobby.
Amazing as always.
Thanks! Always very helpful!
For those wanting their copied range to be a bit more dynamic use this.
OpenBook.Sheets(1).Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
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, StartCell.Column).End(xlUp).Row
LastColumn = Sheets(1).Cells(StartCell.Row, Sheets(1).Columns.Count).End(xlToLeft).Column
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!
ThisWorkbook.Worksheets("HW15").Range("A1").PasteSpecial xlPasteAll
At this part, I am encountering error, that highlights this as yellow.
Can somebody help?
While my colleague uses the same code, but works fine.
@@danieljosiahquijano5659 Could be many things: possible your code has not selected anything to copy. Paste will fail if nothing copied. Your range is different than your paste area.
Also better to use this: high lighting entire column a even Blanks.
Dim rng As Range
Set rng = Range("A2", Range("A" & Rows.count.End(xlup).Address)
rng.Copy
Range("B2").PasteSpecial xlPasteAll
@@danieljosiahquijano5659 Did you figure it out? I'm have the same problem :/
@@johnhayse7147 I did, when you are recording a macro, under "Store macro in", select dropdown "This Workbook". it is a very specific code.
@@danieljosiahquijano5659 Thanks!
beautiful mind , and good teacher , thank you leila .
@tha2ir Talib beautiful teacher, and good mind, thank you leila .
It is great!!!
Thank you so much for this very valuable information.
Wow
I was actually using "Path" option in excel all this long...this really help...Thanks
Very good Leila!!! than
ks for this video!!!
absolutely awesome thanks. Actually just what I was looking for thanks!
My pleasure Niels. Glad to help.
This is a kind of VBA idea i was searching for. Thanks. I have subscribed to this channel as well. Thanks a million.
Glad to help. It's great to have you here.
Your channel is brilliant!
I learn so much. Thanks!
Happy to hear that!
you are just awesome
Thanks! I was able to use your method perfectly :)
Excellent.. Thank you very much for sharing your valuable knowledge with us Leila. 👍 👏 🌟
You're very welcome. Glad you like it.
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
Thank you for the valuable information, i have purchased all of your courses from udemy. They are so very helpful
Thanks for usual support in Excel every day learn new topic
You are my teacher madam, i learned lot from you
हर बार कुछ नया ,धन्यवाद लीला
I'm trying my best :)
Awesome video as always....
TYSM for such an informative instruction, help me a lot 🥰
Thanks for this useful lesson, you are the best 👏👏
Very informative.
Gonna try it later.
You are the best ever.... life saver oh my gosh
Great help thank you 😊
Excellent! Thank you :)
Wonderful video!
Thanks Leila, I got your VBA videos.
That's great Rahul. Thanks for your support.
Very useful ❤. Thank you so much 😊
Just amazing!!
Really good!
Thanks for video!
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" 💡
I know exactly what you mean Thomas. Been in the corporate world myself for quite a while trying to navigate around the IT guidelines :)
@@LeilaGharani ha ha Don't say it so loud ;) anyway... I have enrolled.. Now I have to find some time ⏳
Nice tutorial mam. God bless you
Hi, i really like how easily and precisely you explain things. I have a query though, what should i do if i want to get data by opening a link which is a link to excel sheet and not from any worksheet which is saved in my PC.
Very useful thanks a lot, Keep it up.
Hi Leila. Great tutorial! Thanks for sharing this :)) Thumbs up!!
Thank you! Cheers!
Great! Thank you Leila!
My pleasure.
Great Video 👍🏻.. thanks for sharing
Thanks for the lightning fast comment Faraz :)
Thanks, nice and helpfull.
Very much grateful Leila.
You're very welcome Robert!
BIG THANKS LAILA
Amazing! Really helpful :-)
great video!
u r my excel hero
You rock the item. Super
Finally got this macro.. Thanks a lot...
Glad to help :)
Incredible, this is exactly what I was just searching for! So much thanks! :))>
My pleasure Bee. Glad to help.
Helpful. I didn't know that method existed.
Between this and ADO....these are my two faves to impress the non-technical
another creative ideas ☺👍👌
Thank you so much!!!!
Wish I could do that :) :) I am enrolling to your course for sure.
Amazing, this saved me so much time :)
Great to hear!
Awesome!
nice job! thanks :)
Thanks a lot. it is usefully for me
Thank you so much for this video. How can I adopt this script for importing a complete csv file and pasting it in a specific cell?
Thanks for your excel related content. I was wondering, can Google sheets open (and run) excel files containing macros (buttons, for instance)?
Thank you so much
Excelent. Thanks :)
Very nice video
Hello good night your video is very well explained, I just wanted to ask a question: How can I make a filter inside the files that opened and copy the data from the filter result?
This is great!
Thanks Chris, glad you like it.
thanks very much
Hi Leila, thanks for the great video's I generally watch all of them, I have a small problem, I get Excel files from different members of which I open and then import into my Excel data, the problem is they have a verity of extensions such .xls, .xlsx,.xlsm is there a way to be able to use with an open workbook, your comments would be much appreciated
Regards..Don.
thanks so much
If someone doesn't like spreadsheets its just that they haven't seen this channel
So true!
Or because spreadsheets are the wrong tool for the job most of the time
I am trying to find a VBA code that will find a file utilizing where only the first six characters in the file name are always same and the file name ends with YYYY.MM.DD.xlsx.
now my file is under 2020 folder and have to create seprate folder for each monts like Jan, Feb and Mar under 2020 folder, then in the each month file we have to change and create file on daily basis
like this _ Y:\C & C FEES\Suresh\anbu 2020\04_Apr 2020\InvoicesDD.MM.YY(daily need to change the file date and monthly once change the month).
very interesting 😀
hi, thank you for lots of tutorials you made. appreciate if you continue or added the program to paste on the last row please. TIA.
Hi Leila! Thanks so much for this video. I followed this pretty much step by step, but I'm getting a 'subscript out of range' error. I'm looking at the documentation to understand this error in this context, but I'm not comprehending. Do you have resources or tips on how to debug this?
Wonderful
Thank you!
My pleasure :)
Well done. Very easy to follow and understand. How would handle a dynamic range in OpenBook?
Wow thank you mam ,it's helping me on my work Thanks Once again
My pleasure, glad to help.
Very nice
Thaks you!!
Dear Leila
Thanks for useful videos.
Please help me to find out how I can import file like jpg that start from cell for example left top corner of picture comes from cell A10.
Thank you #LeilaGharani #ExcelQueen for sharing this wonderful way to open files. I am a big fan of yours ( more than of Excel )
I'm happy to hear that :)
Fantastic video. Exactly what I need. After the user selects the file, is there a way for the user to select which data he/she would like to copy into cell A10?