How to make a global replace into the Excel file?

[Aag-Man]

The below mentioned code is used to delete the column and save the excel file on the system of mine.

 dim XL as OLEObject 'Excel Application
    dim WkBook as OLEObject 'Excel Workbook
    XL = new OLEObject("Excel.Application",true)
    WkBook = XL.Workbooks.Open("fileToEdit.xls")
    XL.Columns("R").delete
    XL.Activeworkbook.SaveAs("fileResult.xls")
    XL.Activeworkbook.Close

Well I am looking to have global replace for all the semicolon into the excel spreadsheet. Let me know if you are having any speculation on this particular matter. I am waiting for the prompt replies of yours. thanks a lot in advance.

[Villiers]

Well looking at the requirement of yours I am suggesting following code which you should try and see whether it is working or not. You should copy and paste the above mentioned command.

Dim excel as new ExcelApplication
  excel.Workbooks.Add
  excel.visible = true
  Dim Answer as object
  
  //Create some data
  excel.Range("B2").Value = "Sarah Doe"
  excel.Range("B3").Value = "Sara Doe"
  excel.Range("C1").Value = "Tom Smith"
  
  msgbox "Added data. Lets find only Sara Doe"
  
  //Replace all "Sara Doe" with "Sarah Doe"
  excel.Cells.Replace("Sara Doe","Sarah Doe",office.xlWhole,office.xlByRows,False, False, False)
  
  excel = Nil
  
Exception err as OLEException
  MsgBox str(err)

[Ronchi]

In order to get the requirement of yours I am suggesting following code which you should run on the computer.

dim XL as OLEObject 'Excel Application
    dim WkBook as OLEObject 'Excel Workbook
    XL = new OLEObject("Excel.Application",true)
    WkBook = XL.Workbooks.Open("fileToEdit.xls")
    XL.Columns("R").delete
    XL.Cells.Replace(";","",office.xlWhole,office.xlByRows,False, False, False)
    XL.Activeworkbook.SaveAs("fileResult.xls")
    XL.Activeworkbook.Close

the above mentioned code will replace all the semicolon with the black space and also unique data is presented into the cell.

[Aag-Man]

Well thanks a lot for the prompt replies of yours. I have used the above mentioned code and I have noticed that this particular code is used replace the all the semicolon by using the blank space. However I have noticed that if the cell is having ‘john; Smith’ than the code is ignoring the semi colon itself. I don’t know why the code is not working as it is expected. Let me know if you are having any specific solution to get the requirement of mine.

[Dwarner]

The code is working fine to get the requirement of yours. however you need to change the code office.xlWhole to office.xlpart. after doing the same you should try to run the code on the system of yours and see whether it is working or not. If you are still any query than feel free to ask about the same. I am hoping that it would be useful to get the requirement of yours.

[Author]

In this particular matter I am suggesting below mentioned code which you should use and let me know whether it was useful to you or not.

Private Declare Function GetFileAttributes Lib "Kernel32" Alias "GetFileAttributesA" (ByVal strPath As String) As Long

Function ExistFileDir(sSpec As String) As Boolean
Dim af As Long
af = GetFileAttributes(sSpec)
ExistFileDir = (af -1)
End Function

Sub GetPathAndMask(ByRef Path As String, ByRef Mask As String)
Dim x As Integer 'loop counter
Dim bExists As Boolean

For x = Len(Path) To 1 Step -1
If (Mid$(Path, x, 1) = "\") Or _
(Mid$(Path, x, 1) = "/") Then
Mask = Mid$(Path, x + 1, Len(Path) - x)
Path = Left$(Path, x)
bExists = ExistFileDir(Path)
If (bExists = False) Then
Path = ""
End If
Exit Sub
End If
Next x
End Sub


Function GetSubdirs(directory_text As String, dir_array() As String, counter As Integer) As Integer

Dim temp_var As String


On Error Resume Next


temp_var = Dir(directory_text, vbDirectory)


localcounter = 0

Do Until temp_var = ""

If temp_var "" Then

If GetAttr(directory_text & temp_var) And vbDirectory And Mid$(temp_var, 1, 1) "." Then

ReDim Preserve dir_array(counter)

dir_array(counter) = directory_text & temp_var & "\"

counter = counter + 1
localcounter = localcounter + 1
End If
End If

temp_var = Dir()
Loop

Dim x As Integer 'loop counter
Dim CurDir As String
For x = 1 To localcounter Step 1
CurDir = dir_array(x - 1 + counter - localcounter)
counter = GetSubdirs(CurDir, dir_array, counter)
Next x

GetSubdirs = counter
End Function

Private Sub CloseBtn_Click()
Unload QueryForm
End Sub

Private Sub FindBtn_Click()
' just find, no replace
Call DoReplace(wdReplaceNone)
End Sub

Private Sub ReplaceBtn_Click()
' replace it
Call DoReplace(wdReplaceAll)
End Sub

Private Sub DoReplace(ReplaceMode As Long)

Dim FilePath As String
Dim Mask As String
Dim LogFile As String
Dim ChangeCount As Integer

If (Search_Box.Text = "") Then
MsgBox "Please enter a Search String"
Exit Sub
End If

FilePath = FileMask_Box.Text
GetPathAndMask FilePath, Mask
If ((FilePath = "") Or (Mask = "")) Then





Call MsgBox("Invalid Path! Please enter a valid file path! ", vbOKOnly + vbCritical + vbApplicationModal + vbDefaultButton1, "Invalid Input")



Exit Sub
End If

LogFile = FilePath & "Cleanser_Results.txt"

ChangeCount = ReplaceInDir(LogFile, FilePath, Mask, ReplaceMode)
If (Search_Subdir.Value = True) Then
Dim dir_array() As String
Dim NumDirs As Integer
NumDirs = GetSubdirs(FilePath, dir_array, 0)
Dim x As Integer 'loop counter
Dim CurDir As String
SubCount = 0
For x = 1 To NumDirs Step 1
CurDir = dir_array(x - 1)
SubCount = SubCount + ReplaceInDir(LogFile, CurDir, Mask, ReplaceMode)
Next x
End If

If ReplaceMode = wdReplaceNone Then
MsgBox (ChangeCount + SubCount) & " file(s) found"
Else
MsgBox (ChangeCount + SubCount) & " file(s) changed"
End If

End Sub

Function ReplaceInDir(LogFile As String, Path As String, Mask As String, ReplaceMode As Long) As Integer
Dim ChangedCount As Integer
Dim FileName As String
Dim aStory As Range
Dim FindCount As Long

ChangedCount = 0
FindCount = 0
FileName = Dir(Path & Mask)

Do Until FileName = ""
DoEvents
On Error GoTo CantOpenFile
Documents.Open FileName:=Path & FileName

If Process_Field_Codes.Value = True Then
ActiveWindow.View.ShowFieldCodes = True
End If

On Error GoTo 0
On Error GoTo CantChangeTracking
On Error GoTo 0
ActiveDocument.Saved = True 'don't count ShowRevisions
' Can't change protected documents
If ActiveDocument.ProtectionType wdNoProtection Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
GoTo DocumentProtected
End If

For Each aStory In ActiveDocument.StoryRanges
aStory.Find.Execute _
FindText:=Search_Box.Text, _
ReplaceWith:=Replace_Box.Text, _
MatchCase:=Match_Case.Value, _
MatchWildcards:=Reg_Exp.Value, _
MatchWholeWord:=Whole_Word.Value, _
Replace:=ReplaceMode

If aStory.Find.Found Then FindCount = FindCount + 1
Next aStory

'Repeat the process after toggling Field Codes
If Process_Field_Codes.Value = True Then
ActiveWindow.View.ShowFieldCodes = Not ActiveWindow.View.ShowFieldCodes
For Each aStory In ActiveDocument.StoryRanges
aStory.Find.Execute _
FindText:=Search_Box.Text, _
ReplaceWith:=Replace_Box.Text, _
MatchCase:=Match_Case.Value, _
MatchWildcards:=Reg_Exp.Value, _
MatchWholeWord:=Whole_Word.Value, _
Replace:=ReplaceMode
If aStory.Find.Found Then FindCount = FindCount + 1
Next aStory
End If

If Not ActiveDocument.Saved Then 'then we must have changed it
Open LogFile For Append As #1
Print #1, Date & " " & Time & " Changed " & Chr$(34) & Search_Box.Text & Chr$(34) & " to " & Chr$(34) & Replace_Box.Text & Chr$(34) & " in " & Path & FileName
Close #1
On Error GoTo CantSaveFile
ActiveDocument.Save
ChangedCount = ChangedCount + 1
Else
' print a log even if found something
If (ReplaceMode = wdReplaceNone) And FindCount > 0 Then
Open LogFile For Append As #1
Print #1, Date & " " & Time & " Found " & Chr$(34) & Search_Box.Text & Chr$(34) & " in " & Path & FileName
Close #1
On Error GoTo CantSaveFile
ChangedCount = ChangedCount + 1
End If
End If
FindCount = 0

ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
NextFile:
FileName = Dir
Loop

ReplaceInDir = ChangedCount
Close
On Error GoTo 0

GoTo AllDone

CantChangeTracking:
Open LogFile For Append As #1
Print #1, Date & " " & Time & " Could not modify Tracking status on " & Path & FileName
Close #1
Resume Next
CantSaveFile:
Open LogFile For Append As #1
Print #1, Date & " " & Time & " Could not save " & Path & FileName
Close #1
Resume Next
CantOpenFile:
Open LogFile For Append As #1
Print #1, Date & " " & Time & " Could not open " & Path & FileName
Close #1
Resume NextFile

DocumentProtected:
Open LogFile For Append As #1
Print #1, Date & " " & Time & " Could not process protected document " & Path & FileName
Close #1
GoTo NextFile

AllDone:
End Function