Wednesday, July 6, 2016

Fast Solution to Problem: Append Text File from Spreadsheet

Append Text File from Spreadsheet

Hi All,
I have a spreadsheet I would like to open an existing text file and write the data to a text file column. 
The only routine I’ve used that is similar is pasted in below. 
I want to change this to only write/append the text file column 4288 with Column A spreadsheet data.
As it stands now, the routine below can only write data to a line based on an array that counts out the spaces. 
However, the text file already contains data and I’m just looking to append not to overwrite existing values in other columns.
Any thoughts on how to change the routine to not write over the existing data – just append?
Thank you – Jenny B.
Scenario  - I want Column A of the Excel Spreadsheet to write its contents to Column 4288 of the Text File
Sub WriteBack()
Dim Sizes As Variant
Dim arr As Variant
Dim r As Long, c As Long
Dim fso As Object
Dim ts As Object
Dim TheLine As String
Dim TestStr As String
Sizes = Array(3, 1, 4, 1, 3, 1, 5, 1, 500) <<<<< change this somehow to just write to line 4288 - not overwrite other data??
arr = ActiveSheet.UsedRange
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("C:\Documents and Settings\blooj02\Desktop\formtest.txt", True)
For r = 1 To UBound(arr, 1)
TheLine = ""
For c = 1 To UBound(arr, 2)
TestStr = Left(Trim(CStr(arr(r, c))), Sizes(c - 1))
TheLine = TheLine & TestStr & String(Sizes(c - 1) - Len(TestStr), " ")
Next c
ts.WriteLine TheLine
Next r
Set ts = Nothing
Set fso = Nothing
MsgBox "This has been written to your Text File as requested"
End Sub

Keys to the Problem Append Text File from Spreadsheet

Download SmartPCFixer to Fix It (Free)

To append the text to the existing txt you should redesign your code as follows:

Set ts = fso.CreateTextFile("C:\Documents and Settings\blooj02\Desktop\formtest.txt", True)

this line should be run only once.
Say, first you should check if the file exists then it should not creat a new file but open it for append and write the line(s), like:

            If (fso.FileExists("your text file path" ) Then
              '   NOW YOU should open file FOR APPEND, here 8 argument means to open  file for Appending  
                                       Set ts = fso.OpenTextFile(your text file path & "NAME.txt", 8, True)
                                       ts.WriteLine TheLine ' MODIFY THIS AS YOUNEED
                                       ts.WriteBlankLines 1 ' MODIFY THIS AS YOU NEED

Set ts = fso.CreateTextFile("C:\Documents andSettings\blooj02\Desktop\formtest.txt", True)
             End If

check these links for use

Open Action Center by clicking the Start button Picture of the Start button, clicking Control Panel, and then, under System and Security, clicking Review your computer's status.

Update and run your antivirus software. Windows doesn't come with antivirus software, but Action Center can often monitor the antivirus software that you or your computer manufacturer have installed.

Another Safe way to Repair the Problem: Append Text File from Spreadsheet:

How to Fix Append Text File from Spreadsheet with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.  Open it, and it will scan your system. The junk files will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.

Related: error message fsui.exe/ wlidcli.dll is missing
,Solution to Error: Excel: Automatic number generation
,Troubleshoot:i wanna change language of all windows in Windows 7 Home Premium
,Troubleshooting:IF Function, if the vendor ID in column A matches any of the vendor ID's in column F, I want 1099 to appear in column D. Error
,Fast Solution to Problem: ReadyBoost is not working properly in my computer
,Troubleshoot:Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive. Error,External Hard Drive not listed in Windows 7 backup wizard Tech Support,Tech Support: I'm always being signed off so annoying,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.,Referencing data in Access using Excel [Anwsered],Need Best Way To Present Data [Anwsered],Same question but for windows 7 home edition,sometimes fullscreen won't activate [Solved],Solution to Error: We bought a new computer with windows 7 and it is constantly freezing. How do we fix this?,Solution to Error: Windows 8 update crash (2013-07-22),brclr.dll.mu_ Missing Error Fixer,How To Resolve Missing fin_myagtui.eng Problem,Corrupted f_0006ad File - How to Fix,nkebigvg.cpl Not Found Error Fixer,Corrupted olcontrols.ocx File - How to Fix
Read More: How Can You Fix - Always my system needs Last Known Good Configuration to reboot?,Fast Solution to Error: Applications flash but will not load.,Troubleshoot:appropriate permission Error,Any body know why internet explorer 8 keeps stopping when I try to attach an item to the email. [Anwsered],How to Resolve - any1 did upgrade this day and start gettin windows froze internet froze after update? plz let me know how to fix it?,After Windows Update CD/DVD drive does not work,After installing windows 7 to laptop the labels print out of line how can I fix it,After my computer sleeps the wireless always disconnects and will no reconnect,After signing into, I get a message" This tab has been recovered"....what does this mean and how do I fix it.....,All desktop icons changed to windows live and does not open

No comments:

Post a Comment