Create text file access vba

We all come across with such kind of situation where we had to put huge data in excel files, but eventually, we have faced the situation when the size of that file with the huge data went so up that we had to convert that into a text file.

Due to this we could neither work on that file nor saving the file into excel format. This kind of situation we have seen in many projects where we need to extract the data from the database. Extracting the data from the database is possible if use text file for this purpose. For this, we need to convert the excel file into a text file. Although it is not a big task to when it is a repetitive activity and may take huge time to perform.

To avoid this, we have a process in VBA, with that, we can convert the excel file into a text file without harming the data. By this, we can save a huge set of data in the text file by reducing and compressing the file size as well.

Watch our Demo Courses and Videos. Understanding the VBA code of writing the text file is quite complex but it is not difficult to understand.

In this example, we will see how to map file location with VBA code to write it over the Text file format. The error I get is: "Cannot update.

Database or object is read-only". I appreciate any help. Improve this question. How many of these are you doing? What about using the import wizard? I use the import wizard now, but that's getting impractical. I started getting maybe 5 or 6 of these files a week, now I'm getting a week, and will probably get more. Add a comment. Active Oldest Votes. Improve this answer. Sign up or log in Sign up using Google. Sign up using Facebook.

Your code loops over every line in the text file, but if you are only keeping the last value, that's the reason why you get a "D" instead of an "F". It looks like you're manually writing to a table from a text file - I'd recommend importing the data once, saving the spec, and then using docmd. I text file I will have one or many lines but I can't change formatting of this file becaus I get this file from other aplication. Add a comment. Active Oldest Votes.

How about something like this? Print rst. Fields 0. Name, rst. Fields 0 With rst. MoveLast Debug. Ben Ben Is using the Scripting Runtime preferred over the older channel method? I'd like some reasons to tell my students with info backed up by other experience. Please note that this answer promotes bad coding practice : The problem is that not explicitly defining the correct variable types as well as creating an object by a string reference to its name can cause you very hard to debug problems in the future for example if you misspell parts of the name.

Also, by not typing the variables, you have no way to learn about the other amazing methods FileSystemObject has to offer. Ben: Please consider updating your answer to lead beginners in a better direction. MarcusMangelsdorf I have heard you, but I don't want to have a debate. I disagree that this answer is promoting bad coding practice. Using late binding like this is perfectly acceptable and is useful where you don't know what version of the Microsoft Scripting runtime your user has on their machine.

Show 2 more comments. Open ThisWorkbook. Path property. Bhanu Sinha Bhanu Sinha 1, 10 10 silver badges 9 9 bronze badges. Pleas write the answer with some explanations and details.

I prefer this method to the FSO method because it doesn't require external references and is quite short. Although I do suggest using FreeFile to get the file number instead of hardcoding it as 1. I prefer this approach as well for mundane text file writing. Regarding comment from phrebh about using FreeFile instead of a hardcoded 1 see wellsr. Show 1 more comment. To elaborate on Ben's answer : If you add a reference to Microsoft Scripting Runtime and correctly type the variable fso you can take advantage of autocompletion Intellisense and discover the other great features of FileSystemObject.

WriteLine "something" ' Close it, so it is not locked anymore fileStream.