Consulting

Results 1 to 1 of 1

Thread: Path/File Access Error - Replacing text in xml with text from excel

  1. #1
    VBAX Newbie
    Joined
    Jan 2017
    Posts
    1
    Location

    Path/File Access Error - Replacing text in xml with text from excel

    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
    Last edited by Tommy; 01-19-2017 at 09:57 AM. Reason: Added code tags

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •