Open XML file in Notepad and delete all "-"es

[b.hill]

Hi guys, novice here. I am trying to:

1) Open an XML file (C:\Users\GKC\Documents\data.xml) in Notepad
2) Clear the current contents
3) Paste the new data (data has already been copied)
4) delete "<?xml version="1.0" encoding"UTF-8" standalone="True"?>"
5) delete all "-" characters in the file

I have figured out how to open the XML file in Notepad with the code below, however I need some help on 2 through 5.

[VBA]Shell "notepad.exe C:\Users\GKC\Documents\data.xml", vbMaximizedFocus[/VBA]

Below are some similar threads that may help you guys help me out on this one, but there's nothing in them to do with opening an XML document in Notepad.

http://www.vbaexpress.com/forum/showthread.php?t=13737

http://www.vbaexpress.com/forum/showthread.php?t=27747

http://social.msdn.microsoft.com/For...-098928957165/

[Jacob Hilderbrand]

Using Shell will open the file with the application you specify, in this case Notepad, but you can't do much else with it in the application.

What you need to do is read the text, make the replacements, and create a new text file.

Try this.

[VBA]
Option Explicit

'Set a reference to Microsoft Scripting Runtime.

Sub ReplaceText()

Dim FSO As New FileSystemObject
Dim Stream As TextStream
Dim Text As String
Dim Filename As String

'Set File Name
Filename = "C:\Users\Jacob\Desktop\Test.xml"

'Open Text Stream
Set Stream = FSO.OpenTextFile(Filename)

'Read Text
Text = Stream.ReadAll

'Close Stream
Stream.Close

'Replace Text 1
Text = Replace(Text, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding" & Chr(34) & _
"UTF-8" & Chr(34) & " standalone=" & Chr(34) & "True" & Chr(34) & "?>", "", , , vbTextCompare)

'Replace Text 2
Text = Replace(Text, "-", "", , , vbTextCompare)

'Delete File
Kill Filename

'Create New File
Open Filename For Append As #1

'Write Text
Print #1, Text

'Close File
Close #1

'Release Object Variables
Set Stream = Nothing
Set FSO = Nothing

End Sub
[/VBA]

Chr(34) is just a quote mark (").

[b.hill]

Thank you! The code works great for deleting all instances of "-" and "<?xml version="1.0" encoding"UTF-8" standalone="True"?>".

If I have already copied the new data into the clipboard, is there a way for me to delete the current contents of the XML file and insert/paste the new data before deleting all instances of "-" and "<?xml version="1.0" encoding"UTF-8" standalone="True"?>"?

[Jacob Hilderbrand]

First store the text to a variable, then make the replacements you want, then write the final text to the file.

[Kenneth Hobs]

If you don't know how to get the clipboard content, here is an example.
[VBA]
Function getClipboard()
'Add Reference: 'Reference: Microsoft Forms xx Object
Dim MyData As DataObject

On Error Resume Next
Set MyData = New DataObject
MyData.GetFromClipboard
getClipboard = MyData.GetText
End Function

Sub Test_getClipboard()
Dim s As String, lineCount As Long
s = getClipboard
lineCount = UBound(Split(s, vbCrLf)) + 1
MsgBox s, vbInformation, "Line Count: " & lineCount
End Sub[/VBA]

[b.hill]

I am having trouble combining these two codes to get the desired result. If I recognize my inabilities, can one of you guys please help me out?

[Kenneth Hobs]

While one can use fso methods as DRJ did to read and write text files, I tend to use the VBA methods. Here are some read and write routines. You just need to put the pieces together that you need.

See where I wrote the string ""? Just insert the routine from the clipboard routine with the Replace() routine. That all can be in one line or poke the clipboard into a string and then use replace on that and then write the file.

[VBA]Sub MakeEmptyFiles()
Dim sDir As String, i As Integer

sDir = ActiveWorkbook.Path & "\Test"
If Dir(sDir, vbDirectory) = "" Then MkDir sDir

For i = 1 To 3
MakeTXTFile sDir & "\Test_" & i & ".txt", ""
Next i
End Sub

Sub MakeTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(FolderPart(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If

hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Write #hFile, , str
Close hFile
End Sub[/VBA]

[VBA]Sub test_TXTVal()
MsgBox 1 + TXTVal(ActiveWorkbook.Path & "\Counter.txt")
End Sub

Function TXTVal(filePath As String) As Long
'Expect one numeric long type in the file.
Dim str As Long, hFile As Integer
'str = 0

'Create TXT file if it doesn't exist and add 0 to it.
If Dir(filePath) = "" Then
hFile = FreeFile
Open filePath For Input As hFile
Put #hFile, , str
Close hFile
TXTVal = str
Exit Function
End If

'Get value from TXT file.
hFile = FreeFile
Open filePath For Binary Access Read As #hFile
Get #hFile, , str
Close hFile
TXTVal = str

End Function


[/VBA]

[b.hill]

Thank you guys so much for the help!