How Can I Speed Up Windows?: 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

ts.Close

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)

HI
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
   ts.Close

   Else

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

check these links for use

http://msdn.microsoft.com/en-us/library/314cz14s(v=vs.85).aspx
http://www.computerperformance.co.uk/vbscript/vbscript_file_opentextfile.htm

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.