Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Append file name while importing in Column A

    Hello,

    I am interested to know is it possible to append the file name in column A while importing multiple xml files in worksheet.

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a representative XML file and the code you are using to import. [I presume the code in some way has the file name in some way, since it must it to open and import the file]

    Steve

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Could you attach a representative XML file and the code you are using to import. [I presume the code in some way has the file name in some way, since it must it to open and import the file]

    Steve
    Thanks Steve for the response, but i am sorry i don't have the sample xml files for testing, I have checked it & it does not contain any reference of file name.

    This codes do import all the .xml files from the folder.
    Code:
    Sub RFiles()
    Dim ShellApplication As Object
    Set ShellApplication = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    Path = ShellApplication.self.Path
    Set ShellApplication = Nothing
    Call ListMyFiles(Path, True)
    End Sub
    Sub ListMyFiles(mySourcePath, IncludeSubfolders)
        Set MyObject = New Scripting.FileSystemObject
        Set mySource = MyObject.GetFolder(mySourcePath)
        Application.ScreenUpdating = False
        For Each myfile In mySource.Files
            If Right(myfile.name, 3) = "XML" Or Right(myfile.name, 3) = "xml" Then
                LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
                ActiveWorkbook.XmlImport URL:=mySource & "\" & myfile.name, _
                    ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$" & LastRow + 1)
                maps = ActiveWorkbook.XmlMaps.Count
                For I = 1 To maps
                    ActiveWorkbook.XmlMaps(1).Delete
                Next I
            End If
        Next
        If IncludeSubfolders Then
            For Each MySubFolder In mySource.SubFolders
                Sub ListMyFiles(MySubFolder.Path, True)
            Next
        End If
        End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Where do you want to put the filename? It is in within the code
    Something like:

    Code:
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    range("A" & lastrow+1) = myfile.name
    ActiveWorkbook.XmlImport URL:=mySource & "\" & myfile.name, _
      ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$" & LastRow + 2)
    This will get the last current row, then put the filename in the next row in col A, then import in the row after that...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    foncesa (2014-02-15)

Posting Permissions

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