k0st4din
05-22-2014, 10:10 PM
Hello to all
over a week ago I made a request for some help with a macro, but so far I have no answer. Therefore I beg you if someone is able to help me with it ( because you have to change a little , but I do not know how to do it)
First I do not know how to change what has to change in order to be elected even din file (because it is currently allows open ( only reach a certain folder) that contains the .xml file)
The second error is that as the macro stops and does not want to copy the information and put it in my main file.
Thank you in advance for providing help on your part.
Really is very important that macro
Asking did in this forum (http://www.mrexcel.com/forum/excel-questions/777060-macro-take-information-xml-file-put-xls-file.html)
Sub ImportXMLData()
Application.ScreenUpdating = False
'declaring variables for sub
Dim strFolder As String, strFile As String
Dim xlWkbk As Workbook, xmlFile As Workbook, LastRow As Long
'calling function to open folder and browse for directory
strFolder = GetFolder
'if folder is empty close the sub
If strFolder = "" Then Exit Sub
'indicates browsing for xml files in the selected directory
strFile = Dir(strFolder & "\*.xml", vbNormal)
'set the excel sheet as the active one
Set xlWkbk = ActiveWorkbook
'loop procedure for all files in folder
While strFile <> ""
LastRow = xlWkbk.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Set xmlFile = Workbooks.OpenXML(Filename:=strFolder & "\" & strFile, LoadOption:=xlXmlLoadImportToList)
xmlFile.Sheets(1).UsedRange.Copy
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
xlWkbk.Sheets(1).Range("A" & LastRow + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
strFile = Dir()
Wend
Set xmlFile = Nothing: Set xlWkbk = Nothing
Application.ScreenUpdating = True
End Sub
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
over a week ago I made a request for some help with a macro, but so far I have no answer. Therefore I beg you if someone is able to help me with it ( because you have to change a little , but I do not know how to do it)
First I do not know how to change what has to change in order to be elected even din file (because it is currently allows open ( only reach a certain folder) that contains the .xml file)
The second error is that as the macro stops and does not want to copy the information and put it in my main file.
Thank you in advance for providing help on your part.
Really is very important that macro
Asking did in this forum (http://www.mrexcel.com/forum/excel-questions/777060-macro-take-information-xml-file-put-xls-file.html)
Sub ImportXMLData()
Application.ScreenUpdating = False
'declaring variables for sub
Dim strFolder As String, strFile As String
Dim xlWkbk As Workbook, xmlFile As Workbook, LastRow As Long
'calling function to open folder and browse for directory
strFolder = GetFolder
'if folder is empty close the sub
If strFolder = "" Then Exit Sub
'indicates browsing for xml files in the selected directory
strFile = Dir(strFolder & "\*.xml", vbNormal)
'set the excel sheet as the active one
Set xlWkbk = ActiveWorkbook
'loop procedure for all files in folder
While strFile <> ""
LastRow = xlWkbk.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Set xmlFile = Workbooks.OpenXML(Filename:=strFolder & "\" & strFile, LoadOption:=xlXmlLoadImportToList)
xmlFile.Sheets(1).UsedRange.Copy
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
xlWkbk.Sheets(1).Range("A" & LastRow + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
strFile = Dir()
Wend
Set xmlFile = Nothing: Set xlWkbk = Nothing
Application.ScreenUpdating = True
End Sub
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function