It's 2022 and the knowledge and methods presented in this vid are still absolutely brilliant! Thanks a lot man!

Awwwe No Andrew we don't hate you! I actually love learning the longer way of doing something and then how you have simplified it. I personally think its the best way to learn.Thanks guys.

This was so helpful! Literally everything I know about VBA is from these tutorials, which have completely transformed my use of Excel for my business. I wrote some additional code, using Regular Expressions, to deal with the special-character problem, and I also gave my variables slightly more relevant names to my own situation. In my particular scenario, I had multiple special characters of a varying quantity arising in columns A and D of my spreadsheet, and those entries included not only commas but also parentheses, hyphens, slashes, etc. I used two cases, one if the current column was 1 or 4 (i.e., in Columns A or D), and one otherwise, and in the 1 or 4 case, I used a separate function that looped over a counter variable that counted how many special characters were in the cell to be transferred to the CSV file, encasing each one inside double-quotation marks. Finally, I put a button on the spreadsheet, and added three lines of code (e.g., (1) sub AddTimeEntry_Click() (2) Call ExportToCSVFile (3) End Sub) to call the sub when the button was clicked. My code executes in about 0.1 seconds, so quickly that I had to put in a message box to alert me that the CSV file was saved and written because I kept thinking nothing had happened and saving multiple files. Everything is formatted perfectly no matter how many special characters I have in columns A or D!! If it's of assistance to anyone else, here was my solution--thanks again Wise Owl for all your help! Sub ExportToCSVFile() Dim ws_MyTimeSheet As Worksheet Dim fso As Scripting.FileSystemObject Dim DestinationFolder As String Dim ts As Scripting.TextStream Dim TimeEntryRow As Range Dim ColCount As Integer Dim CurrentColumn As Integer Set ws_MyTimeSheet = ThisWorkbook.Worksheets("MyTimeSheet") Set fso = New Scripting.FileSystemObject DestinationFolder = [REDACTED--INSERT YOUR FILE ADDRESS HERE AND REMEMBER THE BACKSLASH \] Set ts = fso.CreateTextFile(DestinationFolder & Format(Now, "mmddyyyy_hhmmss") & "_upload.csv", True) Application.ScreenUpdating = False ws_MyTimeSheet.Activate ColCount = Range("A1", Range("A1").End(xlToRight)).Cells.Count For Each TimeEntryRow In Range("A1", Range("A1").End(xlDown)) For CurrentColumn = 1 To ColCount Select Case ColCount Case 1 Or 4 ts.Write """" & EncodeValue(TimeEntryRow.Cells(1, CurrentColumn).value) & """" Case Else ts.Write EncodeValue(TimeEntryRow.Cells(1, CurrentColumn).value) If CurrentColumn < ColCount Then ts.Write "," End Select Next CurrentColumn ts.WriteLine Next TimeEntryRow ts.Close Set fso = Nothing MsgBox "Your time entries have been saved to a CSV file and are ready to be uploaded!" End Sub Function EncodeValue(ByVal value As Variant) As String Dim regEx As Object Set regEx = CreateObject("VBScript.RegExp") 'late binding so users don't have to enable the library for Regular Expressions regEx.Global = True 'We want all instances of special characters in our worksheet to be encoded, so set this to True regEx.Pattern = "[,""()\-\;\/\.\']" 'For each instance of the special character in the cell in our worksheet... If regEx.Test(value) Then '....put the special character in double-quotation marks EncodeValue = """" & regEx.Replace(value, "$&") & """" Else EncodeValue = value End If End Function

Thanks for another great video! I love that you teach the longer methods first, then the faster ones. It really helps me to troubleshoot my code! If the short version doesn't work, I try the long version. If that succeeds, I know I probably missed some specific formatting in the short version, and if it doesn't, it's generally easier to pinpoint what is causing my problem!

Thank you for an interesting video!!! If somebody is just starting to work with text files, this video is everything one might need to master the functionality.

Been looking at your video, like 10 more or less, in like 2 years. I am now proficient with VBA but I keep finding your video always satisfactory and always finding something to learn and which is useful. You deserve way more views and thumbs. Just having to deal with xls/xlsx/csv file at work, and wanted to "cache" the csv text and use only some of the data without opening all the csv file to a workbook . I've used "Open file for mode as filenumber" method and find it to work as needed. Something I just wanted to point out is that some file may be generated by non windows users. This the newline character is not CRLF but only LF, this is to know as it will change the way you have to deal with the file to read just 1 line.

Just found your video. Thanks for the tutorial. I have adapted it to my workbook, and it is working as advertised.

Genius. Thanks so much. Finally found an excellent resource for advanced excel VBA topics.

thank you so very much!!! I hope you make lots of money and be very happy in your life :D

Hi, Thank you for the great classes. Is there a course specifically about extracting xml data.

Many thank for the content! Top quality 😃 Great teaching style.

thank you for you excellent tutorials!! i have one question: does this work for multiple txt. file in a folder? many thanks!

Dear Andrew, wow fantastic tutorial series on excel VBA!!!...crystal clear and probably the most comprehensive excel VBA tutorial channel on you tube ... Have a request ; can you please make available the notes that you are using to teach each concept? Cheers

Hello Sir, I have used the split function instead of left() & right() (video from 27:00 min on wards) to split the text into array.

Very good tutorial. Thank you

Once again a fantastic video tutorial. You make the tricky look very easy, sign of a true master. Can you make this seem easy I wonder? I want to amend my change log to incorporate the "Before change" cell values. I've figured that I can write all of the range addresses and values to a public array in the SelectionChange event and then loop through that array in the Change event but that seems a trifle convoluted. How would you go about recording the values that have been changed?

Hi Andrew. I have been following your VBA series, and I just want to thank you for creating such great content! My skills have increase substantially. I would like to request a new subseries to this regarding Shell32 Library, Microsoft Shell Controls And Automation. I have been able to successfully implement Shell32.shell to use ShellExecute function to mass print, and open PDF files, however, I would like to see what this library is capable of doing with a level of expertise you posses. For example, learning some techniques on how to extract data from a PDF file to and excel workbook. Anyways I am a big fan!

I've got some projects in mind where I can use this. Thanks.

Hi Andrew..Can you give some info for reading csv files without a "Data start " row and also having "," within each datapoint(cell)

You are Wizard , spectacular!!

Awesome video! Not sure if it will be on the next tutorial but what if your text has " " them how to include the " " in the text?ts.writeline " " Data Starts Here" " will error

Hi Andrew, thanks for the informative videos, it helps a lot with understanding VBA. I have a question regarding the copying of date into the textfile (+/- min 15 in the video). In the code we don't state that we are copying any cells from the excel. I can see where we are selecting it via range but not the actual copy command. Is this done when writing ts.write r.offset...or somewhere else? This is keeping me busy....

Great videos for someone extremely new to VBA with that being said, I'm hoping that what I'm looking to achieve is possible. I'm creating a form that a user can enter details specific to the data that can be varied, by multi users so if user one completes the form and user 2 completes the form their responses may be different (i.e. form text box states to enter color) user 1 enters yellow yet user two enters blue, then merge it into a static string of details (static details the sky is "form details") (this is not the exact example what I need is more complex I have several form details I'd need to merge into the static string, but I am hoping the color example will give you an idea what I'm attempting to achieve). Do you have a video out there already that shows how to get the form to convert into the text file? I haven't found it yet in my hunt through the videos for the last 3 days and I'm on a time crunch to achieve this endeavor any assistance would be greatly appreciated!!! I did watch the creation of text file, but I'm having a hard time getting it to pull in the details of my form. Currently I do have the form details writing into a worksheet within the excel document, mainly so I could see my form actually working

How do I loop through all the folder or files starting with the same phrase only in Microsoft excel vba?

well done!

Thank you!!

Awesome!

I have used the CDate function as described here, but needed to tweek the "vbTab" so that my program recognised the character "|" instead as the delimiter. However, I found a problem in that the value of "0.00" as a number in my text file has been returned as a date "12:00:00 AM" in excel. How can I rectify this please?

Hello, I have a problem when activating the references in sub menu Tools of VB window. When I click it, a window appears with message "Error accessing the system registry". Do you know why? Is that because of the Excell system or the window system? What should I do? Many Thanks in advance.

Thank you, Andrew, for your great tutorial. Unfortunately this code "does not want" to work on Mac. Any idea how to adjust it? Best regards. Nataliia

how do you change it so it creates a new txt file with the name as the current time and date

Thank You very much for this absolutely perfect tutorial. One question please: Once we read a line in a text file can we move back to the previous lines ? in other words, Can we move freely in the text file and read lines forward and backward as we like ?

Absolutely brilliant! Thank you

Ola Wise, Acompanhando a criação deste código tive o seguinte problema: ts.=fso.CreateTExtFile.............& \pasta destino\Nome_Texto.txt "Permissão negada". Como resolvo isso. Obrigado (usei o office 2010)

Hi Andrew when I do this Do Until ts.AtEndOfLine ActiveCell.Value = ts.ReadLine ActiveCell.Offset(1, 0).Select Loop I get just first two rows, then there are ts.WriteBlankLines and it seems to be where this procedure stops

What if I wanted to read in data from multiple textfiles and combine the data? For example based on a common (key) value shared between both files? Does anybody know of a tutorial on combining data from multiple textfiles?

Hi! Very helpful video thanks! I have a question...how do you read a text file to a one cell only?t.y.

Hi Andrew, I have a quick question. Do we always have to activate these libraries in order to create files or folders....or can we do that without activating the libraries?

textarray=split(textline,vbtab)

amazing

Hi I have one question why didn't you make even a single video MS Access macro?

Mega Like!

sir continously showing error ...i dont know how i should rectify it...i just copied ur commands..

How to create a text file in utf-8 encoded excel vba Thank you

Sir how to change particular some words in a text file by the excel

I haven't read all the comments and at this point haven't watched the whole video but the date issue can be fixed easily. You need to define another string so Dim datevalue As String datevalue = Format(Date, "dd/mm/yyyy") This will set all dates as a string in the given format.

Hi Andrew, Error pulling data from txt to excel, example 67235,12 number is detected as date, I think it gives pre-1900 error, what is the reason and solution? location of error: If IsDate(ThisValue) Then ThisValue = CDate(ThisValue) End If ActiveCell.Value = ThisValue Google translate

woooooooohoooooo.....!!!!!!

I have one question I am extracting some data in text file based on criteria TOTALS but in my case code is working only for one TOTALS as my file contains more than one TOTALS I need extract all TOTALS Values ... Unable to loop through code Please advice.... Many Thanks in advance.. Dim myFile As String, text As String, textline As String, entryDescription As String, amount As Long, Filenum As Long, X As Integer Sub Button1_Click() Range("C1").Value = "2425" X = 1 myFile = Application.GetOpenFilename() Open myFile For Input As #1 Do Until EOF(1) Line Input #1, textline text = text & textline Loop If InStr(text, "TOTALS") > 0 Then TOTALS = InStr(text, "TOTALS") Range("A" & X).Value = Mid(text, TOTALS + 31, 25) X = X + 1 End If Close #1

Hi Andrew, thanks again for another super useful video. Based on this video (Part 23), I wrote a code. It works fine. It copies some data from Excel, creates a text file and pastes it there in a pre-defined layout. The code works what I was expecting. The date/time data in Excel is in dd/mm/yyyy hh:mm:ss and I want it to be in dd mmm yyyy hh:mm:ss format. With ts.write r.Value I get same format as in Excel. How can I change it to the desired format? Changing the format in Excel doesn't help. Thank you.

Hi Andrew Can tell me if its posable to Add a Worksheet to a Workbook and Name the the Worksheet, that I can do, but is it also possable to give the new sheet a Code Name by just using VBA code? Also Is it possable to Import a Module using VBA code. The reson I ask is because I want to create a SetUp for a Workbook with all the Worksheets and Code in Place just using VBA. Regards Phil Millington

ps The workbook I want to update already has code in it.

Hey, there!! first post, guess i'm the only one with a problem. This works great except, of course, except at 16:30 you tested your code and it wrote the text to your test.txt file beginning just under where you said "data starts here". Mine on the other hand starts writing at the bottom of the page and works it's way up. I swear on everything i did it just like you. why is it always me?

Wow

I keep getting an error on the line of that sets the value of ColCount.. anybody spot the issue ? If I simply set ColCount to an integer everything works.. Sub AddDatatoTextFile() Dim fso As Scripting.FileSystemObject Dim r As Range Dim ColCount As Integer Dim ts As Scripting.TextStream Dim i As Integer Set fso = New Scripting.FileSystemObject Set ts = fso.OpenTextFile( _ "C:\$user\WORK\VBA Training\First Folder\Test.txt", _ IOMode:=ForAppending) movielist.Activate 'ColCount = Range("a2", Range("a2").End(xlRight)).Cells.Count ColCount = 4 For Each r In Range("a2", Range("a1").End(xlDown)) For i = 1 To ColCount ts.Write r.Offset(0, i - 1).Value If i < ColCount Then ts.Write vbTab Next i ts.WriteLine Next r ts.Close Set fso = Nothing End Sub

how can we delete a line from a text file ?

The second read isn't working. I look step by step, and don't separate the words, just write immediately to the cell (1row - 1cell)

Great video, thank you! But god damn you are FAST speaker.

Hello. Thanks a lot for your tutorials. But unfortunately - the code doesn't work. I got the message for "Dim fso As Scripting.FileSystemObject" "variable is not defined. Cold you help me please to solve the problem? Best regards AB