none
Trouble Importing XML Schema RRS feed

  • Question

  • So, I have a list of 900 files in Column A; it looks like this:

    C:\Users\rshuell001\Desktop\BilateralAgreement_SummaryFiles\OXRD, LLC - 2041761 - BA Name definition.xml
    C:\Users\rshuell001\Desktop\BilateralAgreement_SummaryFiles\JENIN DRIVE LLC - 2063760 - BA Name definition.xml
    C:\Users\rshuell001\Desktop\BilateralAgreement_SummaryFiles\WESTERN LLC - 2057441 - BA Name definition.xml


    And so on and so forth.  So I came up with a little VBA script to loop through the list of paths/files, and import the schema for each.  I'm trying to match the contents to the file name.

    Sub XML_Schema()
    
    Dim s As String
    
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        Set rRng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A" & LastRow)
    
        For Each rCell In rRng
        rCell.Select
            
        rCell = Replace(rCell, "&", "&")
    
        ActiveCell.Offset(0, 3).Select
        s = ActiveCell.Address
    
        ActiveWorkbook.XmlImport URL:="" & rCell & "", ImportMap:=Nothing, Overwrite:=True, Destination:=Range(s)
    
        Next rCell
        
    End Sub
    

    I have to do a small replace, because these files come to me zipped, and when you unzip them, there's a weird '&' where there should just be '&'.  I have no idea what that's about, but that's not even the problem.  I can't say for sure what the problem is, because I don't get an error, but nothing gets imported.  I know for a fact this worked last week, and I made a couple very minor changes today, and now all of a sudden it doesn't work anymore. 

    In addition to the:  "" & rCell & ""

    I tried the:  """" & rCell & """"

    That doesn't work either.  I checked in the Immediate Window and in the Watch Window. Everything appears to be fine, but it won't import anything. 

    As I get older and older, my eyes are getting worse and worse.  The solution must be right in front of me, but I just can't see it.  Does anyone here see what the problem is?

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, September 15, 2015 1:35 AM

Answers

  • >>>I know for a fact this worked last week, and I made a couple very minor changes today, and now all of a sudden it doesn't work anymore. 

    In addition to the:  "" & rCell & ""

    I tried the:  """" & rCell & """"

    That doesn't work either.  I checked in the Immediate Window and in the Watch Window. Everything appears to be fine, but it won't import anything.<<< 

    Base on your description, I have made a sample with your codes to import one xml file, but I couldn't reproduce this issue.

    Could you point out which codes you have made a couple very minor changes and share the string for the rCell ? That will help us reproduce and resolve your issue.

    Otherwise I suggest you could use return value of XlXmlImportResult to indicate whether the import succeeded, you could refer to below code:

    For Each rCell In rRng
    
            rCell.Select
    
            rCell = Replace(rCell, "&amp;", "&")
    
            ActiveCell.Offset(0, 3).Select
    
            s = ActiveCell.Address  
    
            ret = ActiveWorkbook.XmlImport(URL:="" & rCell & "", ImportMap:=Nothing, Overwrite:=True, Destination:=Range(s))
    
            Select Case ret
    
                Case XlXmlImportResult.xlXmlImportElementsTruncated
    
                    msg = "Data was truncated."
    
                Case XlXmlImportResult.xlXmlImportSuccess
    
                    msg = "XML data imported successfully."
    
                Case XlXmlImportResult.xlXmlImportValidationFailed
    
                    msg = "XML was not valid."
    
            End Select
    
            MsgBox msg
    
    Next rCell
    

    For more information, click here to refer about Workbook.XmlImport Method (Excel)

    • Marked as answer by ryguy72 Wednesday, September 16, 2015 1:58 PM
    Wednesday, September 16, 2015 8:24 AM

All replies

  • It's rCell.Value not rCell


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, September 15, 2015 1:31 PM
  • >>>I know for a fact this worked last week, and I made a couple very minor changes today, and now all of a sudden it doesn't work anymore. 

    In addition to the:  "" & rCell & ""

    I tried the:  """" & rCell & """"

    That doesn't work either.  I checked in the Immediate Window and in the Watch Window. Everything appears to be fine, but it won't import anything.<<< 

    Base on your description, I have made a sample with your codes to import one xml file, but I couldn't reproduce this issue.

    Could you point out which codes you have made a couple very minor changes and share the string for the rCell ? That will help us reproduce and resolve your issue.

    Otherwise I suggest you could use return value of XlXmlImportResult to indicate whether the import succeeded, you could refer to below code:

    For Each rCell In rRng
    
            rCell.Select
    
            rCell = Replace(rCell, "&amp;", "&")
    
            ActiveCell.Offset(0, 3).Select
    
            s = ActiveCell.Address  
    
            ret = ActiveWorkbook.XmlImport(URL:="" & rCell & "", ImportMap:=Nothing, Overwrite:=True, Destination:=Range(s))
    
            Select Case ret
    
                Case XlXmlImportResult.xlXmlImportElementsTruncated
    
                    msg = "Data was truncated."
    
                Case XlXmlImportResult.xlXmlImportSuccess
    
                    msg = "XML data imported successfully."
    
                Case XlXmlImportResult.xlXmlImportValidationFailed
    
                    msg = "XML was not valid."
    
            End Select
    
            MsgBox msg
    
    Next rCell
    

    For more information, click here to refer about Workbook.XmlImport Method (Excel)

    • Marked as answer by ryguy72 Wednesday, September 16, 2015 1:58 PM
    Wednesday, September 16, 2015 8:24 AM
  • That works.

    Thanks!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, September 16, 2015 1:58 PM