Skapa fillista i Excel

[miclj]

Hej..

 

Jag sitter och försöker förstå mig på lite VBA i Excel, men går väl sådär..

Har en kodsnutt som tittar i en mapp och listar filerna som finns där (filnamn, datum, länk)..

Detta verkar jag ha fått snurr på och excel skriver ner min information..

 

Men nu vill jag utöka lite funktionalitet, där kör jag fast..

 

Säg att jag modifierar vissa filer som jag har i min ursprungsmapp (och har läst in information i excel) och lägger dem i en annan mapp..

Jag har filer med samma namn, men med annat datum samt sökväg..

Kan man få en funktion som gör att excel jämför min data i kalkylbladet emot den nya mappen jag vill titta i och endast uppdatera vald information på excelbladet (datum, sökväg)?

 

Den koden jag har gör gör så att excel fortsätter fylla på kalkylbladet, vilket gör att jag får dubbletter.. 

'Sub rad()

'End Sub

'Force the explicit delcaration of variables
Option Explicit

Sub ListFiles()


    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)
    
    'Declare the variables
    Dim objFSO As Scripting.FileSystemObject
    Dim objTopFolder As Scripting.Folder
    Dim strTopFolderName As String
   


    
    'Assign the top folder to a variable
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "Välj mapp med filer till granskning"
        .Show
        If .SelectedItems.Count = 0 Then MsgBox "Operation Cancelled by the user", vbExclamation + vbOKOnly, "List Files": Exit Sub
        strTopFolderName = .SelectedItems(1)
    End With
    
    ' create a new sheet
    ' ThisWorkbook.Sheets.Add(after:=Sheets(Sheets.Count)).Name = Mid$(strTopFolderName, InStrRev(strTopFolderName, "\") + 1)
    
    'Insert the headers for Columns A through F
    Range("A3").Value = "Dokument"
    Range("C3").Value = "Datum"
    'Range("D3").Value = "Fil Typ"
    'Range("E3").Value = "Sökväg"
    Range("D3").Value = "Länk"
        
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Get the top folder
    Set objTopFolder = objFSO.GetFolder(strTopFolderName)
    
    'Call the RecursiveFolder routine
    Call RecursiveFolder(objTopFolder, True)
    
    'Change the width of the columns to achieve the best fit
    Columns.AutoFit
    
End Sub

Sub RecursiveFolder(objFolder As Scripting.Folder, _
    IncludeSubFolders As Boolean)
    
    'IncludeSubFolders = False
    
    If Blad6.chkBoxIsSubFolder.Value = True Then
                IncludeSubFolders = True
            Else
                IncludeSubFolders = False
                End If

    'Declare the variables
    Dim objFile As Scripting.File
    Dim objSubFolder As Scripting.Folder
    Dim NextRow As Long
    
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'Loop through each file in the folder
    For Each objFile In objFolder.Files
        Cells(NextRow, "A").Value = objFile.Name
        'Cells(NextRow, "D").Value = objFile.Type
        Cells(NextRow, "C").Value = objFile.DateLastModified
        'Cells(NextRow, "E").Value = objFile.Path
        Cells(NextRow, "D").Select
            Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
            objFile.Path, TextToDisplay:="Öppna fil"
        
        
        
        NextRow = NextRow + 1
    Next objFile
    
    'Loop through files in the subfolders
    If IncludeSubFolders Then
        For Each objSubFolder In objFolder.SubFolders
            Call RecursiveFolder(objSubFolder, True)
        Next objSubFolder
    End If
    
End Sub

[Monshi]

Jag målar ut hur du ska göra så kanske du kan fixa det.

 

1: Skapa en subrutin i din kod som tar filnamnet som invärde och returnerar antingen noll om inte dubblett eller ett värde, radnummer, om dubblett.

2. Låt koden anropa denna rutin och om värde större än noll skriv in filnamnet på den raden, annars fyll på i slutet.

 

Hur fungerar sub-rutinen i steg ett? ja, det är där problemet finns. Kanske se till att söka efter enbart filnamnet, utan sökväg, i de data som redan finns och om träff returnera raden. Om behövs använd även andra egenskaper som filen har, som senast ändrad/skapad

[miclj]

Jag har väldigt begränsade kunskaper i VBA, då detta är typ första gången jag försöker göra något 

 

Försöker googla och läsa lite här och där för att se hur man bygger kod.. Så om du har tid/lust vore lite mer konkret info/kod till stor hjälp..

[Ove Söderlund]

Jag ska nog vara ärlig och säga att börja med filhanteringsfunktioner i Excel VBA kanske inte är första prioritet när man har begränsade programmeringskunskaper i VBA. Fast lite läsvärt på vägen till den funktionalitet du försöker få fram kan jag bidra med:

http://www.exceltip.com/files-workbook-and-worksheets-in-vba/basic-file-and-folder-examples-using-vba-in-microsoft-excel.html

 

Om man vill försöka lära sig VBA lite mer seriöst, lite komma-igång-info här:

http://www.homeandlearn.org/

[Monshi]

Börja med ett skelett

Function DoFileExist(fileName as String) As Integer

End Function

Kanske det behövs fler argument in men jag antar att filnamnet är allt som krävs

 

 

i stil med denna

Function DoFileExist(fileName As String) As Integer
    Dim c As Range
    Dim rnToSearch
    Set rnToSearch = Blad1.Range("a1").CurrentRegion.Resize(, 1)
    Set c = rnToSearch.Find(fileName)
    If c Is Nothing Then
        DoFileExist = 0
    Else
        DoFileExist = c.Row
    End If
End Function


Sub test()

    Debug.Print DoFileExist("helloworld")
    
End Sub

MEN denna söker på delsträng så kanske Find behöver ändras till

 

 Set c = rnToSearch.Find(fileName, lookat:=xlWhole)

så att enbart exakt match returneras.

 

Ta sedan detta radvärde för att styra om så att antingen filnamnet inte skrivs ut eller att den skrivs ut på nytt så att eventuell sökvägsändring slår igenom.

 

Sökmetoden ovan utgör från att varje filnamn är unikt.

[miclj]

Jag ska nog vara ärlig och säga att börja med filhanteringsfunktioner i Excel VBA kanske inte är första prioritet när man har begränsade programmeringskunskaper i VBA. Fast lite läsvärt på vägen till den funktionalitet du försöker få fram kan jag bidra med:

http://www.exceltip.com/files-workbook-and-worksheets-in-vba/basic-file-and-folder-examples-using-vba-in-microsoft-excel.html

 

Om man vill försöka lära sig VBA lite mer seriöst, lite komma-igång-info här:

http://www.homeandlearn.org/

 

Nej det är väl inte det bästa att börja med.. Men jag behövde funktionen för att spara tid, å man måste ju försöka innan man ger upp 

 

Tack för länkarna..

[Ove Söderlund]

Jag funderar lite på vad du försöker göra/vill göra när du har modifierat filer och sparat i annan mapp.

Är det så att du på något vis vill jämföra originaldokumentet med det modifierade och därefter välja vilket du vill behålla så behövs någon form av kontrollrutin där du kan välja vilket som ska sparas och sedan ta bort från "modifieringsmappen".

Alternativt,

Om det är de modifierade filerna som är det som gäller, i så fall är det bara att kopiera dessa till ursprungsmappen och spara, samt bekräfta skriva över originalet.

[miclj]

Jag funderar lite på vad du försöker göra/vill göra när du har modifierat filer och sparat i annan mapp.

Är det så att du på något vis vill jämföra originaldokumentet med det modifierade och därefter välja vilket du vill behålla så behövs någon form av kontrollrutin där du kan välja vilket som ska sparas och sedan ta bort från "modifieringsmappen".

Alternativt,

Om det är de modifierade filerna som är det som gäller, i så fall är det bara att kopiera dessa till ursprungsmappen och spara, samt bekräfta skriva över originalet.

 

Min tanke är såhär..

Säg att jag arbetar med massa textdokument. Dessa vill jag ha någon form av enkel versionshantering på, så jag sparar de i en mapp som heter 1.0

Sen vill jag skapa en förteckning över mina dokument, det gör jag i excel där jag listar filnamn, datum, sökväg..

 

Men säg jag måste ändra i några dokument, dvs göra en ny version. När jag är färdig med dokumenten sparar jag dem i en ny mapp som heter 2.0

Då vill jag uppdatera min förteckning i excel. Kör jag mitt skript så kommer det fylla på med filerna i mapp 2.0, vilket gör att jag får dubbletter i min förteckning. 

Därför vill jag ha någon sökning som kollar om dokumentet redan finns i förteckningen och isåfall uppdaterar vissa fält.

 

Eftersom jag vill ha en enklare versionhantering så vill jag inte skriva över filer, utan använda mig av flera mappar. Där det blir lite bökigt

[miclj]

Börja med ett skelett

Function DoFileExist(fileName as String) As Integer

End Function

Kanske det behövs fler argument in men jag antar att filnamnet är allt som krävs

 

 

i stil med denna

Function DoFileExist(fileName As String) As Integer
    Dim c As Range
    Dim rnToSearch
    Set rnToSearch = Blad1.Range("a1").CurrentRegion.Resize(, 1)
    Set c = rnToSearch.Find(fileName)
    If c Is Nothing Then
        DoFileExist = 0
    Else
        DoFileExist = c.Row
    End If
End Function


Sub test()

    Debug.Print DoFileExist("helloworld")
    
End Sub

MEN denna söker på delsträng så kanske Find behöver ändras till

 

 Set c = rnToSearch.Find(fileName, lookat:=xlWhole)

så att enbart exakt match returneras.

 

Ta sedan detta radvärde för att styra om så att antingen filnamnet inte skrivs ut eller att den skrivs ut på nytt så att eventuell sökvägsändring slår igenom.

 

Sökmetoden ovan utgör från att varje filnamn är unikt.

 

Tack, en början att titta på.. Ska "bara" lära mig lite mer kod 

[Ove Söderlund]

Tror jag börjar få kläm på vad du försöker få till.

 

Följdfråga:

Kan det tillkomma nya dokument och kommer nya dokument att alltid återfinnas i första ursprungsmappen?

[Monshi]

Annars kan du nog använda dig av programmeringsverktyg som Git/SVN för versionshantering. För kod egentligen men går lika bra för anda filer. Så slipper du uppfinna hjulet igen.

 

Borde fungera men vissa granskningsfunktioner kommer givetvis inte att fungera då det inte är rena textfiler som sparas.

Men med det sagt,  kanske finns "renare" system för dokument? Jag vet inte.

[miclj]

Tror jag börjar få kläm på vad du försöker få till.

 

Följdfråga:

Kan det tillkomma nya dokument och kommer nya dokument att alltid återfinnas i första ursprungsmappen?

 

Det kan tillkomma dokument. Ska man strikt köra på versionshantering så borde de hamna i ursprungsmappen, dvs 1.0.

Men då jag ibland behöver leverera iväg dokument till andra personer så kan det bli att de tillkommna dokumenten kommer hamna i en annan mapp tex 2.0.

För jag behöver ha lite spårbarhet i vad jag levererat iväg, och vill inte blanda ihop dokumenten. Jag levererar oftast en 1.0 sen kan det bli ändringar/nya dokument då får de hoppa till 2.0 (man kan se det lite som att det speglar leverans1 och leverans2)..

 

Känner att det blir ganska rörigt det här.. Är enkelt att bygga mappstrukturer i windows så man sparar filer, med resultatet att det blir massa mappar att hålla ordning på.

[miclj]

Annars kan du nog använda dig av programmeringsverktyg som Git/SVN för versionshantering. För kod egentligen men går lika bra för anda filer. Så slipper du uppfinna hjulet igen.

 

Borde fungera men vissa granskningsfunktioner kommer givetvis inte att fungera då det inte är rena textfiler som sparas.

Men med det sagt,  kanske finns "renare" system för dokument? Jag vet inte.

 

Jag har använt mig av system förut som versionshanterar dokument, vilket är väldigt smidigt.

Men eftersom jag oftast behöver ha någon form av förteckning av dokument jag arbetar med (främst när jag ska skicka iväg dokument till någon annan) så blir det endel pill med det.

Förra systemet kunde autogenerera ett utdrag av filer som fanns där, men krävde endel handpåläggning för att få det snyggt och läsbart. Blir ganska tidskrävande om man har 500 filer..

 

Därför jag tänkte försöka använda mig av excel som skapade en förteckning utifrån filer som finns i en mapp. Låter ju enkelt när man inte har några direkta kunskaper om hur man uppnår det, hur svårt kan det vara liksom 

Tror jag får omvärdera det lite.. 

[Ove Söderlund]

Det är inte helt omöjligt att få till en bra fungerande kod för din önskade funktion. Vi behöver nog bara få till lite givna premisser först.

 

Kan du tänka dig att dina filer lagras på följande vis:

X:\MyPath\ - är grundmappen där dina dokument lagras.

X:\MyPath\1, X:\MyPath\2, X:\MyPath\3 ...osv.. för dina respektive versioner av dokument. Fördelen med en sådan lösning är att det är relativt enkelt att koda för genomsökning av alla mappar oavsett versionsnr (mappnamn).

I rotfoldern (X:\MyPath\) lagras då din sammanställningsfil och där även vba-koden finns.

 

De data du behöver ha är väl i stort sett Filnamn och DateLastModified?

 

I sammanställningslistan finns då 4 kolumner:

Filnamn

DateLastModified

Version (fås ifrån vilken mapp DateLastModified är nyast, felkälla här: om du råkar ändra och spara ett dokument i "tidigare" mapp" så får det dokumentet en nyare DateLastModifed!)

Länk till dokumentet (valbart, är det nödvändigt?)

 

Innan vi går vidare, så fundera mera på det jag skrivit här.

[miclj]

Det är inte helt omöjligt att få till en bra fungerande kod för din önskade funktion. Vi behöver nog bara få till lite givna premisser först.

 

Kan du tänka dig att dina filer lagras på följande vis:

X:\MyPath\ - är grundmappen där dina dokument lagras.

X:\MyPath\1, X:\MyPath\2, X:\MyPath\3 ...osv.. för dina respektive versioner av dokument. Fördelen med en sådan lösning är att det är relativt enkelt att koda för genomsökning av alla mappar oavsett versionsnr (mappnamn).

I rotfoldern (X:\MyPath\) lagras då din sammanställningsfil och där även vba-koden finns.

 

De data du behöver ha är väl i stort sett Filnamn och DateLastModified?

 

I sammanställningslistan finns då 4 kolumner:

Filnamn

DateLastModified

Version (fås ifrån vilken mapp DateLastModified är nyast, felkälla här: om du råkar ändra och spara ett dokument i "tidigare" mapp" så får det dokumentet en nyare DateLastModifed!)

Länk till dokumentet (valbart, är det nödvändigt?)

 

Innan vi går vidare, så fundera mera på det jag skrivit här.

 

Enligt mappstrukturen så stämmer det bra mot hur jag sparar mapparna. En huvudmapp med undermappar för varje version.

 

Angående kolumner så är det önskvärt med de 4 du listat..

Version, går det att få så den listar mappnamnet menar du.. Det vore fint om det gick

Länk till dokumentet är väl egentligen ingen jätte nödvändighet, bara att det är smidigt att kunna utgå från excel-filen om man vill öppna filer.. Man sparar alltid lite tid att ha snabb tillgång för att kunna öppna den senaste filen.. Slippa klicka runt bland mapparna..

[Ove Söderlund]

Förutsättningar:

 

Versionsmapparna ska vara numeriska heltalsvärden. Är mapparna angivna på annat vis behöver du förklara hur mapp-namnen är uppbyggda och hur dessa ska kunna sorteras i önskad ordning. Om numeriska heltalsnamn gäller så är det enkelt att skapa en for/next-loop för varje mapp.

 

Arbetsgång kodbyggande:

 

1. Ta reda på hur många versionsmappar -> bestämmer for/next-loopens gräns

 

2.  Subrutin som hämtar alla filnamnen med tillhörande LastModifiedDate och länk. Hämtade data skrivs till sammanställningsbladet. Versionen blir ju helt enkelt 1 i detta fall.

 

3. For/Next-loop för mapparna 2 - sista versionsmappen

   -  För varje filnamn som hittas i respektive mapp (här hämtas också LastModifiedDate + Länk) körs en jämförelse mot sammanställningsblad med hjälp av If-sats. Jämför först Filnamn -> om hittad -> jämför LastModifiedDate - > Uppdatera LastModifiedDate, Version och Länk i sammanställningsbladet.

 

Fundera lite på det här jag skrivit. Du kommer t.ex. att behöva lära dig konvertering mellan tal och sträng-tal, t.ex. mapp-namn=strängar men For/Next-loopen vill ha heltal. Variabelhantering blir nyckeln till lösningen här, Option Explicit rekommenderas varmt!

 

Går off-duty från kontoret här så tid har du nu att tänka på det här  till nästa vecka :-)