@@WiseOwlTutorials it is indeed, your tutorials especially on FSO and string functions which are to come later in your videos are very useful. I haven’t come across REGEX in your videos unfortunately, which makes a life much easier when it comes to string parsing and string manipulation, but nevertheless text functions you teach us here are very much of help. I have used Knowledge you presented here on FSO in first part of your tutorial on FSO, for my work project and helped me immensely. If anyone wants to learn VBA this is the right address. Thanks for all the effort and thank you for showing us a real masterpiece of truncating string with a loop before using TextToColumn function. In one of your videos, you have used something similar, but this really induced me to think beyond. Great video, thanks. ☘️🍀👍
@@deninsrmic4165 Thanks Denin! Happy to hear that you found several parts of the video useful. RegEx is one of those topics that has been on my list of videos to make for a long time - one day I'll get around to doing it!
@@WiseOwlTutorials that would be brilliant Andrew, as surprisingly very little was covered on VBA RegEx, and if so, it was done not very thoroughly and fleetingly. Most tutorials on RegEx object and it’s meta language was done in Java or Python which is to a certain degree applicable to VBA but tutorials on VBA RegEx methods and properties are not covered as much one would expect. Many thanks. 👍
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)
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, "$&") & """"
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.
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.
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 ?
Hi Fouad! Sadly not, you can only move forwards in a TextStream object. You might find this video on using ADO to connect to a text file useful ruclips.net/video/V7SslIs7HAI/видео.html You can then investigate the various Move methods you can apply to an ADO Recordset object: learn.microsoft.com/en-us/sql/ado/reference/ado-api/move-method-ado?view=sql-server-ver16 learn.microsoft.com/en-us/sql/ado/reference/ado-api/movefirst-movelast-movenext-and-moveprevious-methods-ado?view=sql-server-ver16 I hope it helps!
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!
You're very welcome Jonathan! I'm really happy to hear that the videos have helped you, it sounds like you've learnt a lot! I think that you might be overestimating my experience with using Shell in VBA however - I've used it every once in a while to perform some very simple tasks but I suspect that you know more than I do at this point! It's a nice suggestion though and I'll add it to my list of things to research. Thanks so much for your input and for watching!
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
***** I was mentioning the slides that you use in your tutorial. But the articles that you linked are really great....i had a quick look...and go through them... Thanks for your videos! Cheers
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, 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
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....
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 Zubair! If you've already formatted the Excel cells you can refer to the Text property r.Text rather than r.Value. Alternatively, you can use the Format function to apply any format you like to the Value: ts.Write Format(r.Value, "dd mmm yyyy hh:mm:ss") I hope that helps!
@@WiseOwlTutorials Another not so needed nitty gritty would be if Dates are all shown in Upper Letters. Currently, month is in mmm. The format I used was DD MMM YYYY HH:MM:SS
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?
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?
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.
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)
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
You're welcome, StackOverflow is your best friend for questions like this: stackoverflow.com/questions/4125778/unicode-to-utf-8 stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba I hope it helps!
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
Hi Phil! It is possible to write VBA code to manipulate your VBA project and Chip Pearson has an excellent description of the process here www.cpearson.com/excel/vbe.aspx I'm not sure if it covers importing a module but I hope the background information helps!
@@WiseOwlTutorials Hi Andrew Thanks for the info on my recent query I will check out the web site you mentioned and let you know how I got on, Thanks for taking the time to reply, keep up the good work Kind Regards Phil
Hi Andrew I had a look at the web site you suggested and it look to much for my little brain a step to far. I think I will automate as much as possible and do the rest manually. Thanks for your help and taking the time to reply. I’ll plod on Regards Phil
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
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.
WiseOwlTutorials as I said at the point I had not watched the whole video. I'll take the time later today to watch the whole thing. my little snip works for me.
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
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?
Yeah here look... Dim fso As Scripting.FileSystemObject Dim ts As Scripting.TextStream Dim r As Range Dim colcount As Integer Dim i As Integer Set fso = New Scripting.FileSystemObject Set ts = fso.OpenTextFile(Environ("userprofile") & "\desktop\Newtextfile\test.txt", ForAppending) Worksheets("data").Activate colcount = Range("a2", Range("a2").End(xlToRight)).Cells.Count 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
badassack Oh forget it, friggin thing works now, didn't do anything different, just started completely over and it worked, craziness. By the way , love the new video, so far. The project i'm working on is reliant on your video's. seems like when i need to know something new, you come out with just the right video, thanks alot, talk soon I'm sure.
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
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?
Have three streams open (1 for each file and 1 for the output file) and compare line by line or bring the contents of the files into a worksheet and use vlookup to merge.
thank u ... seeking on the the net.. the result is that is No way to delete a ROW on a text file... only to copy value on the temp file then delete original and replace.... done
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
![[4K] Watch SpaceX Catch A Starship Rocket From Space!!! #IFT5](http://i.ytimg.com/vi/pIKI7y3DTXk/mqdefault.jpg)
It's 2022 and the knowledge and methods presented in this vid are still absolutely brilliant! Thanks a lot man!
Happy to hear it's still useful, thanks for watching!
@@WiseOwlTutorials it is indeed, your tutorials especially on FSO and string functions which are to come later in your videos are very useful. I haven’t come across REGEX in your videos unfortunately, which makes a life much easier when it comes to string parsing and string manipulation, but nevertheless text functions you teach us here are very much of help. I have used Knowledge you presented here on FSO in first part of your tutorial on FSO, for my work project and helped me immensely. If anyone wants to learn VBA this is the right address. Thanks for all the effort and thank you for showing us a real masterpiece of truncating string with a loop before using TextToColumn function. In one of your videos, you have used something similar, but this really induced me to think beyond. Great video, thanks. ☘️🍀👍
@@deninsrmic4165 Thanks Denin! Happy to hear that you found several parts of the video useful. RegEx is one of those topics that has been on my list of videos to make for a long time - one day I'll get around to doing it!
@@WiseOwlTutorials that would be brilliant Andrew, as surprisingly very little was covered on VBA RegEx, and if so, it was done not very thoroughly and fleetingly. Most tutorials on RegEx object and it’s meta language was done in Java or Python which is to a certain degree applicable to VBA but tutorials on VBA RegEx methods and properties are not covered as much one would expect. Many thanks. 👍
Do you have mentoring?
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
Excellent work! Thanks for taking the time to share your solution - I'm sure that others will find it helpful!
@@WiseOwlTutorials Thanks so much!
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.
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 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 ?
Hi Fouad! Sadly not, you can only move forwards in a TextStream object.
You might find this video on using ADO to connect to a text file useful ruclips.net/video/V7SslIs7HAI/видео.html
You can then investigate the various Move methods you can apply to an ADO Recordset object:
learn.microsoft.com/en-us/sql/ado/reference/ado-api/move-method-ado?view=sql-server-ver16
learn.microsoft.com/en-us/sql/ado/reference/ado-api/movefirst-movelast-movenext-and-moveprevious-methods-ado?view=sql-server-ver16
I hope it helps!
@@WiseOwlTutorials Thank you very much. I will watch it. wish you all the best ❤
Many thank for the content! Top quality 😃 Great teaching style.
Thanks for the kind comments and for watching!
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!
You're very welcome Jonathan! I'm really happy to hear that the videos have helped you, it sounds like you've learnt a lot! I think that you might be overestimating my experience with using Shell in VBA however - I've used it every once in a while to perform some very simple tasks but I suspect that you know more than I do at this point! It's a nice suggestion though and I'll add it to my list of things to research. Thanks so much for your input and for watching!
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
***** I was mentioning the slides that you use in your tutorial.
But the articles that you linked are really great....i had a quick look...and go through them...
Thanks for your videos!
Cheers
thank you so very much!!! I hope you make lots of money and be very happy in your life :D
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, 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
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....
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 Zubair! If you've already formatted the Excel cells you can refer to the Text property r.Text rather than r.Value.
Alternatively, you can use the Format function to apply any format you like to the Value:
ts.Write Format(r.Value, "dd mmm yyyy hh:mm:ss")
I hope that helps!
@@WiseOwlTutorials Hi Andrew, thank you sooooo much. It certainly worked.
@@zubairkahn1983 Excellent!
@@WiseOwlTutorials Another not so needed nitty gritty would be if Dates are all shown in Upper Letters. Currently, month is in mmm. The format I used was DD MMM YYYY HH:MM:SS
@@zubairkahn1983 Hi Zubair, you can use the UCase function to change text to upper case.
UCase("1 jan 2022") will return 1 JAN 2022
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?
You are Wizard , spectacular!!
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.
thank you for you excellent tutorials!! i have one question: does this work for multiple txt. file in a folder? many thanks!
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?
Okay thanks! You are awesome!
Thank you!!
You're welcome, John! Thanks for watching!
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.
Very good tutorial. Thank you
Hi, Thank you for the great classes. Is there a course specifically about extracting xml data.
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)
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
Awesome!
Thanks!
How to create a text file in utf-8 encoded excel vba
Thank you
You're welcome, StackOverflow is your best friend for questions like this:
stackoverflow.com/questions/4125778/unicode-to-utf-8
stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba
I hope it helps!
Hi I have one question why didn't you make even a single video MS Access macro?
I've got some projects in mind where I can use this. Thanks.
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
Hi Phil! It is possible to write VBA code to manipulate your VBA project and Chip Pearson has an excellent description of the process here www.cpearson.com/excel/vbe.aspx
I'm not sure if it covers importing a module but I hope the background information helps!
@@WiseOwlTutorials Hi Andrew Thanks for the info on my recent query I will check out the web site you mentioned and let you know how I got on, Thanks for taking the time to reply, keep up the good work Kind Regards Phil
Hi Andrew I had a look at the web site you suggested and it look to much for my little brain a step to far. I think I will automate as much as possible and do the rest manually. Thanks for your help and taking the time to reply. I’ll plod on Regards Phil
@@philmillington5025 No problem Phil! If it helps, I feel the same when it comes to automating the VB Editor!
how do you change it so it creates a new txt file with the name as the current time and date
How do I loop through all the folder or files starting with the same phrase only in Microsoft excel vba?
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
sir continously showing error ...i dont know how i should rectify it...i just copied ur commands..
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.
WiseOwlTutorials as I said at the point I had not watched the whole video. I'll take the time later today to watch the whole thing.
my little snip works for me.
I and i'm sure others appreciate your commitment to your videos and user responses. Good jod with the videos you've put up. Hope to see more of them!
WiseOwlTutorials I have subscribed. Happy to help if you need it. Seen good things and good practice so far in your videos
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
Thank you ! It solved the problem
Hi Andrew..Can you give some info for reading csv files without a "Data start " row and also having "," within each datapoint(cell)
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
Thanks for your help, Andrew!
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?
Yeah here look...
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim r As Range
Dim colcount As Integer
Dim i As Integer
Set fso = New Scripting.FileSystemObject
Set ts = fso.OpenTextFile(Environ("userprofile") & "\desktop\Newtextfile\test.txt", ForAppending)
Worksheets("data").Activate
colcount = Range("a2", Range("a2").End(xlToRight)).Cells.Count
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
badassack Oh forget it, friggin thing works now, didn't do anything different, just started completely over and it worked, craziness. By the way , love the new video, so far. The project i'm working on is reliant on your video's. seems like when i need to know something new, you come out with just the right video, thanks alot, talk soon I'm sure.
Hi! Very helpful video thanks! I have a question...how do you read a text file to a one cell only?t.y.
WiseOwlTutorials it worked!yeah👍🏼thanks very much! 😄
textarray=split(textline,vbtab)
Sir how to change particular some words in a text file by the excel
You can use the Substitute method docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.substitute
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
Thank you.. your tutorials are really great.
Absolutely brilliant! Thank you
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?
Have three streams open (1 for each file and 1 for the output file) and compare line by line or bring the contents of the files into a worksheet and use vlookup to merge.
well done!
amazing
how can we delete a line from a text file ?
Hi Gabi, this is probably the easiest way to do it stackoverflow.com/questions/30047644/how-to-delete-a-line-in-a-txt-file-if-number-exists-in-vba
thank u ... seeking on the the net.. the result is that is No way to delete a ROW on a text file... only to copy value on the temp file then delete original and replace.... done
Mega Like!
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
woooooooohoooooo.....!!!!!!
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)
ps The workbook I want to update already has code in it.
Wow
Great video, thank you! But god damn you are FAST speaker.