Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing XML Files (2003)

    Hi, I'm not an expert in Excel VBA. I've prepared a procedure in Access to append a series of xml files(please see attachment) into an Access table and I'd like to know what the equivalent code is in Excel of the line highlighted in red so as to append the same files into an Excel worksheet because I think pretty much the rest of the sub can be used in Excel too. Here's the Access sub:
    <pre>Private Sub cmdLoadXML_Click()
    Dim fso As Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Dim fil As Scripting.File
    ' Turn off screen updates
    Application.Echo False, "Importing XML..."
    ' Turn on error handling
    On Error GoTo cmdLoadXML_Err
    Shell """C:ProgrammiWinZipWZUnzip.exe"" -o """ & _
    Me.txtXMLDocument & """ ""C:test"""
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder("c:test")
    For Each fil In fld.Files
    ' Import the XML document
    <font color=red>Application.ImportXML "c:test" & fil.Name, acAppendData </font color=red>
    Next fil
    Kill "c:test*.xml"
    ' Exit the routine
    Exit Sub
    ' Error handling routine
    cmdLoadXML_Err:
    ' Turn screen updating back on.
    Application.Echo True
    ' Tell the user the problem
    MsgBox "An error has occurred importing the XML: " & Err.Description
    ' Exit the routine
    Exit Sub
    End Sub</pre>

    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing XML Files (2003)

    In Excel, you'd have to open the XML file in a separate window:

    Workbooks.OpenXML Filename:="c:test" & fil.Name

    Then copy/paste the data into an existing worksheet.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML Files (2003)

    Thank you so much Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. What would be the code to put in the click event of the CommandButton1 which would allow me to open the explorer window, select a file and put the name of the selected file in the textbox1 on the attached xls?
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing XML Files (2003)

    Look up GetOpenFilename or FileDialog in the Excel VBA help. Both come with examples of how to use it.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML Files (2003)

    Thank you Hans for your guidance. So there's no way to append the imported files via automation as in Access? Does one have to manually copy and paste the various Excel files' content together?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing XML Files (2003)

    I'm not an expert in XML, but as far as I know Excel doesn't offer a direct way of appending an XML file to an existing sheet. It may be possible though.

    You can copy and paste using VBA code - Copy and Paste are methods of the Range object.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML Files (2003)

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    One thing I don't understand is why the CommandButton2_Click procedure creates read-only files.
    Oh yes, I get it now. It's because the files still have the xml extension. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML Files (2003)

    I've added code to the For...Next loop in the Sub CommandButton2_Click() procedure to try and append the data in the various xml files to the Sheet2 of the attached book3.xls.
    When I try to compile though, <font color=blue>.Row </font color=blue> gets highlighted and I get the message: Invalid qualifier.
    What's the proper syntax for the line
    ws1.Range("A65536").End(xlUp).Row.Offset(1, 0).Select
    ?
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing XML Files (2003)

    ws1.Range("A65536").End(xlUp) is a range object: the last non-blank cell in column A.
    ws1.Range("A65536").End(xlUp).Row is number: the row number of this cell.
    Offset applies to a range, not a number. You should omit .Row:

    ws1.Range("A65536").End(xlUp).Offset(1, 0).Select

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML Files (2003)

    Thank you Hans, now when the sub is run, it stops at the line
    Range("A3:BE26").Select
    with the message: "Error in the Select method for the Range class"
    What could be wrong with that line?
    I've even tried to add the line
    Sheets(1).Select
    before the offending line to make sure the only sheet in the imported xml file is selected but to no avail.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing XML Files (2003)

    Declare a variable of type Workbook:

    Dim wbk As Workbook

    Then use the following code in the loop:

    Set wbk = Workbooks.OpenXML(FileName:="c:test" & fil.Name)
    wbk.Worksheets(1).Range("A3:BE26").Copy Destination:=ws1.Range("A65536").End(xlUp).Offset( 1, 0)

    This copies the range without selecting the worksheet or range.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML Files (2003)

    You're a God!!! <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>
    Thank you so much Hans <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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