Automated Text Import in Excel with File Selection - The Easy Way

Automated Text Import in Excel with File Selection - The Easy Way - VBA

Рет қаралды 134,983

3 жыл бұрын

MY FULL EXCEL VBA COURSE [60% Discount]: www.teachexcel.com/vba-course...
200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
How to import a text or csv file into Excel using an automated macro and vba - this is the easier, yet robust, way to import text using a macro, with user-selected files or hardcoded file names.
Excel File: www.teachexcel.com/excel-tuto...
Excel Forum: www.teachexcel.com/talk/micro...
This tutorial teaches you how to import text using a macro with a file selection popup window that lets the user choose the file to import and also using a predefined file name so that you can quickly import the file without having to choose it from the computer.
All code in this tutorial is commented and fully explained and I take you step-by-step through the process of building the import macro and showing you how to change it to meet your needs.
This tutorial combines many VBA features, including:
- GetOpenFilename()
- OpenText
- IF Statements in VBA
- Copying values between workbooks in Excel.
Excel Courses:
- VBA/Macro Course: www.teachexcel.com/vba-course...
- Building Professional Forms: www.teachexcel.com/premium-co...
TeachExcel.com
#msexcel #howto #tutorial

Пікірлер: 63

@TeachExcel 3 жыл бұрын

MY FULL EXCEL VBA COURSE (Beginner to Expert) [60% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_EzUTK5hhjWM 200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content

@philipboakes2974 3 жыл бұрын

Really helpful. The explanation is absolutely clear, have been a subscriber for a while now and glad I found your channel. Thanks.

@TeachExcel 3 жыл бұрын

I'm so glad you think so and thank you so much for following me and your nice comment :)))

@BUSINESSRESOURCES130 3 жыл бұрын

Thanks so much for this excellent video. I create Excel systems for subscribers and usually I have them copy/paste input data. Now I can have them import directly from CSV/Text. What I want to know is how to append newly imported data to the first empty row (on the master) automatically. I imagine this might be a more advanced topic.

@roor20 Жыл бұрын

Excellent Tutorial! Well put together and thorough. I do have a quick question. Is it possible to bring the data in without disrupting the formatting with Workbooks.OpenText? I've tried to figure out a way but and getting stuck. Thanks!

@jackiebanta1299 3 жыл бұрын

Hello, i love this video! Very helpful. Is it possible for you to add one more to this coding? Add the CSV file name that was selected in any cell. So when anybody else is looking at the excel file, they know where the data came from.

@pj5151 2 жыл бұрын

Superb! Well documented and explained. Thank you

@juliopaniagua7293 3 жыл бұрын

I am learning a lot from you... but I still need to find a way to keep adding data (from more text files) to the same table... do you have a video about that?

@robertkaniewski3281 2 жыл бұрын

Thank you so much.. Is it possible to import data to a specific row, e.g. no. 10?

@vishp4tel_507 2 жыл бұрын

Thank you so much. Great explanation. This is my first time writing any sort of code and this helped me greatly with my task. Keep up the great work. 😊

@darrylmorgan 3 жыл бұрын

Great Tutorial!! Really Enjoyed The Explanation For The Cool VBA Code...Thank You Sir :)

@TeachExcel 3 жыл бұрын

I'm glad you like it Darryl! I rather enjoyed making this one)

@mateomontoyanaranjo5948 2 жыл бұрын

Thanks mate, this really helped me heaps

@DuyenLe-kt3xx Ай бұрын

A very informative and helpful tutorial ever, I'm truly thankful.

@diwakarsingh2156 3 жыл бұрын

for forward slash (\) what we take ?? Delimiter

@kannanmassey5783 2 жыл бұрын

Very well explained. Thanks a lot Current range selects only column A

@satishloke 3 жыл бұрын

great , simple, complete, to the point,

@TeachExcel 3 жыл бұрын

Thank you! I'm glad you like it!!

@jcgaming9749 3 жыл бұрын

This is an amazing tutorial, though I’m having issues with the leading zeros in numerical data in the CSV file not appearing and then not importing through to the destination file. Any tips on how I can fix this in the VBA code at all?

@mroberts2112 2 жыл бұрын

Leading zeros are not numbers, they are character strings.

@sachin0200 2 жыл бұрын

hi sir, concept was awesome, i have one doubt , how we import (add) more data in same database without replacing old data. for eg:- you have prepared for 15 data and if we import another file in same database staring from 16th data or next from 15th data...pls reply sir...

@bvvenkatesh2992 2 жыл бұрын

Thank you so much for this video

@gaminxden11 2 жыл бұрын

You deserve my subs👏

@lucabottini9302 Жыл бұрын

Thank you so much! No other words to say! =)

@alexrosen8762 3 жыл бұрын

Super useful! Thanks :-)

@TeachExcel 3 жыл бұрын

You're welcome Alex! Glad you think so :)

@christopherlindsay1440 3 жыл бұрын

This is awesome, but the code will not allow me to select where the file comes from (location). when I run the code, it automatically pulls up all the files on my computer, not just the text files in the location I put in originally with the "fileToOpen = "location of text file.txt". Do you have any advice on that?

@otisskinner2039 2 жыл бұрын

I am trying to do what this tutorial teaches but my code fails saying that the object doesn't support this property or method. This happens at the point where the code to copy the wsTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A1"). I even tried to specify the range as in Range("A1:Q100") but same result. The code does open another tab in the workbook I want it to but the copy paste never happens. Any help?

@KWEDA27 2 жыл бұрын

How to import multiple different txt to different worksheets in same excel in automated way. pls help.

@yaspalsharma9410 2 жыл бұрын

i need to auto update CSV data into excel file without opening excelsheet can you help me for this.

@motonanga12 9 ай бұрын

Quick Question: How do you skip break line? The text file I am importing contains empty lines which is stopping the import process. no error but only a portion of the file is imported. Thanks for your help

@harycan6923 5 ай бұрын

Unbelievable fantastic , supper , thank you .❤❤❤❤

@omarrahim673 Жыл бұрын

Great work, respected sir. Please export code for txt paper, as well as import code from txt paper. Thank you

@ravindrabisht7520 2 жыл бұрын

How to open multiple .txt file?

@stevenbrown1287 9 ай бұрын

How would I make it so the it doesn’t just start the text file at row 2 but also a certain column?

@fjnomad 2 жыл бұрын

Great tutorial ! the only issue I have with this is importing csv . Even if I put StartRow : 2 or 3 it keeps importing the first row

@sandrodaniel2928 2 жыл бұрын

omg I get the same issue

@HenareW Жыл бұрын

timestamp 08:30 re-write and apply below will fix your issue wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Offset(1, 0).Resize(Range("A1").CurrentRegion.Rows.Count - 1).Copy WsMaster.Range("A2")

@bhaskarroy3364 2 жыл бұрын

Kindly announce the keyboard shortcuts that you are using while you are explaining, keeping up with the pace is just insane! Thanks and best wishes!

@TeachExcel 2 жыл бұрын

Sorry about that - usually the shortcuts will be: Alt + F11 to go from worksheet to VBA window and then Alt + F8 to view the macros to run from the worksheet.

@bhaskarroy3364 2 жыл бұрын

@@TeachExcel Thanks! Although imI shouldn't ask it here, but, could you kindly tell me how to backtrack a calculated field on my excel pivot table so that I can check the vlookup functions that are running to display the data?

@DagamerJc22 Жыл бұрын

I want to do the same thing but the only difference is that my file has spaces between some rows and when i run the code, excel only reads a portion of the file up until it detects a space in a row. How can i change that??

@yami39 6 ай бұрын

I have a question. When I import a txt file, how do I treat my numbers as strings rather than a number? Because it automatically formats cell (in my case my number is converted to scientific).

@keitharaneta3465 2 ай бұрын

Hello, If the file being imported contains an empty cell, it stops further copying the proceeding contents of the file. how can this be resolved? thanks for the answer

@jeffanderson6740 Ай бұрын

Does all of the data import as text?

@tubemanfredo 2 жыл бұрын

It doesnt work with excel 365. Why?

@longthuy8311 6 ай бұрын

Good

@joeb253 4 ай бұрын

These codes hard to understand for beginner do you have videos make it easier.

@user-ht5ke5xd5i 5 ай бұрын

Is there a way to do this from a pdf document?

@2750nav 2 жыл бұрын

INSTEAD OF SPECIFYING THE ROW NUMBER, IS IT POSSIBLE TO DEFINE A WORD, THE PROGRAM CODE NEEDS TO SEARCH FOR THIS WORD IN TEXT PAD, THEN IMPORT THE ROWS (SAY 10 ROWS STARTING FROM THE ROW WHERE THE KEY WORD IS SPECIFIED) EG: IN YOUR CASE "asc-39" this word is in 9th row 3rd column, write the codes with "asc-39" as key word, SO EXCEL SHOULD IMPORT ROWS 9 TO 16. I CAN PAY FOR YOUR SERVICE. THANK YOU IN ADVANCE FOR YOUR VIDEOS AND LESSONS.

@solomong.gebrhana1204 Жыл бұрын

Hey, what if I want to automate importing a text file to a database table on MS SQL server Management? Please the key word here is "Automate", I know how to do it manually, but I want to do it automatically. Thanks a lot.

@xXNitemareXx 6 ай бұрын

Save As *.csv (MS-DOS) if you have that option. I have to reconfigure my analytical data and save the exported file as MS-DOS .csv or our SQL DB gets cranky.

@user-wd3fh2ue4d 9 ай бұрын

If i want to run it several times , it gives the error of file name already exists

@mdtanweer555 7 ай бұрын

❤❤

@diya2909 9 ай бұрын

If in a folder multiple text files are there then how to use this code sir

@xXNitemareXx 6 ай бұрын

You should make a separate output subfolder in the same file folder that holds the text copy of your converted original files. Give it a general import file name that will remain static within the code (i.e. IMPORT.TXT) but will be overwritten every time you save to it. This will protect your data files in their normal file folder by making a separate folder just for the import text file, and there shouldn't be as many duplicate files confusing things. The directory string will be mostly the same except your text file will have one extra string for the subfolder it belongs to, so make sure you account for each path string as it relates to the different syntaxes (open file path vs. save as file path).

@yolo_burrito Жыл бұрын

Are you still doing the premium course? The site seems sketchy the price on the site is $199 but checkout is $295. Also when I try to ask a question in the form it says error and asks me to contact a site administrator.

@TeachExcel Жыл бұрын

Hi M. Port, where do you see the price for 199? And which forum are you trying to ask a question in? Have you tried to contact the site admin on TeachExcel.com via the contact page?

@yolo_burrito Жыл бұрын

@@TeachExcel yeah, and they changed it. So scummy. I’m out.

@ajitkumarverma4411 3 жыл бұрын

Sir i need to help . one folder there 3 to 4 txt file. how to these txt file data copy and paste in one excel . In text file separate by comma(,) columns Plz help sir how to implement this solution in vba

@AssTraped 5 ай бұрын

why does your wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A3") not clash with Workbooks.OpenText _ Filename:=fileToOpen, _ StartRow:=2, _ DataType:=xlDelimited, _ Tab:=True

@dwayne9456 Жыл бұрын

Question: Why are you answering questions of relevance to the topic- @otisskinner2039 @otisskinner2039 6 months ago I am trying to do what this tutorial teaches but my code fails saying that the object doesn't support this property or method. This happens at the point where the code to copy the wsTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A1"). I even tried to specify the range as in Range("A1:Q100") but same result. The code does open another tab in the workbook I want it to but the copy paste never happens. Any help? among others?

@dwayne9456 Жыл бұрын

Use this instead: wsTextImport.Worksheets(1).Columns("A1:Q100").Copy wsMaster.Columns("A1:Q100"). FYI this was from a beginer.