How to Import Text (.txt) Files Into Excel - Excel VBA Tutorial

No video

How to Import Text (.txt) Files Into Excel - Excel VBA Tutorial - Awesome!!

Рет қаралды 212,571

Күн бұрын

📊 Free Workbooks: www.excelvbais...
🥷Join Excel Ninja Pro: www.excelvbais... Months FREE On Annual Plan Auto Applied)
🥷Excel Ninjas FB Group: www.excelvbais... (Free downloads, Trainings, Live Q&A and more)
In this lesson, we learn to open text (.txt) files in excel using VBA. We open them into different columns depending on whether they're comma separated or delimited some other way, such as TAB delimited.
If you use text files a lot, you may want to build macros based on this method of opening them, using Workbooks.OpenText etc. . .Check it out! You can utilize this method with some custom import/export functions that can allow you to automate even more of your work! Thanks for watching!
Fantastic Developer Tools:
🔒 Transform Any Excel File Into A Locked EXE: www.excelvbais... (25% off with code ‘25OFF’)
🟡 Create Custom Installers: www.excelvbais...
👋 Business Inquiries, Consulting, Comments, etc: www.excelvbais...

Пікірлер: 75

@ExcelVbaIsFun 11 жыл бұрын

That's a great point, James! In this case, we didn't want to create a connection or query to a db or even in this case to a txt file that may try to update. We simply wanted access to the data and then to sever the connection to that data immediately once we gleamed the info. You're right, there are sooo many ways to skin these Excel cats! Thanks for your comments, keep them coming sir! Dan :)

@ExcelVbaIsFun 10 жыл бұрын

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

@johnbismarksesay577 6 жыл бұрын

Very helpful, the videos contributed to my capacity as a professional. A big thank to the providers

@ExcelVbaIsFun 11 жыл бұрын

I'll have to research this myself. After I have wrapped my head around this, I'll make a vid for ya! Thanks for your comments as always jen! Dan

@Sobolev514 11 жыл бұрын

As usual very helpful video. Thanks a lot Dan!

@bpatmurray 9 жыл бұрын

Thanks Dan, great video. I have a question, is there a way to create a macro to automatically collate data from a numerous number of other excel documents, without going through and showing it what to do for each one? I want to automate the automation. Any suggestions? Thank you.

@realesticetea6241 3 жыл бұрын

Great video, thanks

@kiranpavuluri5416 10 жыл бұрын

Great job Dan, Thanks for videos

@ExcelVbaIsFun 10 жыл бұрын

Thanks Kiran! Appreciate it! Dan

@ksrinivas1729 8 жыл бұрын

Sir your contents worth millions of Rupees(for us)

@anupammandal2697 11 жыл бұрын

Thanks a lot Dan..u r awesome!

@ExcelVbaIsFun 11 жыл бұрын

Thanks Anupam! You too!

@sujatha.arunachalam 10 жыл бұрын

It was very informative video.I am a beginner and want to learn VBA-Excel , so please provide other tutorial video. Thanks

@ExcelVbaIsFun 10 жыл бұрын

No problem, Sujata, I have about 200 videos currently. i recommend starting here at the basics series. Excel VBA Basics #1 - Getting started with Range object, running and debugging Thanks for the comment and the "Like"!

@lamarbess2281 5 жыл бұрын

Thanks great JOB!!!!!

@orlandopalone80 4 жыл бұрын

Very good

@ExcelVbaIsFun 4 жыл бұрын

Thanks, orlando palone!

@roopashreevenkatesh2350 6 жыл бұрын

Thank you so much for this video😊

@ExcelVbaIsFun 6 жыл бұрын

Hi Roopashree, you're very welcome! Thanks for the comments!

@ExcelVbaIsFun 11 жыл бұрын

uploading a video on how to open a link in Firefox, Chrome or internet explorer. Hope this helps you, you were my inspiration for this vid. Thanks!

@mrabalone3492 5 жыл бұрын

i love you Sir

@chaitanyagaddam8407 7 жыл бұрын

good one Dan:)

@Myrslokstok 11 жыл бұрын

You are a excel genius. How would you crate a hyperlink that opens in a nother webbrowser like Firefox, when Explorer do not work and is not certified to use??? Thanks Michael!

@PramodYadav027 8 жыл бұрын

Good one Dan :). I was looking for this today and happy to see a detailed video on the same :). Thanks for sharing the knowledge! Are you still active with this stuff? I see your last videos are around 1 year old. Cheers !

@ExcelVbaIsFun 8 жыл бұрын

Thank you! yes, unfortunately I've been slammed with much more responsibility at my new job, in addition to running a couple businesses and software consulting. Also, I've got 25000 students on Udemy.com with my complete paid courses, whose questions keep me busy. lol. if you have any questions, the best way to reach me nowadays is through the contact form on www.excelvbaisfun.com , as it goes to my email. thanks! dan

@PramodYadav027 8 жыл бұрын

ExcelVbaIsFun Wow! 25,000 ! I am sure you have a lot up your sleeves than :). I have noted your website. Will get in touch when I need help :). Thanks again and all the best !

@tj9382 3 жыл бұрын

Hi is it possible to loop through a word pad file extracting specific criteria based information and adding it to an excel spreadsheet? Thanks 🙏

@bombay24 8 жыл бұрын

Hi Dan. Great video. Is there a way to set up a checksum or something similar to ensure that all data from each .txt file has been imported?

@jen110987 11 жыл бұрын

Thanks Dan. I use txt file a lot. Can you do a macro using excel in microsoft outlook? Like sending emails.

@scdeanorki5 9 жыл бұрын

Thanks. That is awesome! How do you import text into an open workseet? I tried the ActiveWorkSheet.QueryTables.Add but where my problem lies is that the File path I want to add is a variable which is concatenated with the filePath of the workbook and the text file name (i.e: myVarPath + "fileName.txt"). I can't seem to get the ActiveWorkSheet.QueryTables.Add constructor to use my variable. Any ideas?

@lhow3100 10 жыл бұрын

As a project I've taken on, I am trying to figure out how to import data from a Word Document form into Excel. Unfortunately, because there is a lot more text in the word document besides the information that I need, it is very difficult to extract what I need to. For example, the form says, "Full Name" and then there is a spot for the individual to put their name. It then says, "Preferred Name" and then there is a spot for the individual to put their preferred name. I only want the data entered by the individual to be imported. How can I do that? Your videos have been a great help! Thanks

@rakhisaxena4352 4 жыл бұрын

Hi Dan thanks for this I work in garment industry and there are many approvals and discussions so is it possible to embed an email as an attachment in excel automatically?

@ExcelVbaIsFun 11 жыл бұрын

cool! Do you have any tips for us since you already use this system? Dan

@jayricks8938 9 жыл бұрын

OK quick question possibly stupid but how would you take this macro and keep adding on to it. Like I want to combine multiple text files into 1 excel file just like its done in this video

@simonjoserodriguezbrito423 4 жыл бұрын

I have a question. In listbox the colour blue that select items I want to change for other colour. Can I change it. and If is posible, How I can do it. Help me please.

@07lacha 8 жыл бұрын

hey Dan thanks for sharing! i have a question though, is there anyway to make the macro import the text on a specific sheet in a certain excel file?

@Redtorres16 9 ай бұрын

How to extract text or specific text only from notepad to Excel using VBA

@bagusyudistira3802 7 жыл бұрын

God Bless you man, this video save my ass

@orlandolcd 8 жыл бұрын

How would you charge to put commas on a colum? Thank you

@kennyowz 7 жыл бұрын

Is there a way to do this, with PDF files? I've found a way to get the data I need from manually saving a PDF file as an Excel file, with the help of some Excel formulas after that. But I'd like to have the PDF-Excel conversion run automatically when I click on a button or something. OR! Is there an easier way to get data from a PDF file (only specific data/text/nums) with VBA? THANK YOU =)

@spoortirawat8693 4 жыл бұрын

Hi, how can I do the same for multiple text files.

@allento8139 7 жыл бұрын

Thank you

@ExcelVbaIsFun 7 жыл бұрын

+Allen To Very welcome!

@allento8139 7 жыл бұрын

thank you

@jeewankoirala7200 9 жыл бұрын

how to move the content in html file. like when we open it it appears moving. like that so plz provide me

@tnyoga 10 жыл бұрын

Hello Dan..Thanks a lot for this video... I would like to learn more about the VBA programs you have explained in this video. But i could not able to see the text clearly after run around 7 mins. could you please help on this, so that i can go through full video with clear text. please help me understand your information. Thanks in advance for your help!

@ExcelVbaIsFun 10 жыл бұрын

yogesh, Make sure you click on the gear symbol (Settings) in the lower right hand of the screen and set the quality to 720p (HD). Then make sure to put in large player mode OR even fullscreen. That should make it crystal clear. Yours, Dan

@tnyoga 10 жыл бұрын

Thank You Dan.

@ExcelVbaIsFun 10 жыл бұрын

ExcelVbaIsFun You're very welcome!

@krystlepostle4755 8 жыл бұрын

how to take a Script Text File ] and turn it into Tweets..please explain how to do this

@firstname4337 11 жыл бұрын

why didn't you use the Data tab / Get External Data ? was it just because you want to show the macro ?

@clayman648 7 жыл бұрын

Is there a way I can get my Music.txt file with all play count and ITunes info into an .xml or ITunes library.ltd And import all the info from Music.txt so it can be in that order with dates etc?

@ValmisFilm 4 жыл бұрын

23:21 - it does open C hard disc, but for some reason not let´s say my second hard drive F: . Why is that? It just opens the last place that was opened.

@prakashbh8619 6 жыл бұрын

Hi I am working in Accounts Receivable team were i need to download 21 different bank statement and copy and pest all bank statement in Excel on daily bases Is there any way to import these statements Directly from Bank Website to Excel Help Me out please

@kostasfilippopoulos8737 7 жыл бұрын

Hello guys, I download PPC Auto Report file in txt. document and it I see asterisks * Does anyone know how can I see the keywords behind these * ? Is it something I have to convert to on my Excel (where I insert the txt.) or there in the txt. file? Thanks anyways!

@ALEXSHUNCAI 5 жыл бұрын

this is just one file on the desktop, what if you want to use VBA to open multiple text files? how to loop them

@MarcoTorres4given 5 жыл бұрын

what if I have more than one text file with different names

@prakashbh8619 6 жыл бұрын

I am working in Accounts Receivable team were i need to download 21 different bank statement and copy and pest all bank statement in Excel on daily bases Is there any way to import these statements Directly from Bank Website to Excel Help Me out please

@ashwanisharma8597 4 жыл бұрын

Sir is main coloum wise Nahi as Raha hai

@idmansabir 8 жыл бұрын

docReader to Excel?

@PrakashPraaku 7 жыл бұрын

Hello sir, how can we get mail content data into excel file using VBA, the mail content format will be like, Date, Company Name, Total Expenses, Spending Date, Location. These are the headers, and below this there will be some 20 rows of data, How to get this into Excel and save in a specific folder with the mail subject line name automatically, This will be a great help sir, Regards, Prakash, Bangalore, India

@ExcelVbaIsFun 7 жыл бұрын

Hi Prakash, Not sure what you're looking for. What do you mean by mail content? Thanks Dan

@Myrslokstok 10 жыл бұрын

Thank you, When I try to follow your eminent tutorial it does not work. The macro does not record all the parts, when I run it again, just paste it into all values straight up and down without separation in different columns. Think formats election efterråt has something with it all to do. If I do it right and then select the text will return the worksheet to a normal Ctr + V paste. It is strange that it does not record everything that I do. Beloow is the macro code, do not worry about the first few rows: Sub Makro3() ' ' Makro3 Makro ' ' Sheets("ImportAgressoKKKONTO").Select Cells.Select Selection.ClearContents Range("A1").Select ActiveSheet.PasteSpecial Format:="Unicode-text", Link:=False, _ DisplayAsIcon:=False End Sub Thanks again!

@AlinGaoChannel 11 жыл бұрын

Hi Dan, do you have an email address so I can send my question and a sample spreadsheet to you

@MyVasant 8 жыл бұрын

My .txt data should convert like this in excel? can anyone help me SCID00 - PI1 SCID01 - P01 SCID02 - IE5 KRW,5 HUF,0 LMV,3 KRW,2

@Jake-hk2uq 7 жыл бұрын

lol that says JP Morgan

@sabirmolla1880 6 жыл бұрын

চ্্

@sabirmolla1880 6 жыл бұрын

চ্্েবকেে

@ExcelVbaIsFun 6 жыл бұрын

Thanks for the comment!

@ALEXSHUNCAI 5 жыл бұрын

actually, you didn't touch the real stuff, just record macro and put up on the youtube, everyone can do this, what you should do is to put up multiple text files at the certain directory, then from there, show the audiences how to open them all at one go, turn off that damn music at the end, it is not super awesome video to me, it is just the simple shit and you thought it was the greatest achievement.