Uitzoeken of excel 2003-bestanden vba bevatten

[richard1970]

Beste VBA-deskundigen,

Op mijn werk zijn we bezig met het voorbereiden om van Office 2003 over te stappen naar Office 2010. Nu ligt bij mij de vraag om te inventariseren welke bestanden VBA-code bevatten.
Ik heb code gevonden die dit doet voor de actieve map, maar de code scant niet voor een complete schijf.
Ik zie dat sFolder verwijst naar de defaultfolder, en als ik die folder aanpas kan ik telkens een andere map scannen. Maar we hebben honderden mappen! Het is dus geen doen dit telkens aan te passen.

Nu heb ik twee vragen:
1) Is de code aan te passen zodat iedere map op het systeem gescand wordt?
2) Is het mogelijk de code aan te passen zodat niet alleen de bestandsnaam in kolom A wordt gezet, maar het hele pad?
Bekijk bijlage aantal_met_macro.xls
Groeten,

Richard

 

[Warme bakkertje]

Richard,

Als ge nu de code er nog bij kunt doen dan kunnen we iets meer zeggen :d

 

[richard1970]

Bekijk bijlage aantal_met_macro.xlsHoi Rudi,

Vreemd dat de code foetsie is.
Hierbij nogmaals het bestand.
Ik zal na uploaden gelijk even controleren of het er nu wel in staat
Na check: gelukt

Overigens test ik vanuit Excel 2003 omdat we nog niet over zijn op Office 2010.

Richard

 

[snb]

Ik zou maar vast koffie gaan zetten:

Sub M_snb()
    For Each dr In CreateObject("scripting.filesystemobject").drives
 
        sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & dr.RootFolder & "*.xls /b /s").stdout.readall, vbCrLf)
 
       For Each it In sn
           x = 0
           With GetObject(it)
             For Each vb In .VBProject.vbcomponents
               x = x + vb.codemodule.countoflines
             Next
             .Close False
           End With
           If x > 0 Then c01 = c01 & "_" & it
        Next
    Next

    sn=split(c01,"_")
    for j=0 to ubound(sn)
       sheets(1).cells(j+1,1)=sn(j)
    next
End Sub

Maar wat wil je vervolgens met deze informatie ?

 

[richard1970]

Hoi SNB,

Bedankt voor de code. Ik krijg eerst een compileerfout: "Een variabele is niet gedefinieerd", en dan wordt "dr" gearcheerd.
Mmmm, als ik Option explicit uitzet, draait de code wel.
Nu krijg ik de melding
Fout 76 tijdens uitvoering: "Kan het pad niet vinden". Als ik met F8 stap voor stap door de code ga, dan zie ik dat de melding komt na de regel:
sn = Split(CreateOhbject....................
Als ik het stukje code "dr.rootfolder" aanpas naar "mijn documenten", dan loopt de code wel en verschijnen er een paar keer dos-boxen. Als die verdwijnen, dan is er -voor zover ik kan zien- niets gebeurd?? Hoe nu verder?

Wat ik uiteindelijk wil is een lijst met bestanden die macro's bevatten en hun locatie. Bij de overgang van Office 2003 naar Office 2010 zullen we moeten testen of alle zelfgemaakte VBA-code het nog doet.

Richard

 

[snb]

Je hebt blijkbaar systeemmappen en verborgen mappen waartoe je geen toegang hebt.
Je hebt blijkbaar niet uitgezocht wat 'Drives' betekent.
Wijzig svp code pas, nadat je hem snapt.
In kolom A krijg je een overzicht van alle bestanden met VBA-code.
gebruik:

Sub M_snb()
  on error resume next
  For Each dr In CreateObject("scripting.filesystemobject").drives
 
    sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & dr.RootFolder & "*.xls /b /s").stdout.readall, vbCrLf)
 
    For Each it In sn
      x = 0
      With GetObject(it)
        For Each vb In .VBProject.vbcomponents
          x = x + vb.codemodule.countoflines
        Next
        .Close False
      End With
      If x > 0 Then c01 = c01 & "_" & it
    Next
  Next

  sn=split(c01,"_")
  for j=0 to ubound(sn)
    thisworkbook.sheets(1).cells(j+1,1)=sn(j)
  next
End Sub

 

[richard1970]

Hoi snb,

Ik ben niet iemand die bij het minste of geringste zegt "Hij doet het niet". Ik krijg zelf wel eens zulke calls binnen en de houding dat iemand zich volledig afhankelijk opstelt zonder eigen initiatief vind ik maar niets. Vandaar dat ik even kijk of ik door iets aan te passen de boel draaiende krijg.
Jouw code opent de excelbestanden bestanden (vandaar dat ik koffie mag gaan zetten ), maar ik heb op mijn systeem bestanden staan met events die geactiveerd worden zodra het bestand geopend wordt. Tja, dan blijft 'ie steken in bijv. een invoerscherm. Ook zijn er bestanden met fouten in de vba-code en daar blijft de procedure dan ook op hangen. Is daar wat aan te doen?
Ik vind je code elegant, maar het verschilt wel veel met hetgeen ik in post #3 plaatste waarin -volgens mij- de bestanden niet geopend werden, maar alleen gecontroleerd op de aanwezigheid van VBA-code. De code zelf hoeft niet gecontroleerd te worden, het gaat om een inventarisatie van bestande die code bevatten. Het testen komt pas als we een testomgeving ingericht hebben met Office 2010.

Richard

 

[snb]

Die 'andere' methode opent de bestanden ook, maar op een andere manier: niet als Excel bestand maar als een digitaal bestand.

 

[richard1970]

Ah, en dan worden de events niet uitgevoerd.
Is dat in te bouwen in jouw code? Ik kan niet inschatten of er veel bestanden zijn met events, maar als het bij mij en showstopper is, dan op het netwerk ook vast.

 

[richard1970]

Hoi snb,

Ik heb de Excelbestanden die op mijn systeem problemen opleverden een andere extensie meegegeven zodat ze overgeslagen worden.
Nu doorloopt jouw code alle mappen, opent de bestanden en sluit ze weer en tenslotte wordt ook het bestand zelf gesloten, maar zonder dat er een lijst verschijnt met de namen van de bestanden die VBA bevatten. Dat is wel mijn uiteindelijke doel. Gaat er iets niet goed, of zit deze stap niet in de code?

Groeten,

Richard

 

[snb]

Denkvoudje() vermoed ik: het bestand met de code is natuurlijk ook onderdeel van de macro.

Maar deze code lijkt me nog eenvoudiger:
Een combinatie van wat jij als methode aanleverde en mijn suggestie:

Sub M_snb()
  c00 = Replace("V.B.A._.P.R.O.J.E.C.T._.C.U.R", ".", Chr(0))
    
  For Each dr In CreateObject("scripting.filesystemobject").drives
    For Each it In Filter(Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & dr.RootFolder & "*.xls /b /s").StdOut.readall, vbCrLf), ":"), ThisWorkbook.FullName, False)
      Open it For Binary As #1
        If InStr(Input(LOF(1), 1), c00) Then c01 = c01 & "_" & it
      Close
    Next
  Next
    
  sn = Split(c01, "_")
  Sheets(1).Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

 

[richard1970]

Beste snb,

Mooie code en het draait een heel tijdje, maar uiteindelijk zie ik nergens output?
De bestanden worden nu inderdaad digitaal geopend, dus sneller, maar wat er uiteindelijk gegenereerd wordt weet ik niet.

Levert deze code bij jou wel een lijst met bestandsnamen op in excel?

Richard

 

[snb]

Je kunt een onderbrekingspunt gebruiken of het hulpscherm locals om te zien wat er in de variabele c01 zit.

Of gebruik het venster direkt met in de code een regel 'debug.print c01'

 

[richard1970]

Hoi snb,

Ik ben de code aan het draaien om in het venster lokale variabelen te zien wat de waarde is van de variabele C01. Maar daar komt als eerste iets te staan wat onleesbaar is.
Soms verandert deze waarde, maar altijd in iets onleesbaars. Zie bijlage voor de eerste waarde.
Zegt dit jou iets? Is hier iets aan te doen?

Oh ja, ik moet er bij zeggen dat ik als eerste regel uit jouw vorige code heb geplaatst: On Error resume Nest.
Als ik dat weghaal krijg ik gelijk te zien "Fout 76 tijdens uitvoering: Kan het pad niet vinden."

 

[snb]

Gebruik svp mijn laatste suggestie in post #11

Dan zie je begrijpelijke resultaten in variable c01.

 

[richard1970]

Hoi snb,

Ik had je tip gezien, maar niet goed kunnen toepassen woensdag. Vandaag een te hectische dag gehad op het werk. Hopelijk morgen meer tijd om te testen.

Richard

 

[richard1970]

Beste snb,

Met de code debug.print c01 kan ik inderdaad leesbaar zien wat de waarde is van de variabele c01.
Ik ben het nu aan het testen op mijn werkplek en niet zoals afgelopen woensdag thuis op mijn XP-systeem toen ik sneller reageerde op je posts.
Ik krijg na een aantal mappen een foutmelding. Dit gebeurt pas nadat de mappen op C gescand zijn (en ik kan door telkens op F8 te klikken in het venster 'Lokale variabelen' zien dat alle mappen met xls-bestanden gescand worden inclusief systeemmappen, dus dat gaat goed).
Eerst toont de variabele it geen waarde meer <leeg>, en als ik vervolgens weer op de regel kom "For Each it In Filter......." dan komt de foutmelding: "Fout 76 tijdens uitvoering: Kan het pad niet vinden."

Ik heb toen de code "On error resume next" vooraan gezet en nu krijg ik een lijst te zien met excelbestanden waarin inderdaad vba gebruikt wordt!
Het grapppige is dat een bestand met een underscore in de naam wordt nu op twee regels geschreven.
Op mijn systeem wordt nu wel C gescand, maar niet D. Dat geeft niet, want het gaat straks om de S:\-schijf. Maar ik weet niet of jouw code het goed doet wanneer het specifiek om deze drive gaat.
Is de regel:
For Each it In Filter(Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & dr.RootFolder & "*.xls /b /s").StdOut.readall, vbCrLf), ":"), ThisWorkbook.FullName, False)

zo aan te passen zodat ik de schijf kan intikken?
Als ik er namelijk "D:" van maak, krijg ik de excelbestanden met macro's vier keer te zien

Richard

 

[snb]

Zoek svp eerst eens op in VBA documentatie wat

For Each dr In CreateObject("scripting.filesystemobject").drives​

betekent

Van een beveiligd VBAprojekt kan natuurlijk niet het aantal regels geteld worden.

De 'underscore' kwestie had je natuurlijk zelf eenvoudig kunnen oplossen.

Sub M_snb()
  on error resume next
  For Each dr In CreateObject("scripting.filesystemobject").drives
 
    sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & dr.RootFolder & "*.xls /b /s").stdout.readall, vbCrLf)
 
    For Each it In sn
      x = 0
      With GetObject(it)
        For Each vb In .VBProject.vbcomponents
          x = x + vb.codemodule.countoflines
        Next
        .Close False
      End With
      If x > 0 Then c01 = c01 & vblf & it
    Next
  Next

  sn=split(c01,vblf)
  for j=0 to ubound(sn)
    thisworkbook.sheets(1).cells(j+1,1)=sn(j)
  next
End Sub