Khalil
01-19-2017, 09:31 AM
As the title suggests, I have an xml file with many keywords (i.e. numbers 1-30) that I want to replace with other keywords present in an excel sheet (i.e. 31-60). 31-60 are present in the same row. I'm trying to open the xml file, find the location of 1-30 within the xml, replace them sequentially such that 1 is replaced with 31 and 2 is replaced with 32 etc. I then want to save the new file to a new file location giving it a new name. If possible, I want to loop the process for many rows of data so that eventually I end up with many xml files with different data in them. this is the code I have so far. Im also getting a Path/File Access Error on "Open sFileName For Output As iFileNum". I'd appreciate any help. Thanks for taking the time to read this.
Option Explicit
Dim string_name(1 To 30) As Variant
Dim file_name As String
Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
Dim count As Integer
Sub replace_strings()
count = 1
Windows("Excel File Name.xlsm").Activate
Range("B4").Activate
file_name = ActiveCell
Do While ActiveCell <> ""
ActiveCell.Offset(0, 1).Activate
string_name(count) = ActiveCell
count = count + 1
Loop
sFileName = "C:\XML file location.xml"
iFileNum = FreeFile
Open sFileName For Input As iFileNum
Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum
sTemp = Replace(sTemp, "bcsx", string_name(1))
iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum
End Sub
Option Explicit
Dim string_name(1 To 30) As Variant
Dim file_name As String
Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
Dim count As Integer
Sub replace_strings()
count = 1
Windows("Excel File Name.xlsm").Activate
Range("B4").Activate
file_name = ActiveCell
Do While ActiveCell <> ""
ActiveCell.Offset(0, 1).Activate
string_name(count) = ActiveCell
count = count + 1
Loop
sFileName = "C:\XML file location.xml"
iFileNum = FreeFile
Open sFileName For Input As iFileNum
Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum
sTemp = Replace(sTemp, "bcsx", string_name(1))
iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum
End Sub