VBA to BROWSE & COPY Data from SELECTED File in Excel

Рет қаралды 323,969

4 жыл бұрын

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

Пікірлер: 359

@LeilaGharani 5 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/vba-select-file

@ronvds4713 4 жыл бұрын

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.

@LeilaGharani 4 жыл бұрын

I'm glad I can still surprise you :)

@djdisasterjames 4 жыл бұрын

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!

@LeilaGharani 4 жыл бұрын

I'm glad the tutorials are helpful for you James.

@lephterisp 4 жыл бұрын

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

@craiggibson8547 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

That's great Craig. I'm glad you like the course. Many thanks for the feedback!

@merbouni 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Thank you, that makes me very happy :)

@shabbirkanchwala-abwaab6263 4 жыл бұрын

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!!!

@olibolivar7223 4 жыл бұрын

I really learn new things every time I watch your videos. Thanks a lot! :D

@mellowkoopa 4 жыл бұрын

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.

@LeilaGharani 4 жыл бұрын

You're very welcome. I'm glad to have you here.

@lifeinfluenceramitkachalia 4 жыл бұрын

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.

@Thurmanatr16 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

I'm glad if my videos are helpful in the corporate world :)

@Matt-zp9jg 3 жыл бұрын

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!

@danieljosiahquijano5659 Жыл бұрын

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.

@Matt-zp9jg Жыл бұрын

@@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

@johnhayse7147 Жыл бұрын

@@danieljosiahquijano5659 Did you figure it out? I'm have the same problem :/

@danieljosiahquijano5659 Жыл бұрын

@@johnhayse7147 I did, when you are recording a macro, under "Store macro in", select dropdown "This Workbook". it is a very specific code.

@johnhayse7147 Жыл бұрын

@@danieljosiahquijano5659 Thanks!

@matthewmusselwhite591 Жыл бұрын

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

@andyoverington712 3 жыл бұрын

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

@shrutiagarwal936 4 жыл бұрын

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.

@buradagunta1 3 жыл бұрын

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

@vishalpatil4102 3 жыл бұрын

Thank you for the valuable information, i have purchased all of your courses from udemy. They are so very helpful

@dhruvakerkar6956 3 жыл бұрын

Wow I was actually using "Path" option in excel all this long...this really help...Thanks

@jonathanjesusdelvillarrebo7429 4 жыл бұрын

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?

@vpt262 4 жыл бұрын

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?

@hddevani 4 жыл бұрын

As always, excellent Top-Notch Pure explanation...

@MrKallemyran 4 жыл бұрын

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" 💡

@LeilaGharani 4 жыл бұрын

I know exactly what you mean Thomas. Been in the corporate world myself for quite a while trying to navigate around the IT guidelines :)

@MrKallemyran 4 жыл бұрын

@@LeilaGharani ha ha Don't say it so loud ;) anyway... I have enrolled.. Now I have to find some time ⏳

@smohanv 4 жыл бұрын

This is a kind of VBA idea i was searching for. Thanks. I have subscribed to this channel as well. Thanks a million.

@LeilaGharani 4 жыл бұрын

Glad to help. It's great to have you here.

@kurtauerbach5883 2 жыл бұрын

Thanks this really helped me out. Clear and not boring!

@enriqueblanco8784 2 жыл бұрын

Thanks for your excel related content. I was wondering, can Google sheets open (and run) excel files containing macros (buttons, for instance)?

@ashrafgharsan553 3 жыл бұрын

Very useful and practical. Thank you for the great material!

@totallycybertechnologies1631 4 жыл бұрын

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?

@EJtoU 3 жыл бұрын

Help please :) I keep getting “Application or Object-defined error.” At the line “FiletoOpen=Application.GetOpenFilename(Title:=“Browse for your File & Import Range”,FileFilter:=“Excel Files(*.xls*),*xls*”). Yes, I did dim FiletoOpen as Variant. The same error happens if I use the actual file path inside GetOpenFilename(). Any ideas?

@14Lewiss 3 жыл бұрын

For “PasteSpecial” how do I keep the leading zeros from the CSV file. I need the zeros in the front. I tried formatting the sheet to Text before importing but zeros are lost.

@smith28c 4 жыл бұрын

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.

@JG-fg1ye 4 жыл бұрын

excel-lent as always, Leila is stunning 👍🏻

@jmstampe 4 жыл бұрын

Between this and ADO....these are my two faves to impress the non-technical

@spunjbom 4 жыл бұрын

Awesome job, Leila! I can see myself using this in the future.

@LeilaGharani 4 жыл бұрын

That's good to hear Bobby.

@aelina1588 Жыл бұрын

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?

@mtabbasi 4 жыл бұрын

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.

@TheWinswill 4 жыл бұрын

Need help... I've a file wherein I want to just copy and paste my data to another file irrespective of the file name present. I would really appreciate your help on this. Regards.

@sasavienne 4 жыл бұрын

Excellent.. Thank you very much for sharing your valuable knowledge with us Leila. 👍 👏 🌟

@LeilaGharani 4 жыл бұрын

You're very welcome. Glad you like it.

@vaishalikhedlekar3733 3 жыл бұрын

This is really great !! But would like to understand how can we apply filter on the selected file based on a certain criteria and copy only the filtered data on the macro workbook.

@vengamanaidu996 4 жыл бұрын

You are my teacher madam, i learned lot from you

@mielkew27 4 жыл бұрын

As always simple and easy to follow. But how can we clear the Clipboard as I'm getting notification every time I run the Macro.

@tha2irtalib343 4 жыл бұрын

beautiful mind , and good teacher , thank you leila .

@craiggibson8547 4 жыл бұрын

@tha2ir Talib beautiful teacher, and good mind, thank you leila .

@stevepyrdeck936 4 жыл бұрын

Leila,I am looking for help with dependent drop down list.How do I make the (second list) dependent list, have multi selection checkboxes?Thanks, SteveBTW your videos are Great!

@edgovan1 2 жыл бұрын

Is there any way of skipping rows? For example, conditional on whether data in a column of the range in the source data has "skip" written in it. Thank you for an excellent video - subscribed.

@JuzSayWooo 3 жыл бұрын

Thank you so much! I literally learn VBA from you and I have no background before this. Can I ask, what does the “title” argument actually do? I don’t get that part...

@GrahamSchroder 4 жыл бұрын

Your channel is brilliant! I learn so much. Thanks!

@LeilaGharani 4 жыл бұрын

Happy to hear that!

@prakharsrivastava7795 Жыл бұрын

Thank you so much!! Extremely helpful video!!

@twgrisham 4 жыл бұрын

Well done. Very easy to follow and understand. How would handle a dynamic range in OpenBook?

@gummadinagalakshmi4083 3 жыл бұрын

Thanks it's help me somehow.but I am seeing for same sheet I need to count the value having NA and seperate the count to another sheet.can you advise.. Thanks 😊

@augustodemelo915 3 жыл бұрын

It is great!!! Thank you so much for this very valuable information.

@gutobrasiliabrasil 2 жыл бұрын

Great!!! It is exactly what I was looking for!!! You are the best!!!

@jjewel6812 3 жыл бұрын

Fantastic tutorial as always. Is there a way to Copy a range and paste it in an other workbook, while adding the Values in that Range with the Value in the Cells, separated with a divider. For example "| ". So if a Cell holds the Value "ABC|". And the Cell to Copy holds the Value "MNO", The result should be "ABC|NMO|". And that with all the Cells in the Range.

@ibrahimriyadhalrikabi757 3 жыл бұрын

Hi dear , I've question if possible: I need strongly to share my VBA project with many users the same time (at least 20 users) Please advise me 🙏

@alexpspa60 3 ай бұрын

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

@Things-Recycleing 4 жыл бұрын

Thanks for usual support in Excel every day learn new topic

@Ca7a1n 2 жыл бұрын

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!

@michelacosta9886 Жыл бұрын

Very good Leila!!! than ks for this video!!!

@arere9948 10 ай бұрын

TYSM for such an informative instruction, help me a lot 🥰

@sanikananoti1702 3 жыл бұрын

Hi, the video really helped. I need a little help with the code. For my application i need to upload the file ( mostly PDF) and save it in a folder like we do for an image in userform. How should I excatly go about it?

@thomasfetter 11 ай бұрын

you are just awesome

@AjayGupta-xm4hr 4 жыл бұрын

Hi Leila, can you guide me how to open a zip file thru excel power query. The external data from website, that I am trying to get into excel table using power query is in zip format, so I do not know how to unzip it. Inside the zip there is a csv file, which I actually need but zip format is becoming blocker for me. Thanks.

@briangilbride 3 жыл бұрын

is there a video that details how to edit this further? I'm looking to open a file, filter a pivot table in that file, copy the filtered data, then paste that into the original workbook

@swapnilsakpal2504 3 жыл бұрын

Awesome video ma'am..much needed. iTs working for me. Thanks a lot. going one step ahead, I've a small huddle. I need to use index match formula within the same code, I tried but failed. Instead of defining the cells from source file. i need macro to use index match function and get the result in active sheet. Could you please assist me. Thank you so much in advance

@junangeles Жыл бұрын

This is what I've been looking for for my reporting! thanks so much. How about before pasting to destination sheet, a prompt or option to select the new range will be prompted or even paste data to the next blank row below the 1st copied file, is that possible? pls. thanks

@olibolivar7223 4 жыл бұрын

Woah! Just what I needed. Thanks! :D

@jbalmanza7537 3 жыл бұрын

Hi do you have a sample macro that can open a folder and update the data files inside that folder and after updating the files open another folder and do the same thing... please I hope you notice this. Thank you

@angelinam6855 4 жыл бұрын

Hi Leila, it would be great to see VBA on an automation to pasting information from Excel or Word to outlook email

@johnborg5419 4 жыл бұрын

Wish I could do that :) :) I am enrolling to your course for sure.

@stephanie_ong 3 жыл бұрын

Thanks! I was able to use your method perfectly :)

@sanjaybarge5487 4 жыл бұрын

I want to import specific two or three column data from other work book and keep auto update please guide how

@civilworks3591 4 жыл бұрын

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.

@rickkeev4919 4 жыл бұрын

Is there a way to do this using ADO? To bring in from multiple files?

@FanJapanStudios 4 жыл бұрын

Best 👏 Channel 👏 EVER!!! 👏

@anomolyreport 4 жыл бұрын

And here I thought openfiledialog and filesystem objects are the only ways to get file names.. Thank you!!

@georgemedhat375 4 жыл бұрын

hello can i select and merged files exel in one file i have more 100 files i need to open all of the files access or vba or excel

@mhmdseadawy8228 2 жыл бұрын

Thanks for this useful lesson, you are the best 👏👏

@nielskock3221 4 жыл бұрын

absolutely awesome thanks. Actually just what I was looking for thanks!

@LeilaGharani 4 жыл бұрын

My pleasure Niels. Glad to help.

@sanketnarkhede2585 3 жыл бұрын

Hey, If I take filename as input and I've to split the filename and rename it , how can I do , could you please explain

@arunkumarnair001 4 жыл бұрын

Can u please help in copying two non adjacent columns and pasting in another workbook?

@ridgmont61 4 жыл бұрын

I have done this to get data from a CSV file - but there seems to be a problem when dates are copied from the CSV file into the main worksheet - the dates are interpreted in USA format (mm/dd/yy) and not the local format (dd/mm/yy) - the dates cannot not be reformatted successfully automatically - any ideas?

@susmithasunil6611 3 жыл бұрын

Hi Leila how we can get the list of files from one folder and then password protect it in VBA. Many thanks

@everythingandanything4102 4 ай бұрын

Hi Leila, great vids as always, however, i have a question, I am getting a run time error 9, 'subscript out of range' on line 9 ThisWorkbook.worksheets("Selectfile").range("A10").pastespecial xlpastevalues what could be the problem?

@wayneedmondson1065 2 жыл бұрын

Hi Leila. Great tutorial! Thanks for sharing this :)) Thumbs up!!

@LeilaGharani 2 жыл бұрын

Thank you! Cheers!

@atiqsarwar4222 4 жыл бұрын

Hello madam, Hopes you fine... Mam can we form a report like crystal reports, which started with brought forward balance and show debit and credit entries like general ledger... Hope you understand what I am trying to asking...

@sachinkumbhar4097 3 жыл бұрын

Thanks leila ,, but what if we want to copy the data from multiple excel

@francoismarsau6412 Жыл бұрын

Great videos as always. I have signed up for some courses of yours to learm more. You are geat teacher. you mentioned that " and copy specific ranges and import them in our file will be shown later how to do". Can you give example of this or tell me lesson no in you course that explains this as I could not find it. Thank you once again for great courses and video content.

@anonymouscyp4308 3 жыл бұрын

Thanks in advance I don't know what am I doing wrong. When I run the macro, it keeps saying _ error 9" subscript out of range".

@vinaynimje97 4 жыл бұрын

I m following your channel since long. I have learnt a lot. Thank U . I have one question .. i want to update data if condition met i.e. ID, If ID Exists data should be updated but if ID doesn't exist the data should be save in last row of database. Kindly help.

@letme4931 3 жыл бұрын

hello thanks for this lesson i have quistion what is code to get data from another workbook and compare it with this workbook and write T in selected cell if Identical and F if not i hobe help me thank you

@spneerajmishra Жыл бұрын

Great help thank you 😊

@jeffreylaygo8147 3 жыл бұрын

What can I do to pick the last data from column E instead of E20?

@jakeg9711 4 жыл бұрын

Can button reside on a different tab, and the data be pasted in to cell A1? Thanks:)

@rockhammer5334 4 жыл бұрын

thanks for very helpful video. I find that IF VarType(FileToOpen) = vbString And len(FileToOpen) > 1 is better able to handle the different cases.

@donovanscott4311 2 жыл бұрын

So even when FileToOpen is variant type there is an error message when you select cancel. How can I make it to where the user can change their mind on importing data and be able to select cancel without the error popping up?

@pauldhinakaran1121 2 жыл бұрын

Mam , I have to VBA code for open the particular named excel (Master data file ) copy paste all ranges in master data file then close the immediately .Simply saying keep importing the data without open the master data file .

@vinice9222 4 жыл бұрын

Hello MAM instead of 1 excel i want add data from multiple excel from folder and also copy (compile) all available data one by one. Please assist

@samirmohapatra8582 Жыл бұрын

Very useful ❤. Thank you so much 😊

@mc24bk Жыл бұрын

Great video, very easy to understand. How to copy the entire column A & B without setting a range number?

@smaranrai4898 3 жыл бұрын

how can I paste the data through relative cell reference so that the data gets pasted on the next available blank cell.

@girishsawant8263 Жыл бұрын

How can we separate the file name and folder name derived through "Getopenfile" method ? I want to save a new workbook in that folder after processing the data from file that we picked in that folder. For that I need to separate file name from the path... So that I can use this path to save new workbook. Thank You !!