Modules & VBA :: Writing To Text File With Line Feeds
Jun 22, 2015
I have a function which takes a recordset and writes the contents to a text file. One of the fields in the recordset is a free format text field, the contents of which originate from a text box on a form. Occasionally, a user can hit the 'Enter' key to add a new line (carriage return) to the note within the text box.
The problem is, when the output text file is subsequently imported to Excel, the carriage returns within that text field serve to move the next piece of data to the next row (even though the CR is within the double-quote text-qualifier in the text file)I want line breaks within the text field to be retained within the cell.
One option would very simply be to replace the carriage returns with simple spaces in the write function. I could do that quite easily. However, I would prefer to retain whatever the user has entered into the text box, including line breaks, if at all possible?So if the user broke his free format 'note' into multiple lines (for whatever reason), it would similarly appear as multiple lines in the same cell (and in the same row / record) in the Excel worksheet.
I could replace the carriage return with some obscure character during the write, then switch it back during the Excel import, but that seems a little clunky (plus Murphy's Law dictates that no matter how obscure a character I choose, somebody, at some point, will genuinely use it, and it will get switched out. Which means I'd have to use a non-printing character?could I simply replace the carriage return with a line feed? (Or is it the other way around? I always get carriage returns and line feeds mixed up!)there's a simple way around this but I always get confused as to precisely which ASCII character the 'Enter' key produces when keying into a text box.
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
I have a data entry form with a memo control physically sized to match the allowable space for this information when printed on a report.
Basically, I need to be able to stop the user from further input when the cursor reaches the lower right corner of the memo control. Beeping when reaching the limit is acceptable, but a pop-up message would be ideal.
I have not been able to find a solution that will limit the amount of text displayed in the memo control that will also include however many LF's a user inputs. All of the search results refer to only limiting the number of characters and do not take into account LF's.
I am new to Access and would appreciate all suggestions.
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I want to open a txt file through my access database and append a line to it. When I use the opentextfile method for appending but it always appends my new text line to the end of the last line. I want it to append the new text to a new line. I have tried object.skipline but this is not supported when appending?
This is what I get: "oldline" "newline"
but I want: "oldline" "newline"
Here is my code
sub ConcatenateTxtFile ()
Set fs = CreateObject("Scripting.FileSystemObject") Set f = fd.OpenTextFile(dpath, ForAppending) 'Need a line here to skip to next line f.writeline ("newline")
I some code that is reading the first 32 lines of a text file and paste the results into 1 text box on individual lines. I would like each line to go into an individual text box.
I have 32 text boxes named txt_1, txt_2, txt_3 I have a variable named ThisLine holding the line of the text file. I have a variable named textBoxName holding the name of the control.
Need to putting these together to write the line to a text box .
Code: Sub ReadFile() Me.txt_newData = "" Me.txt_OrigData = "" 'again, we need this strange thing to exist so that ... Dim fso As New FileSystemObject
I need to insert(append) first and last line in xml file...
So I have xml file and I need to insert one line to the top and at the of the text... Xml file contains Cyrillic characters and method to recreate xml file is going wrong with characters conversions... The last line a can append easy but the first line is a problem...
I have a textbox on continues form which contains multiple lines and I d like to set it up so it ll be scrolled down completely when it displays the form. I ve already found a few so called solutions but nothing worked for me.
I'm trying to figure out at what point an error message occurs while a database is closing. This problem only occurs if someone clicks on the red close button on the top right of the screen when there are several forms open. If the user exists normally there is NO problem.
I am attempting to use the print statement to write to a text file to track what is happening as the database shuts down. Unfortunately, the open (print) statement, when issued, zaps any prior contents which precludes having a full printed record. I am hoping for a print syntax that would allow appending.
Code: Open "ErrorReport.txt" for Output as #1 Appended text to the file to show what is happening in that event. Close #1
The program, in closing, does stop with an error message that a certain file can't be found. But when I click on debug, the program simply closes. I have also commented out VBA references to the file that can't be found, but the error still persists .
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
I am periodically importing Excel files into access.Making the data usable requires removing spaces, parsing certain fields, adding datasource field, etc. Currently, I am importing the un-formatted data into a staging table, cleaning it up with a query and then copying the updated staging table to the final table.
I have header table and a distribution table that both need to be written to a txt file. They need to be written in a certain order Header. Distribution. Header. Distribution. etc. There can be multiple distribution lines for the header line.
So far this is what I'm trying and its not working right. ~Legend~ hrst = header record set drst = distribution record set tblHeaderExp = header table exporting from tblDistExp = distribution table exporting from ~End of Legend~
Can anyone tell me what's wrong with the below statement ? I'm trying to pull the contents from a text box in a form. The text box is supposed write the contents to a field (memo data type) in a table. Thanks ! ! !
I have a linked *.txt file and a qwery that returns the top 1 record from it. (The ideea is to check a value from one column of the file).Both the linked file/table and the query run corectly,I have a vba code on the on open of a form that is checking the query above mentioned (the form is on the Startup option of the database) and when I need to use the Dcount of the Query "
Code:
DCnt = DCount("Den_Fis", "Data_R_Mod_U$G9707014")
I get the "Runtime error 3625: the text file specification..." doesn't exist etc.I pres debug and test the query, I get the "Unknown Jet Error".I press Stop in the VBA and open the Qry - it shows what is was ment to show.I know the workaround is to make a maketable with the needed info and the Dcount on the local table, but it would add one query and one table to the database.
I have a text file that cannot be imported and turned into a table because it has no line feed characters and is therefore well over 255 columns. The fields are delimited by commas, however.
How can I search this file for all instances of "{id" and then grab the next 10 characters to the right and store them somewhere?
Header as this fields: Num; CompanyCode; InvoiceNumber; Date; Total; Taxes; Subtotal
Detail as this fields linked with Header by InvoiceNumber: InvoiceNumber; CardNumber; Date; ClosureDate; Qt; Price; Tax; Subtotal
And i've got to export to a text file as follows:
first line with a header then the other lines with the details, and if it has another header it continuous with first a header and then details again...
I have a text file that I am using VBA to read and print 2 pieces of data to another text file.The code works to a point, in some instances it does not appear to find the end of file but captures and writes all the data. Or, after printing 36 lines correctly, it then stops writing.
I have stepped through the code and it appears to be capturing the data up past this issue but for what ever reason it does not print it to my text file.
PHP Code:
Option Compare DatabaseOption Explicit Public Mode As String Public FUNDREF As String Public rst As String Function DAILYAUDITTRAIL() Close #1 Close #2 Open "C:UsersTEST IN.TXT" For Input As #1 Open "C:UsersTEST OUT.txt" For Output As # 2 Do While Not EOF(1) Line Input #1, rst If rst Like "KM56Aasdf*" Then FUNDFINDER End If Loop End FunctionFunction FUNDFINDER()
This is the first time that I have done any major work with forms. After I thought I had finished a problem came up. There are several forms that are use to input information into a table. When the form is opened it grabs an automated number for tracking. The problem is, that if the form is opened and then closed it creates a line of data with all null values.
What I would like to do is have the form open, the user fills out the information, and upon pressing the "save" button, the data will save to the table. I think I need to have each text box write to a variable and then save on the click event. Or I could be completely wrong and need to do something else.
i'm guessing this is something I would learn in Forms 101 if i had ever taken that type of course
They are receiving e-mail with some data that they want to transfer to access database to track. The one think they can do is copy past, copy paste. That is a lot of data to copy and paste. The date looks like this format
Field1 Text1
Field2 Text2 (text2 can be more than 255 char)
Field3 Text3 (text3 can be more than 255 char)
Field4 Text4
Field1 Text1
Field2 Text2 (text2 can be more than 255 char)
Field3 Text3 (text3 can be more than 255 char)
Field4 Text4
and so on.. It can be 50 records
I am thinking they can copy this to the text file. Then the code form Access grabs the text file and imports to access table with format below.
Where the word Author= appears to extract the data between the double quotes, so in the above case I want to extract "All Saints" excluding the double quotes.And then where Title= appears extract "On & On", again excluding the double quotes.So I would end up with data in my table looking something like this
strArtist [Author] strSong [Title] strGenre [Genre] strYear [Year] All Saints On & On Pop America Venture Highway Rock 1972 Amillionsons Misty Blue Pop 2002
I have created a form to send emails with attachments. The attachment path is specified in an unbound field which I have called [ToAttach] Rather than typing in the path, I want to use the browse function. I have inserted a browse button and can browse for the required file using following, but can't figure out how to place the file name in the unbound field as a string.
Code: Dim f As Object Set f = Application.FileDialog(3) f.AllowMultiSelect = False f.Show
I have a function to write some data to a text file (as an activity log, separate to the live data contained in the DB)
It uses the FileSystemObject.OpenTextFile method and works fine
However, I'm conscious that, although perhaps unlikely, there is the possibility (which is enough for me to worry about it) that multiple users could trigger the function simultaneously, which could cause a conflict when multiple FE's try to write to the same log file at the same time.
So I'm trying to add some code to prevent this from happening, by checking if write-access is available before proceeding.
Below is the piece of code I've added. Was considering, as an alternative, looping until the objFile is no longer Nothing, rather than depending on the Err.Number?
Code: Dim objFSO As Object Dim objFile As Object On Error GoTo ErrorHandler ... Set objFSO = CreateObject("Scripting.FileSystemObject")
I have a table. Also i have a text file which some of the fields are matching with my table fields ( lets say field A and B ). Now, i need to do compare of these A & B of my table against A & B of the text file and give a result as follows;
1. "Field A" not in the table but in the text file (un-matching data to be shown ) 2. "Field A" not in the text file but in the table (un-matching data to be shown ) 3. "field B" mismatches
