Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Change Text in mid File (MSOffice 2007 VBA)

    Seemingly Simple task one would have thought, but "no" says the man at MS.

    OK so you can open a Text file to Read or Append in VBA : object.OpenTextFile(filename[, iomode[, create[, format]]])
    IE IOMode can only be: "ForReading" or "ForAppending" so if you want to use the Writeline it will only append to the File.

    Is the only way to Read the Whole file in to an array? and rewrite it and then rename it to the Original (this will obviously take longer on a large number files where I am only trying to replace the Carriage Return (Chr(13)) with a new Line. IE leaving the Text upto the Carriage Return and moving that which comes after to the next line etc for each Carriage Return.

    Anyway Any thoughts are Welcome before I commit to 3 times longer runtime per document.

    Graliv

    If Anyones Feeling Frisky They could adapt the Below (for Converting MS Docs to TXT) for me if they like (Original Code Supplied by HansV). Actually I think I just Solved it, but anyway: Opinions welcome!

    Public Function ConvWord()

    ' Both paths must end in a backslash

    Dim Fsys As New FileSystemObject
    Dim instream As TextStream

    Set ThisFolder = Fsys.GetFolder("Cocuments and SettingsAdminDesktopTXTFiles")
    Set Allfiles = ThisFolder.Files
    For Each File In Allfiles
    'Set instream = Fsys.OpenTextFile(File, 1, False, False)
    Fsys.DeleteFile File, True
    Next

    Const strSource = "Cocuments and SettingsGraemeDesktopDocFiles"
    Const strTarget = "Cocuments and SettingsGraemeDesktopTXTFiles"

    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim strFile As String
    Dim blnStart As Boolean

    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
    If wrdApp Is Nothing Then
    MsgBox "Can't start Word!", vbExclamation
    Exit Function
    End If
    blnStart = True
    End If
    On Error GoTo ErrHandler

    strFile = Dir(strSource & "*.doc")
    Do While Not strFile = ""
    Set wrdDoc = wrdApp.Documents.Open(FileName:=strSource & strFile)
    wrdDoc.SaveAs FileName:=strTarget & Replace(strFile, ".doc", ".txt"), _
    FileFormat:=2 ' wdFormatText
    wrdDoc.Close SaveChanges:=False
    strFile = Dir
    Loop

    ExitHandler:
    On Error Resume Next
    wrdDoc.Close SaveChanges:=False
    Set wrdDoc = Nothing
    If blnStart Then
    wrdApp.Quit SaveChanges:=False
    End If
    Set wrdApp = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Text in mid File (MSOffice 2007 VBA)

    When modifying text in text files / text streams you always have to read from one file and write to another one, then delete the original file and rename the new file. Operations on text files are very fast, but VBA is a relatively slow interpreted language. The open and save routines in Word are compiled C code, which is much more efficient.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Change Text in mid File (MSOffice 2007 VBA)

    Thought as much. OK, I'll Have to sort that out.
    Read Line Then Append the Line/Lines to a new File and Delete then rename. YAWN!!!
    Workies!!!!

    TX Hansv As USual..

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Text in mid File (MSOffice 2007 VBA)

    <P ID="edit" class=small>(Edited by AlanMiller on 08-Apr-08 08:48. )</P>I'd be looking at using a purpose-built tool for the job.
    ReplaceEm I use often and is a good tool.
    Actual Search & Replace (not tried by me) sounds similar and supports a commandline, so you could still control things via VBA. Shareware though <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Edit - Further hunting suggests General Search And Replace (gsar) freeware might be best suited to the task.

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Text in mid File (MSOffice 2007 VBA)

    I wrote two small utility functions, and make extensive use of them for manipulating data within a file.
    It's true <post#=705,007>post 705,007</post#> that you generally need to read from a first file and then write to a second, but if you'd rather not go through the business of cleaning up files after you (kill the original, rename the new to the original etc), this method is quite satisfactory:
    <pre>Sub TESTPutFileData()
    MsgBox strGetFileData("G:GreavesAdminWeb LINKS.txt") ' returns CR/LF if the file does not exist
    '
    Call PutFileData("G:GreavesAdminWeb LINKS.txt", "") ' Should create an empty file of 0+2 characters.
    MsgBox strGetFileData("G:GreavesAdminWeb LINKS.txt") ' returns CR/LF if the file does not exist
    '
    Call PutFileData("G:GreavesAdminWeb LINKS.txt", "alphabet") ' Should re-create a file of 7+2 characters.
    MsgBox strGetFileData("G:GreavesAdminWeb LINKS.txt") ' returns CR/LF if the file does not exist
    '
    Call PutFileData("G:GreavesAdminWeb LINKS.txt", "aa") ' Should re-create a file of 2+2 characters.
    MsgBox strGetFileData("G:GreavesAdminWeb LINKS.txt") ' returns CR/LF if the file does not exist
    '
    End Sub
    </pre>


    I have used these two procedures to process truly massive text files with no apparent delay in processing. If I need to replace all occurrences of one string by another throughout the file, I read the string into memory, effect a little string-replace function along the entire string,l and then just write the string back to the original file.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •