Fout in Excel VBA

[Niels2VBA]

Schets van de situatie:

- Er is een excel document (inventaris dossiers)
- Er is een word document waar volgende VBA programma in zit dat in de Excel-lijst in de kolom "dossiernummers" alle gegevens gaat ophalen en deze dan oplijst in een dropdown-box dat wordt aangeboden in het wordt document.

Voor het ophalen van de gegevens uit de excel lijst (automatisch bij het openen van het word-document) heb ik volgende code:

Private Sub UserForm_Initialize()

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & "Z:\Inventaris dossiers.xls;"
rs.Open "SELECT * FROM [Blad1$]", conn

Do While (Not rs.EOF And Not IsEmpty(rs("Dossiernummer")))
    ComboBox1.AddItem rs("Dossiernummer")
    rs.MoveNext
Loop

rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
End Sub

Probleem:
Bij het openen van het word-document krijg ik een foutmelding:

Run-time error '-2147352571 (80020005)':
Type mismatch


Beschrijving:
Het rare is dat deze code al heel lang werkt.
Wanneer ik een oude versie van het excel document gebruik dan werkt dit ook opnieuw (met dus dezelfde code in het word document)
Soms gebeurd het dus dat ineens het document niet meer werkt.
Wat ik op dat ogenblik moet doen is, het oude bestand nemen dat nog werkt en de nieuwe inhoud die erbij gekomen is in dit nieuwe document plakken (= veel werk).
Iemand een idee hoe dit zou kunnen komen?

Wanneer ik in debug mode kijk zie ik dat alle cellen effectief doorlopen worden tot op het einde van het document en wanneer de "while" de laatste keer doorlopen wordt is de waarde "null". Het is hierna dat de fout te voorschijn komt.

Heeft iemand dit probleem al gehad?
Kan iemand helpen?

 

[wampier]

Het verschil is of er een lege regel op het einde staat of niet. NULL triggert geen "isempty" want volgens database code is NULL een geldige waarde. Echter zal NULL waarschijnlijk bij "AddItem" de mismatch veroorzaken.

 

[snb]

Als kolom E de dossiernummers bevat, zou ik dit gebruiken:

Private Sub UserForm_Initialize()
  with getobject("Z:\Inventaris dossiers.xls")
     combobox1.list=.sheets(1).columns(5).specialcells(2).value
    .close false
  end with
End Sub

 

[Niels2VBA]

Het verschil is of er een lege regel op het einde staat of niet. NULL triggert geen "isempty" want volgens database code is NULL een geldige waarde. Echter zal NULL waarschijnlijk bij "AddItem" de mismatch veroorzaken.

Ik snap je uitleg (deels )...
De volgende lijn is inderdaad een lege lijn.
Dat was net de bedoeling, de lijst vullen tot de eerste lege lijn die we tegen komen.

Ken je een mogelijk oplossing?

 

[snb]

Die heb ik je zojuist gegeven.

 

[Niels2VBA]

Inderdaad!

Werkt!
En zo simpel...

Heel erg bedankt!

 

[Niels2VBA]

Heb een beetje heviger getest en ben op het volgende gestoten:

Wanneer het Excel document open staat en een word document dat de gegevens van dit excel document via de macro oproept wordt geopend, dan wordt het excel-document automatisch afgesloten.

Probleem is dat dit ook nog zonder afsluiten gebeurd.

Is er een mogelijkheid om ervoor te zorgen dat het excel document niet sluit?
En indien niet mogelijk, dat het document ten minste eerst opslaat?

Erna kan ik het optioneel misschien opnieuw laten openen op het einde van de routine...

 

[OctaFish]

Als je .Close False vervangt door .Close True wordt het document bewaard. Op deze site vind je een voorbeeld van een functie die checkt of je bestand open is of niet. Door er een boolean variabele aan toe te voegen waarin je het resultaat True of False opslaat, kun je verder in het proces een processplitsing maken die ofwel het bestand sluit als het niet geopend was, of hem open laat.

 

[snb]

Met deze aanvullende eis is deze code voldoende:

Private Sub UserForm_Initialize()
      combobox1.list=getobject("Z:\Inventaris dossiers.xls").sheets(1).columns(5).specialcells(2).value
End Sub