Open, Find and Replace in Notepad

[Tiglet]

Hi,

I have a csv file (which I can't change the format of) which includes dates (which VBA tries to convert to US format).

If I load this file into Notepad, do a find ("/&quot and replace (",&quot and then save, then open .csv in Excel, the date is now spread across 3 columns and I can combine it using the date function and it will be correct.

However, I am stuck!
So far, the only thing I can get to work is:-

Sub Do_Shell()
Shell "c:\WINNT\notepad.exe", 1
End Sub

Could somebody please give me some code examples to get this to work (I have scoured the site but to no avail!!)

Thanks in advance

Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]

 

[Zathras]

Among the interesting things about VBA is the fact that old-fashioned Basic syntax still works (mostly). While it isn't feasible to update inside of Notepad, it is possible to manipulate your file directly with some Basic code:
[blue]

Option Explicit

Sub ReplaceSlashes()
Dim buffer As String
  Open "c:\test.csv" For Input As #1
  Open "c:\noslash.csv" For Output As #2
  While Not EOF(1)
    Line Input #1, buffer
    ReplaceBufferChar buffer, "/", ","
    Print #2, buffer
  Wend
  Close
End Sub

Private Sub ReplaceBufferChar(AString As String, _
       ReplaceWhat As String, WithWhat As String)
Dim i As Integer
  i = InStr(AString, ReplaceWhat)
  While i > 0
    Mid(AString, i, 1) = WithWhat
    i = InStr(AString, ReplaceWhat)
  Wend
End Sub

[/color]
If your dates are quoted, this won't quite work. You will need to tweak it a bit. But if your dates are simple xx/xx/xx between commas, this should work just fine. Of course, any slashes elswhere in the file will also be replaced by commas, so you may need to do further refinement to handle that possibility.

 

[Tiglet]

Hi,

Thanks for the tip about NotePad, no wonder I couldn't find any code!!!
Big thanks also for the code, I have put it in and it works but hey, guess what, my date is in quotes and therefore doesn't split it across the three columns in Excel.

Sorry to be a complete pain, how do I tweak it!!

Thanks in advance

Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]

 

[Zathras]

That may be a good thing. All you need to do is run a macro to turn all of those strings into dates after the worksheet has been populated.
[blue]

Option Explicit

Sub test()
  ParseDates ("G")
End Sub

Sub ParseDates(AColumn As String)
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nColumn As Integer
Dim nRow As Long
  With ActiveSheet.UsedRange
    nFirstRow = .Row
    nLastRow = .Rows.Count + .Row - 1
  End With
  nColumn = Columns(AColumn).Column
  For nRow = nFirstRow To nLastRow
    On Error Resume Next
    Cells(nRow, nColumn) = DateValue(Cells(nRow, nColumn))
  Next nRow
End Sub

[/color]
Replace the

 "G"

with whatever column your dates are actually in.