Herbers Excel/VBA-Archiv

ID's aus Accessabfrage prüfen

• ID's aus Accessabfrage prüfen von Frederic vom 13.09.2019 15:59:16
• AW: ID's aus Accessabfrage prüfen - von ChrisL am 13.09.2019 17:45:00
• AW: ID's aus Accessabfrage prüfen - von Luschi am 13.09.2019 19:42:43
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 08:39:02
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 08:41:46
• AW: ID's aus Accessabfrage prüfen - von ChrisL am 16.09.2019 09:54:13
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 10:41:31
• AW: ID's aus Accessabfrage prüfen - von peterk am 16.09.2019 10:51:12
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 11:13:19
• AW: ID's aus Accessabfrage prüfen - von ChrisL am 16.09.2019 14:17:24
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 15:18:53
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 15:33:46
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 16:01:23
• AW: ID's aus Accessabfrage prüfen - von Frederic am 16.09.2019 16:01:24
• AW: ID's aus Accessabfrage prüfen - von ChrisL am 16.09.2019 16:26:00

Betrifft: ID's aus Accessabfrage prüfen von: Frederic
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
          

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: 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

Beiträge aus dem Excel-Forum zum Thema "ID's aus Accessabfrage prüfen"