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

Подписаться 255 тыс.

Просмотров 140 тыс.

50% 1

MY FULL EXCEL VBA COURSE [60% Discount]: www.teachexcel...
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...
Excel Forum: www.teachexcel...
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...
- Building Professional Forms: www.teachexcel...
TeachExcel.com
#msexcel #howto #tutorial

Опубликовано:

1 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 64

@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

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

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

@motonanga12 11 месяцев назад

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

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

@keitharaneta3465 4 месяца назад

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

@DuyenLe-kt3xx 4 месяца назад

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

@fjnomad 3 года назад

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

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

@allensilva3066 6 дней назад

Hello how about excel to notepad? 0:30

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

@yami39 8 месяцев назад

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).

@OmkarThorat-u7m 7 месяцев назад

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

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

@mdtanweer555 9 месяцев назад

❤❤

@jeffanderson6740 3 месяца назад

Does all of the data import as text?

@satishloke 3 года назад

great , simple, complete, to the point,

@TeachExcel 3 года назад

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

@Harycan0 7 месяцев назад

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

@robertkaniewski3281 2 года назад

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

@KWEDA27 2 года назад

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

@AssTraped 8 месяцев назад

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

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

@omarrahim673 2 года назад

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

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

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

@joeb253 6 месяцев назад

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

@tubemanfredo 2 года назад

It doesnt work with excel 365. Why?

@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 8 месяцев назад

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.

@stevenbrown1287 Год назад

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

@Tech-h4i 2 года назад

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

@longthuy8311 8 месяцев назад

Good

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

@JadhavKeshav 11 месяцев назад

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

@kannanmassey5783 2 года назад

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

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

@alexrosen8762 3 года назад

Super useful! Thanks :-)

@TeachExcel 3 года назад

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

@lucabottini9302 Год назад

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

@diwakarsingh2156 3 года назад

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

@pj5151 2 года назад

Superb! Well documented and explained. Thank you

@bvvenkatesh2992 3 года назад

Thank you so much for this video

@ravindrabisht7520 3 года назад

How to open multiple .txt file?

@mateomontoyanaranjo5948 2 года назад

Thanks mate, this really helped me heaps

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

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

@gaminxden11 3 года назад

You deserve my subs👏

@diya2909 Год назад

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

@xXNitemareXx 8 месяцев назад

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).

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

@vishp4tel_507 3 года назад

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. 😊

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

@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