Just nu i M3-nätverket
Gå till innehåll
marbe537

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

Rekommendera Poster

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

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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?

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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?

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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?

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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>

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

 

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

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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?

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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å.

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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?

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser
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.

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

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.

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Skapa ett konto eller logga in för att kommentera

Du måste vara medlem för att kunna kommentera

Skapa ett konto

Skapa ett nytt konto på vårt forum. Det är lätt!

Registrera ett nytt konto

Logga in

Redan medlem? Logga in här.

Logga in nu



×
×
  • Skapa nytt...