VBA batch file to insert text at end of 50 files slow, 90% CPU usage

equalizer88 · #1 08-13-2015, 07:55 AM

I have simple macro to insert text to end of all text files in the folder that user chooses. It works for my data, but it takes 30 seconds for 40 files and CPU usage goes to 90%. Is this normal? Is there simple fix to macro to make this less CPU intensive?

Code:

 
Sub Batch_Insert_Text_End_of_File()
Application.ScreenUpdating = False
Dim strInFold As String, strOutFold As String, strFile As String, strOutFile As String, DocSrc As Document
Dim i As Long
   'Call the GetFolder Function to determine the folder to process
strInFold = GetFolder
If strInFold = "" Then Exit Sub
strFile = Dir(strInFold & "\*.txt", vbNormal)
   'Check for documents in the folder - exit if none found
If strFile <> "" Then strOutFold = strInFold & "\Output\"
   'Test for an existing outpfolder & create one if it doesn't already exist
If Dir(strOutFold, vbDirectory) = "" Then MkDir strOutFold
strFile = Dir(strInFold & "\*.txt", vbNormal)
  'Process all documents in the chosen folder
i = 1
While strFile <> ""
   Set DocSrc = Documents.Open(FileName:=strInFold & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
   With DocSrc
     Dim oRng As Range
     Set oRng = DocSrc.Range 'oRng set to entire document
     wrd = " 45 CALCULATE, 'BLIN-ARRAY-SIZE' = XXX $" & Chr(10) & _
           " 50 END, 'INIT_TABLE_" & i & "' $"
     oRng.InsertParagraphAfter
     oRng.InsertAfter wrd
     strOutFile = strOutFold & Split(.Name, ".")(0)
        'Save and close the document
     .SaveAs FileName:=strOutFile, FileFormat:=wdFormatText
     .Close
   End With
   i = i + 1 'counter for index
   strFile = Dir()
Wend
Set Rng = Nothing: Set DocSrc = Nothing
Application.ScreenUpdating = True
End Sub
Function GetFolder(Optional Title As String, Optional RootFolder As Variant) As String
On Error Resume Next
GetFolder = CreateObject("Shell.Application").BrowseForFolder(0, Title, 0, RootFolder).Items.Item.Path
End Function

macropod · #2 08-13-2015, 06:12 PM

There's nothing inherently 'wrong' with your code, though it could be made a little more efficient:

Code:

Sub Batch_Insert_Text_End_of_File()
Application.ScreenUpdating = False
Dim strInFold As String, strOutFold As String, strFile As String, strOutFile As String, DocSrc As Document
Dim i As Long
'Call the GetFolder Function to determine the folder to process
strInFold = GetFolder
If strInFold = "" Then Exit Sub
strFile = Dir(strInFold & "\*.txt", vbNormal)
'Check for documents in the folder - exit if none found
If strFile <> "" Then strOutFold = strInFold & "\Output\"
'Test for an existing outpfolder & create one if it doesn't already exist
If Dir(strOutFold, vbDirectory) = "" Then MkDir strOutFold
strFile = Dir(strInFold & "\*.txt", vbNormal)
'Process all documents in the chosen folder
While strFile <> ""
    Set DocSrc = Documents.Open(FileName:=strInFold & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With DocSrc
      i = i + 1 'counter for index
      .Range.InsertAfter vbCrLf & _
        " 45 CALCULATE, 'BLIN-ARRAY-SIZE' = XXX $" & vbCrLf & " 50 END, 'INIT_TABLE_" & i & "' $"
      .SaveAs FileName:=strOutFold & .Name, FileFormat:=wdFormatText, AddToRecentFiles:=False
      .Close
    End With
    strFile = Dir()
Wend
Set Rng = Nothing: Set DocSrc = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder(Optional Title As String, Optional RootFolder As Variant) As String
On Error Resume Next
GetFolder = CreateObject("Shell.Application").BrowseForFolder(0, Title, 0, RootFolder).Items.Item.Path
End Function

equalizer88 · #3 08-14-2015, 09:46 AM

Thanks. Windows overhead with VBA still causes high CPU usage. With similar program written in "C", I'm sure overhead is much lower, but it takes longer to write, need compiler everywhere, etc.

macropod · #4 08-16-2015, 04:56 PM

Quote:

Originally Posted by equalizer88

With similar program written in "C", I'm sure overhead is much lower

That doesn't necessarily follow. VBA isn't like its ancient predecessor, GW-Basic. Before execution, even VBA is semi-compiled, so all you might save is the compilation time. Besides, which would you prefer - a process that runs as quickly as possible, thereby hitting as much as 90% CPU usage, or one that keeps the CPU usage down to 25% but takes four times as long to complete (not that I'm suggesting C#-code would do this)?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Remove Compatibility Mode on DOCX files (batch)	w64bit	Word	17	02-01-2015 06:02 AM
VBA Batch Find & Replace for all MSOffice extensions, to replace File Name and Content of the File	QA_Compliance_Advisor	Word VBA	11	09-11-2014 11:51 PM
how to batch update linked image files path?	stanleyhuang	Word	3	09-11-2014 12:51 AM
batch file	romanticbiro	Office	1	06-30-2014 06:04 PM
Insert-Text from File in Word Ribbon?	tatihulot	Word	2	10-12-2011 04:40 AM