Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-13-2015, 07:55 AM
equalizer88 equalizer88 is offline VBA batch file to insert text at end of 50 files slow, 90% CPU usage Windows 7 32bit VBA batch file to insert text at end of 50 files slow, 90% CPU usage Office 2010 32bit
Novice
VBA batch file to insert text at end of 50 files slow, 90% CPU usage
 
Join Date: Jul 2015
Posts: 15
equalizer88 is on a distinguished road
Default VBA batch file to insert text at end of 50 files slow, 90% CPU usage


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
Reply With Quote
  #2  
Old 08-13-2015, 06:12 PM
macropod's Avatar
macropod macropod is offline VBA batch file to insert text at end of 50 files slow, 90% CPU usage Windows 7 64bit VBA batch file to insert text at end of 50 files slow, 90% CPU usage Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 08-14-2015, 09:46 AM
equalizer88 equalizer88 is offline VBA batch file to insert text at end of 50 files slow, 90% CPU usage Windows 7 32bit VBA batch file to insert text at end of 50 files slow, 90% CPU usage Office 2010 32bit
Novice
VBA batch file to insert text at end of 50 files slow, 90% CPU usage
 
Join Date: Jul 2015
Posts: 15
equalizer88 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 08-16-2015, 04:56 PM
macropod's Avatar
macropod macropod is offline VBA batch file to insert text at end of 50 files slow, 90% CPU usage Windows 7 64bit VBA batch file to insert text at end of 50 files slow, 90% CPU usage Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by equalizer88 View Post
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)?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
batch file insert, slow response macro, vba

Thread Tools
Display Modes


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 file to insert text at end of 50 files slow, 90% CPU usage 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
VBA batch file to insert text at end of 50 files slow, 90% CPU usage how to batch update linked image files path? stanleyhuang Word 3 09-11-2014 12:51 AM
VBA batch file to insert text at end of 50 files slow, 90% CPU usage batch file romanticbiro Office 1 06-30-2014 06:04 PM
VBA batch file to insert text at end of 50 files slow, 90% CPU usage Insert-Text from File in Word Ribbon? tatihulot Word 2 10-12-2011 04:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft