Läsa in data till excel från xml-filer via makro

[marbe537]

Jag har ett antal olika xml-filer som följer en viss standard, dvs jag har ett antal olika element som jag skulle vilja läsa ut och lista upp i excel.

 

Tex skulle jag vilja löpa igenom alla filerna och lista upp ett fält från alla dessa filer i en kolumn.

 

Exempel:

Det finns ett fält som heter skapad av

i xml-filen (fil1.xml)  ser det ut som ... <skapad av> Kalle kula</skapad av>

 

I min excel fil vill jag i B1 lista vilket fält jag letar efter (skapad av) och i B2 då få svaret Kalle Kula

I A1 listar jag sökvägen till mappen med xml-filerna

I A2 har jag en listar jag filnamnet fil1.xml

 

Tänker mig sedan att fylla på med ett antal filer i B3.. B20 och då ska när jag triggar makrot, uppgifter för filerna läsas in till B-kolumnen.

 

Då filnmanen ändrar sig under resans gång, skulle det vara riktigt bra om man hade ett annat makro, typ initiera fil, som med hjälp av sökvägen från A1, fyller i alla xml-filer i den angivna mappen i A2 och nedåt..

 

Några tips om hur jag skapar makrot som triggar ovanstående?

 

mina xml-filer skapas med <?xml version="1.0"?>

 

 

exempel.xlsx

[Monshi]

Frågan är hur man ska gå tillväga. Antingen låter man Excel importera XML-filen och ur detta leta reda på det du önskar eller så läser man in filen enbart i VBA och söker genom kod där.

 

Men enklast om du bara ska ha en post är nog att

1: Skapa en import av XML som text till ett blad. Det blir en Querytable som man via kod ganska enkelt kan uppdatera med andra filnamn.

2: Ha formel, eller kod iofs, som söker efter denna tag i filel/cellerna.

3: Kopiera resultatet till önskad plats

4: Öppna ny fil, gå till 2.

 

Strunta helt i XML-formateringen, hantera som text. Absolut enklast om du bara ska ha ett unikt värde.

 

 

Hur mycket av detta klarar du själv?

 

[Monshi]

Fast ser nu att du inte bara ska ett unikt värde..

 

Ska du extrahera flera poster ur XML?

 

Kanske se

http://msdn.microsoft.com/en-us/library/dd819387%28v=office.12%29.aspx

där det finns VBA-lösningar som kan hjälpa.

[marbe537]

Fast ser nu att du inte bara ska ett unikt värde..

 

Ska du extrahera flera poster ur XML?

 

Kanske se

http://msdn.microsoft.com/en-us/library/dd819387%28v=office.12%29.aspx

där det finns VBA-lösningar som kan hjälpa.

Jag har ett antal filer med flertalet xml-element, min tanke var kunna läsa in dessa till Excel för att snabbt kunna kolla av olikheter, dvs där elementinnehållet skiljer sig åt.

Filnamnen är relativt konstanta men innehållet i xml-filerna uppdateras kontinuerligt.

 

Jag tänkte mig ett makro som läser in alla elementinnehåll (för elementnamn givna i rad 1) i alla filer jag angett i A kolumnen.

 

Någon som kan tänka sig att sätta sig in i hur man gör ett makro som öppnar upp en xml-fil och plockar ut det innehåll jag valt att jag vill lista?

[Monshi]

Visst kan jag...
Men kan du göra något själv eller ska jag skriva allt?
 
Filhanteringen inget problem, bara poppa upp en dialog där du markera de filer du vill läsa in.
Koden kan skriva in filnamnen i celler, öppna/importera fil för fil (som sagt en querytable är enkel att använda) och söka igenom dem efter nyckelorden.
Men som jag undrar ovan, är det en enkel post du vill åt enbart, värdet för en nyckel?
eller är det flera av stil med

<skapadav>Test Person</skapadav> 
<record> 
                    <LastName>Smith</LastName> 
                   <Sales>16753</Sales> 
                   <Country>UK</Country>
</record> 
<record> 
                   <LastName>Johnson</LastName> 
                   <Sales>14808</Sales> 
                   <Country>USA</Country> 
</record>

Där du vill få ut alla <record>?

 
 
 
och hur stora filer?

[marbe537]

Visst kan jag...

Men kan du göra något själv eller ska jag skriva allt?

 

Filhanteringen inget problem, bara poppa upp en dialog där du markera de filer du vill läsa in.

Koden kan skriva in filnamnen i celler, öppna/importera fil för fil (som sagt en querytable är enkel att använda) och söka igenom dem efter nyckelorden.

Men som jag undrar ovan, är det en enkel post du vill åt enbart, värdet för en nyckel?

eller är det flera av stil med

<skapadav>Test Person</skapadav> 
<record> 
                    <LastName>Smith</LastName> 
                   <Sales>16753</Sales> 
                   <Country>UK</Country>
</record> 
<record> 
                   <LastName>Johnson</LastName> 
                   <Sales>14808</Sales> 
                   <Country>USA</Country> 
</record>

Där du vill få ut alla <record>?

 

 

 

och hur stora filer?

 

Hej igen!

Först; Skulle vara tacksam om jag fick åtminstone god vägledning med att få till filen. Känner inte att jag löser tex 

 

"Filhanteringen inget problem, bara poppa upp en dialog där du markera de filer du vill läsa in" sjäv! 

Därför tacksam för all hjälp jag kan få!

Jag har väldigt små filer. Innehåller typiskt bara 10 olika poster och alla med en unik element post. (som dock kan vara rätt lång ibland men den får plats i en cell i excel.

 

Dvs mins xml-filer ser mer ut som:

<skapadav>Test Person</skapadav>

 <LastName>Smith</LastName>

<Sales>16753</Sales>

<Country>UK</Country>

[Monshi]

Se bifogad fil

Koden i den:

Sub MyFilePicker()
Dim vrtSelectedItem As Variant
    Dim rnTarget As Range
    Set rnTarget = Sheet1.Range("A2")
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Add "XLM", "*.xml", 1
         If .Show = -1 Then

            
            For Each vrtSelectedItem In .SelectedItems
                
                QT_Updater vrtSelectedItem
                rnTarget.Cells(1, 1) = getFileName(vrtSelectedItem)
                rnTarget.Cells(1, 2) = Blad1.Range("rnResultat")
                Set rnTarget = rnTarget.Offset(1)
                
                

            Next vrtSelectedItem
       
        Else
        End If
    End With


End Sub

Function getFileName(filePath As Variant) As String
    Dim i As Integer
    Dim strT As String
    i = InStr(1, filePath, "\", vbTextCompare)
    strT = filePath
    While i <> 0
        strT = Right(strT, Len(strT) - i)
        i = InStr(1, strT, "\", vbTextCompare)
    Wend
    getFileName = strT
End Function

Sub QT_Updater(fileName As Variant)
    With Blad1.QueryTables(1)
    .TextFilePromptOnRefresh = False
        .Connection = "TEXT;" & fileName
        .Refresh
    End With
End Sub

[marbe537]

 

Se bifogad fil

Koden i den:

Sub MyFilePicker()
Dim vrtSelectedItem As Variant
    Dim rnTarget As Range
    Set rnTarget = Sheet1.Range("A2")
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Add "XLM", "*.xml", 1
         If .Show = -1 Then

            
            For Each vrtSelectedItem In .SelectedItems
                
                QT_Updater vrtSelectedItem
                rnTarget.Cells(1, 1) = getFileName(vrtSelectedItem)
                rnTarget.Cells(1, 2) = Blad1.Range("rnResultat")
                Set rnTarget = rnTarget.Offset(1)
                
                

            Next vrtSelectedItem
       
        Else
        End If
    End With


End Sub

Function getFileName(filePath As Variant) As String
    Dim i As Integer
    Dim strT As String
    i = InStr(1, filePath, "\", vbTextCompare)
    strT = filePath
    While i <> 0
        strT = Right(strT, Len(strT) - i)
        i = InStr(1, strT, "\", vbTextCompare)
    Wend
    getFileName = strT
End Function

Sub QT_Updater(fileName As Variant)
    With Blad1.QueryTables(1)
    .TextFilePromptOnRefresh = False
        .Connection = "TEXT;" & fileName
        .Refresh
    End With
End Sub

Tack för detta!

Jag hittar dock ingen bifogad fil och det verkar som att en dle av koden är beroende av denna, så tacksam om du kunde bifoga filen.

När jag kopierar in koden, får jag fel i QT_Updater, då den inte hittar querytablen

[Monshi]

Ajfan filen försvann

 

Här är den

 

 

exempel.zip

[marbe537]

Ajfan filen försvann

 

Här är den

Nu så börjar jag få grepp på utläsningen!

Stor tack!

Jag har nu lagt till så att jag löper igenom ett antal kolumner, men sprang på ett problem då jag några xml-poster som är mer än 250 tecken långa.

Det verkar som att funktionen MATCH inte får träff på dessa fält, om cellen där värdet finns har mer än 250 tecken.

Något förslag på hur man kan komma runt detta?

[Monshi]

250 tecken?

Två möjliga vägar

1: man använder FIND i VBA istället.

 

2: man låter Excel läsa in xml-filen som en XML-fil men... ja... gillar inte det men det kan gå.

[marbe537]

250 tecken?

Två möjliga vägar

1: man använder FIND i VBA istället.

 

2: man låter Excel läsa in xml-filen som en XML-fil men... ja... gillar inte det men det kan gå.

Löste det istället med att göra 2 extra kolumner bredvid xml-datan, där jag plockar ut element namnet i en kolumn och innehållet i en annan mha textmanipulation av hela strängen. Sedan hämtar jag data via Vlookup och elementnamnet.

 

Nu återstår att fiska ut sista mappen i sökvägen och lista den i en kolumn. Denna innehåller typiskt vilken karaktär av xml-fil det är.

Problemet är följande:

Har en sökväg enligt C:\xxx\yyy\filen.xml och skulle vilja plocka ut yyy.

På vilken nivå filen finns, dvs hur många \ som krävs är varierande.

Jag tänker mig en funktion liknande den du gjorde för getFileName, men där man ska plocka ut sista mappen istället.

 

Uppslag?

[Ove Söderlund]

C:\xxx\yyy\filen.xml
      ^   ^
pos1 =|   |= pos2

Jag skulle ta in strängen i en loop och stega från strängens "höger" (strängfunktion Right) och fånga upp pos2 respektive pos1 och ta ut strängen mellan dessa pos med strängfunktionen Mid.

[Monshi]

Har ju redan en funktion som plockar ut filnamnet genom arbeta sig igenom strängen rekursivt. Bara att anpassa denna till att plocka ur mappnamnet.

 

Finns flera sätt... du kommer nog på ett.