Herbers Excel-Forum - das Archiv

Geschrieben am: 13.09.2019 15:59:16
Hallo :)
Ich habe folgendes Problem und zwar habe ich in einer Access Datenbank mir eine Abfrage erstellt, wo in der ersten Spalte sogenannte ID's drinstehen. Diese ID's möchte ich nun in einem Exceldokument suchen und wenn diese vorhanden sind grün markieren..
Das Modul läuft in Access ;)
Bisher war mein Lösungsweg folgender


Sub ScannenID()
Dim Dokumentenpfad_Visual As String
Dim Sheet As Worksheet
Dim ValidMeldeposition As Object
Dim wb As Excel.Workbook
Set objExcel = New Excel.Application
Set wb = objExcel.Workbooks.Open("C:\Users\fsommer\Desktop\FINREP Navigator DPM 2.8\Annotated  _
Table Layout 280-FINREP 2.8.xlsx", True)
objExcel.Visible = False
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DataPointVID from My_ValidReport", dbOpenSnapshot)
Do Until rs.EOF = True
For Each Sheet In ActiveWorkbook.Worksheets
With Sheet.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
ValidMeldeposition.Interior.ColorIndex = 43
End With
Set ValidMeldeposition = Nothing
Next
rs.MoveNext
Loop
End Sub

dies hat nicht funktioniert.. kommt immer der Fehler: Laufzeitfehler 91: Objektvariable oder With-Blockvariable nicht festgelegt
hab es dann mit einer If schleife versucht


Do Until rs.EOF = True
For Each Sheet In ActiveWorkbook.Worksheets
With Sheet.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
If ValidMeldeposition = True Then
Cells.Interior.ColorIndex = 43
End If
End With
Set ValidMeldeposition = Nothing
Next
rs.MoveNext
Loop

hat jemand von euch eine Idee? Kann ja eeeeig nicht so schwer sein oder :D?
Beste Grüße
Frederic

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1712983.html
Geschrieben am: 13.09.2019 17:45:00
Hi Frederic
Du verwendest den Begriff Sheet als Variable. Sheet ist aber auch ein Schlüsselwort in VBA.
Womöglich reicht es den Namen der Variable zu ändern. Ansonsten Frage bitte wieder als offen markieren.
cu
Chris

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1712997.html

Geschrieben am: 13.09.2019 19:42:43
Hallo Frederic,


For Each Sheet In ActiveWorkbook.Worksheets
With Sheet.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
If Not (ValidMeldeposition Is Nothing)  Then
ValidMeldeposition.Interior.ColorIndex = 43
End If
End With
Next Sheet

Außerdem solltest Du unbedingt in der Vba-Hilfe zu 'Find' nachschauen - da gibt es unter Bemerkungen Hinweise, welche Parameter bei der Find-Methode unbedingt gesetzt werden sollten und vor allem warum.
Gruß von Luschi
aus klein-Paris

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713241.html

Geschrieben am: 16.09.2019 08:39:02
habe folgenden Code:


Sub ScannenID()
Dim Dokumentenpfad_Visual As String
Dim ws As Worksheet
Dim ValidMeldeposition As Object
Dim wb As Excel.Workbook
Set objExcel = New Excel.Application
Set wb = objExcel.Workbooks.Open("C:\Users\fsommer\Desktop\FINREP Navigator DPM 2.8\Annotated  _
Table Layout 280-FINREP 2.8.xlsx", True)
objExcel.Visible = False
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DataPointVID from My_ValidReport", dbOpenSnapshot)
Do Until rs.EOF = True
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
If Not (ValidMeldeposition Is Nothing) Then
ValidMeldeposition.Interior.ColorIndex = 43
End If
End With
Next ws
Loop

doch wenn ich den ausführe kommt das runde Ladezeichen und ich kann nichts mehr anklicken und muss die anwendung schließen.. öffne ich dann wieder Access ist der Code auch nicht gespeichert..
P.S. bzgl 'Find' weiß ich nicht wo ich das finden soll.. war in der VBA Referenz..

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713244.html

Geschrieben am: 16.09.2019 08:41:46
habe folgenden Code:


Sub ScannenID()
Dim Dokumentenpfad_Visual As String
Dim ws As Worksheet
Dim ValidMeldeposition As Object
Dim wb As Excel.Workbook
Set objExcel = New Excel.Application
Set wb = objExcel.Workbooks.Open("C:\Users\fsommer\Desktop\FINREP Navigator DPM 2.8\Annotated  _
_
Table Layout 280-FINREP 2.8.xlsx", True)
objExcel.Visible = False
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DataPointVID from My_ValidReport", dbOpenSnapshot)
Do Until rs.EOF = True
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
If Not (ValidMeldeposition Is Nothing) Then
ValidMeldeposition.Interior.ColorIndex = 43
End If
End With
Next ws
Loop

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713256.html

Geschrieben am: 16.09.2019 09:54:13
Hi
Je nach Anzahl Datensätze und Tabellenblätter, kann die Suche lange dauern, da sich die beiden Faktoren multiplizieren. Evtl. also einfach eine Frage der Geduld (nicht rum klicken, sondern brav warten).
Teste den Prozess mal mit wenig Daten.
Zu Find konnte ich den betreffenden Link gerade auch nicht mehr finden. Aber es geht darum, dass du unbedingt die optionalen Parameter mit angeben solltest.
Die Einstellungen für LookIn, LookAt, SearchOrder und MatchByte werden gespeichert, sobald Sie diese Methode verwenden.
Wenn Sie beim nächsten Aufruf der Methode keine Werte für diese Argumente angeben, werden die gespeicherten Werte verwendet. Das Festlegen dieser Argumente ändert die Einstellungen im Dialogfeld Suchen, und das Ändern der Einstellungen im Dialogfeld Suchen ändert die gespeicherten Werte, die verwendet werden, wenn Sie die Argumente auslassen.
Legen Sie diese Argumente zum Vermeiden von Problemen explizit fest, sobald Sie diese Methode verwenden.
cu
chris

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713267.html

Geschrieben am: 16.09.2019 10:41:31
Hey ;)
also hab es nur mit zwei Worksheets versucht (...2.9.xlsx), aber ich glaube der hängt sich auf..


Sub ScannenID()
Dim Dokumentenpfad_Visual As String
Dim ws As Worksheet
Dim ValidMeldeposition As Object
Dim wb As Excel.Workbook
Set objExcel = New Excel.Application
Set wb = objExcel.Workbooks.Open("C:\Users\fsommer\Desktop\FINREP Navigator DPM 2.8\Annotated  _
Table Layout 280-FINREP 2.9.xlsx", True)
objExcel.Visible = False
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DataPointVID from My_ValidReport", dbOpenSnapshot)
Do Until rs.EOF = True
For Each ws In wb.Worksheets
With ws.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
If Not (ValidMeldeposition Is Nothing) Then
ValidMeldeposition.Interior.ColorIndex = 43
End If
End With
Next ws
Loop

hab bei Aktive.Workbook jetzt durch wb ersetzt, da ich es ja vorher schon geöffnet habe
ist der Code so in Ordnung? finde den nicht lang aber vllt gibt es ja immer bessere Lösungen ;)
Beste Grüße
Frederic

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713268.html
Geschrieben am: 16.09.2019 10:51:12
Hallo
Dir fehlt ein "rs.MoveNext"! Du hast einen Loop um dein Recordset abzuarbeiten, liest aber nicht das nächste Record ein, somit arbeitest Du das erste Record in einer Endlosschleife ab.

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713272.html

Geschrieben am: 16.09.2019 11:13:19
hatte es drin damals
aktueller Code:

 Sub ScannenID()
Dim Dokumentenpfad_Visual As String
Dim ws As Worksheet
Dim ValidMeldeposition As Object
Dim wb As Excel.Workbook
Set objExcel = New Excel.Application
Set wb = objExcel.Workbooks.Open("C:\Users\fsommer\Desktop\FINREP Navigator DPM 2.8\ _
Annotated Table Layout 280-FINREP 2.9.xlsx", True)
objExcel.Visible = False
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DataPointVID from My_ValidReport", dbOpenSnapshot)
Do Until rs.EOF = True
For Each ws In wb.Worksheets
With ws.UsedRange
Set ValidMeldeposition = .Cells.Find(What:=rs!DataPointVID)
If Not (ValidMeldeposition Is Nothing) Then
ValidMeldeposition.Interior.ColorIndex = 43
End If
End With
Next
rs.MoveNext
Loop
End Sub

funzt nicht.. bin echt am verzeifeln.. muss doch gehen.. ist doch nur nach Nummern aus Accessabfrage in Excelsheets immer suchen.. oder man packt die nummern in ein Array aber selbst das hat nicht geklappt.. der Array war irgendwie leer..

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713308.html

Geschrieben am: 16.09.2019 14:17:24
Hi
MoveNext war ein guter Tipp, aber ansonsten läufts. Ich habe mir die Situation nun mit wenigen Dummy-Daten nachgestellt und der Code flutscht in einer Millisekunde ohne Fehler durch.
Achte darauf, dass du die Excel-Datei richtig schliesst. Der Code öffnet die Datei und blendet aus, geschlossen wird sie aber nicht.
Nachfolgend der gleiche Code, aber mit der empfohlenen Late-Binding Variante (Extra Verweis wird dadurch überflüssig). Inhaltlich unverändert (auch Find unkorrigiert!).

Sub ScannenID()
Dim db As Database, rs As Recordset
Dim objExcel As Object, wb As Object, ws As Object, ValidMeldeposition As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set wb = objExcel.Workbooks.Open("C:\Pfad\Mappe.xlsx", True)
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DataPointVID from My_ValidReport", dbOpenSnapshot)
Do Until rs.EOF = True
For Each ws In wb.Worksheets
Set ValidMeldeposition = ws.UsedRange.Cells.Find(What:=rs!DataPointVID)
If Not (ValidMeldeposition Is Nothing) Then ValidMeldeposition.Interior.ColorIndex = 43
Next ws
rs.MoveNext
Loop
End Sub

cu
Chris

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713328.html
Geschrieben am: 16.09.2019 15:18:53
also hab es jetzt auch mal mit einer Mappe getestet, in der ein paar ID's untereinander stehen, die eig alle grün sein müssten.. aber auch bleibt alles unverändert...
ich starte das Makro ja aus Access ;)
bzgl. Find weiß ich wirklich 0 was du damit meinst? ist das noch wichtig?
Beste Grüße
Frederic

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713334.html
Geschrieben am: 16.09.2019 15:33:46
ah sry stimmt.. der öffnet die ... gar nicht gesehen.. danke :) weißt du grade wie man schnell schreiben kann, dass der beim duplikaten den zweiten dritten usw auch markiert? auf dem zweiten Sheet ist kein problem aber kann sein das zwei auf einem vorkommen

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713339.html

Geschrieben am: 16.09.2019 16:01:23
komisch.. bei der Mappe ging der Code aber jetzt kommt die Fehlermeldung bei
Set ValidMeldeposition = ws.UsedRange.Cells.Find(What:=rs!DataPointVID)
dass ein Objekt erforderlich ist..
und wie kann man dass eig performance mäßig verschnellern? glaube wenn es bald vllt funktioniert.. braucht der eine zeit.. :D
Beste Grüße
Frederic

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713340.html

Geschrieben am: 16.09.2019 16:01:24
komisch.. bei der Mappe ging der Code aber jetzt kommt die Fehlermeldung bei
Set ValidMeldeposition = ws.UsedRange.Cells.Find(What:=rs!DataPointVID)
dass ein Objekt erforderlich ist..
und wie kann man dass eig performance mäßig verschnellern? glaube wenn es bald vllt funktioniert.. braucht der eine zeit.. :D
Beste Grüße
Frederic

Betrifft: AW: ID's aus Accessabfrage prüfen
von: 1713343.html
Geschrieben am: 16.09.2019 16:26:00
Hi Frederic
Ja, der Hinweis zu Find ist wichtig. Nochmal (aller guter Dinge sind 3):
https://www.herber.de/mailing/vb/html/xlmthfind.htm
da steht dann auch wie es mit FindNext weiter geht.
Die Performance kannst du vermutlich nur verbessern, wenn du die Datenmenge resp. den zu durchsuchenden Bereich reduzierst.
cu
Chris

Excel-Beispiele zum Thema "ID's aus Accessabfrage prüfen"
Zellen auf Kommentar überprüfen	Bereiche bei Mehrfachauswahl prüfen
Prüfen, ob ein Menü vorhanden ist	Eingabe auf bereits vorhanden prüfen und, wenn nein, einfügen
Blatttyp und Arbeitsmappennamen prüfen	Bei Öffnen Standardschriftgröße prüfen und Zoom festlegen
UserForm-TextBox-Werte auf Numeric prüfen und in Tabelle eintragen	Werte prüfen und kopieren
Werte nach Kriterien prüfen und auf Blätter verteilen	Nummerngruppe prüfen und melden