PDA

View Full Version : Path/File Access Error - Replacing text in xml with text from excel



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