Office: (Office 365) Status ändern nach Bearbeitung

Hallo Accessprofis

Ich als völlig unwissender was Access betrifft habe da mal eine Frage zu einer Accessdatenbank.



Es geht um die Spalte Status in der Datenbank. 2 sachen sind mir klar Status = 0 kein Eintrag und Status = 1 Eintrag vorhanden.
Die Datenbank übergibt die Werte an einen Tempsheet in einer Exceltabelle, von wo diese dann in Textboxen einer Userform per VBA eingelesen werden. Ist es möglich, noch einen Status zu generien z.B. als Kennzeichnung dafür das die Bearbeitung einer Bestellung (betrifft dann ganze Zeile) mit 2 als erledigt markiert werden kann und wenn ja wie.
Hintergrund ist dass erledigte Bestellungen nicht mehr im Tempsheet in der Tabelle beim nächsten laden der Datenbank erscheinen sollen.

Vielen Dank im Voraus
Gruß
Detlef

 

Hallo Detlef,

was verstehst du unter "Status generieren"? Du musst nur im Feld (=Spalte) "Status" im betroffenen Satz (=Zeile) die Zahl 2 eingeben und die im vorigen Posting gezeigte Tabelle "Bestellungen" wieder schließen. Dann ist der Status gespeichert und die Tabelle "Bestellung" harrt des Importes ins Excel-TempSheet.

 

Hallo Anton Exl,

vielen Dank für Deine Antwort. Genau was Du geschrieben hast " eine 2 eingeben von Hand " soll nicht sein. Was ich meinte ist, dass eine weiter Zahl, die 2 neben der Standard 0 und der 1 vordefiniert ist.

Gruß
Detlef

 

Hallo Accessprofis
Noch einmal zum obigen Thema eine Frage. Mit folgendem Code wird in der Datenbank der Status von 1 nach 2 geändert,
soweit bin ich jetzt gekommen.

Code:

Sub UpdateValue()

    FilePath = "C:\Users\Bärchen\Desktop\Database - Kopie.accdb"
           'Verbindung öffnen
            Conn.Open ConnectionString
    
    '###  Querry
    
        Dim QueryString As String
    
        '# Werte Lesen
            'Alle Spalten von der Tabelle "Bestellungen" laden
                QueryString = "SELECT * FROM Bestellungen" '* steht für alle Spalte
            'Spalten "ID_Abteilung" und "Status" von der Tabelle  "Bestellungen" laden
                QueryString = "SELECT ID_Abteilung,Status FROM Bestellungen"
            
      '# Zeilen aktualisieren
            'Hier die Spalte "Status" aller Zeile der Tabelle "Bestellungen", die den Wert 1 in der Spalte "Status" haben, nach 2 ändern
             QueryString = "UPDATE Bestellungen SET Status = '2' WHERE ID_Abteilung = 1"
            
            
        'Hinzufügen oder Bearbeitung der Daten
            Conn.Execute QueryString
    
    '### Datanbankverbindung schließen
       Conn.Close

End Sub

Allerdings werden hier die Werte der gleichen ID_Abteilung alle von 1 nach 2 gesetzt. Wie im screenshot markiert.

Der Code wird bei Betätigung eines Optionsbuttons ausgeführt. Wie kann der Code nun so angepasst werden, dass zuerst der obere Wert nach von 1 nach 2 geändert wird und nach Drücken eines anderen Optionbuttons dann der untere Wert von 1 nach 2, in sofern dies so möglich ist. Hintergrund der Frage ist, dass Einträge mit Status 2 nicht mehr geladen werden, was so auch funktioniert, das aber für jeden Wert einzeln geschehen muss, da sonst alle Einträge mit Status 2 ja nicht geladen werden.

Vielen Dank schon mal für Eure Hilfe.
Gruß
Detlef

 

Hallo Detlef,

du hast eine bestehende Access-Tabelle namens "Bestellungen", die so aufgebaut ist, wie du sie im obigen Screenshot gepostet hast. Du möchtest, dass mit Hilfe eine SQL-Update-Abfrage der Inhalt des Feldes "Status" geändert wird: Falls ein Satz vorliegt mit dem Feld "ID_Abteilung", dessen Inhalt 1 ist, soll der Inhalt des Feldes "Status" auf 2 geändert werden.
Die benötigte SQL-Anweisung lautet:
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1;
Eines muss hier allerdings klar sein: Dieses Update wird auf alle Sätze (=Zeilen) in der Tabelle "Bestellung" angewendet, deren ID_Abteilung = 1 ist.

Ist dieses eine Kriterium zu allgemein, muss man die WHERE-Klausel detailierter gestalten, wobei du leider nicht mitgeteilt hast, worin diese Detailierung bestehen soll.
Falls das Feld "ID" das zusätzliche Kriterium liefern sollte, könnte die SQL-Anweisung lauten:
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID =1;
bzw.
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID =19;

oder aber auch:
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID < 5;
bzw.
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID >=15;

oder noch detailierter:
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID < 5 AND Status = 1;
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID >=15 AND Status = 1;

oder welche Kriterien du dir auch immer einfallen lässt. Die WHERE-Klausel muss so konstruiert sein, dass sie einen Wert vom BOOLschen Typ erzeugt (d.h. Werte mit Resultat wahr oder falsch).

 

Hallo Anton Exl,

Vielen Dank für die ausführlichen Beschreibungen.
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1;
Das hiermit alle Werte auf Status 2 aus ID_Abteilung 1 geändert werden, habe ich ja schon rausgefunden. Um das etwas genauer zu formulieren folgendes, Es gibt in ID_Abteilung, ca 24 verschiedene ID,s. welche sich immer wieder bei Bestelleingabe wiederholen, somit weiss ich auch nicht im vorraus, wer wann eine Eingabe macht und welche ID(Autowert)
in Spalte(ID) dann der ID_Anlage zugeordnet ist.

UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID < 5 AND Status = 1;
UPDATE Bestellungen SET Status = 2 WHERE ID_Abteilung = 1 AND ID >=15 AND Status = 1;

Wenn ich das richtig verstehe, für jemanden der mit Access noch nichts zu tun hatte, werden hier in den Beispielen der
Status auf 2 gesetzt, Wenn die ID_Abteilung kleiner 5 oder größer, gleich 15 ist. Dies würde mein Problem so wie ich das sehen nicht lösen, da die Spalte ID(Autowert) ja fortlaufend ist und zB ID_Abteilung = 1, der ID 25 und 30 bei zwei Bestellungen
hintereinander zugeordnet sein könnte. Wenn ich jetzt den Vergleich mit größer/gleich 15 mache wird dann nicht auch der Status bei beiden ID,s gleichzeitig auf 2 gesetzt oder habe ich hier einen Denkfehler??

Was ich brauche ist etwas, womit die Zeilen zb. mit ID_Abteilung = 1 einzeln auf Status 2 geändert werden können wobei ich von meiner Logik ausgehend, sicherlich einen Wert zur eindeutigen Identfizierung der Zeilen mit ID_Abteilung = 1 brauche um diese einzeln ändern zu können und hier weiß ich im Moment nicht, wie ich das Bewerkstelligen soll.

Gruß
Detlef

 

Hallo,

dann nehme ich an, benötigst du eine normale Tabellenabfrage wie die folgende:

SELECT * FROM Bestellungen WHERE ID_Abteilung = 1 AND Status = 1;

Diese Abfrage zeigt dir alle Sätze aus der Tabelle "Bestellungen" an, deren Feld ID_Abteilung den Wert 1 besitzt und ebenso das Feld Status. In dieser kannst du dann die gewünschten Sätze/Felder händisch updaten.

 

Hallo Anton Exl,

warscheinlich zu wenige Hintergrundinformationen für die Lösung des Problems. Also genau das Eintragen von Hand soll ja eben nicht sein.
Beim öffnen dieser UserForm in Excel, werden die Daten aus der Datenbank geladen und im Moment alle 20 sek automatisch aktualisiert.




Mit dieser UserForm wird der Status über die Optionbutton auf nicht da oder erledigt gesetzt.
Dabei werden die TextBoxen in der UserForm oben geleert, die DatenBank geöffnet und der Status in der DatenBank auf 2 gesetzt. Das Funktioniert alles Super und wie es soll. Der Status muss von 1 auf 2 gesetzt werden, da sonst ja Daten wieder geladen würden, welche schon bearbeitet sind. Auch wenn bei allen Einträgen bei ID_Abteilungen in der Datenbank gleichzeitig von Status 1 nach 2 gesetzt werden, wird bei Drücken des Optionsbutton (die beiden oberen linke TextBoxen und die beiden unteren rechte TextBoxen) je nach Betätigung die linke oder rechte Seite geleert und die andere Seite bleibt gefüllt, allerdings nur so lange bis jemand die UserFormen schließt und wieder öffnet. Weil in der DatenBank ja alle ID_Abteilungen = 1 im Status dann egal welcher Optionbutton betätigt wurde auf 2 gesetzt sind und somit dann nicht mehr geladen werden. Daher benötige ich eine Lösung das eben nur immer eine Zeile in der DatenBank geändert wird. Von Hand eintragen ist daher nicht möglich und auch nicht gewollt, da alles Andere ja schon automatisch läuft. Vieleicht hilft die Erklärung bei der Lösung meines Problems.
Gruß
Detlef

 

Hallo,

falls du damit gemeint hast, dass du pro Update jeweils nur den ersten Satz in der Tabelle "Bestellungen" im Feld "Status" aktualisieren (=auf 2 stellen) möchtest, falls das Feld "ID_Abteilungen" =1 und das Feld "Status" =1 ist, dann müsste die folgende VBA-Sub "StatusUpdate" in ACCESS funktionieren.

Code:

Public Sub StatusUpdate()
  Dim Dbs As DAO.Database
 
  Set Dbs = Application.CurrentDb
 
  With Dbs.OpenRecordset("SELECT TOP 1 * FROM Bestellungen WHERE ID_Abteilung = 1 AND Status = 1 ORDER BY ID ASC;")
    If .EOF Then
       MsgBox Prompt:="Kein Status=1 für ID_Abteilung=1 vorhanden", Title:="Suche erfolglos"
    Else
       .Edit
       !Status = 2
      .Update
    End If
  End With

End Sub

Sie gehört in ACCESS in ein allgemeines Codemodul, welches du übers ACCESS-Menüband > Tabulator "Erstellen" > Gruppe "Makros und Code" > Button "Modul" erreichen kannst.

 

Hallo Exl

Hab das Modul und das Makro in Access eingefügt und es funktioniert, dafür vielen Dank, wäre ich ohne Deine Hilfe niemals drauf gekommen. Soweit so gut, allerdings habe ich jetzt noch die Frage, wie ich das Makro über die Optionsbutton der kleinen UserForm oben, ansprechen bzw starten kann, da ja über diese alle Statusänderungen vorgenommen werden. Es muss also wenn ich zB auf nicht da oder erledigt klicke, ja eine Verbindung aus Excel zum Makro in der Datenbank hergestellt werden, oder es muss den Optionbutton zugewiesen werden. Wenn das geht wie mache ich das. Hab keinen Schimmer da ich mich mit der Thematik ACCESS und EXCEL VBA erst seit Februar 2022 beschäftige.
Gruß
Detlef

 

Hallo Detlef,

um das umzusetzen, dürfte es besser sein, in Access statt der SUB StatusUpdate (die ich dir gestern übermittelte) in Access die FUNCTION WurdeStatusUpgedatet

Code:

Public Function WurdeStatusUpgedatet() As Boolean
  Dim Dbs As DAO.Database
 
  Set Dbs = Application.CurrentDb
 
  With Dbs.OpenRecordset("SELECT TOP 1 * FROM Bestellungen WHERE ID_Abteilung = 1 AND Status = 1 ORDER BY ID ASC;")
    If .EOF Then
       WurdeStatusUpgedatet = False
    Else
       .Edit
       !Status = 2
      .Update
      WurdeStatusUpgedatet = True
    End If
  End With

End Function

zu verwenden. Die Function hat den großen Vorteil gegenüber der SUB, dass man mit ihr den Erfolg oder den Misserfolg des Updatens in Access an Excel zurückmelden kann und erst in Excel entsprechende Maßnahmen ergreifen kann/braucht/muss. Dagegen bleibt die SUB, so wie sie konstruiert ist, bei Misserfolg in einer MessageBox in Access hängen, statt sofort wieder nach Excel zurückzukehren.

In Excel kann die Access-Funktion zB. mit folgender SUB aufgerufen werden:

Code:

Public Sub Update_durch_AccessFUNCTION()
  Dim AccFile As String
  Dim AccFunc As String
  Dim WarUpdateErfolgreich As Boolean        '<== Variable für den Rückgabewert
 
  AccFile = "S:\Dokumente\Database3.accdb"   '<== anpassen !!
  AccFunc = "WurdeStatusUpgedatet"           '<== Eventuell anpassen!!
 
  With CreateObject("Access.Application")
     .OpenCurrentDatabase AccFile
     WarUpdateErfolgreich = .Run(AccFunc)  '<== Access-Funktion mit Rückgabewert
     .Quit
  End With
 
  If WarUpdateErfolgreich Then
    MsgBox "Erfolgreiches Update des Status=1 auf Status=2 für ID_Abteilung=1"
  Else
    MsgBox Prompt:="Kein Status=1 für ID_Abteilung=1 vorhanden", Title:="Update erfolglos"
  End If

End Sub

Hier sieht man sofort den Vorteil der Access-Function (gegenüber einer Access-SUB), dass man auf den Erfolg/Misserfolg der Function in Excel reagieren kann:
IF WarUpdateErfolgreich Then MsgBox .... else MsgBox .... End IF
Du wirst wahrscheinlich das 1. MsgBox als Kommentar deaktivieren (weil es nur nervig ist), während du das 2. MsgBox in irgendeiner Form beibehalten wirst.

Diese SUB Update_durch_AccessFUNCTION, die in Excel in ein allgemeines Codemodul gehört, kannst du in deiner Userform in der Click-Event-Prozedur des Option-Buttons aufrufen, zB:

Code:

Private Sub OptionButton1_Click()

  Call Update_durch_AccessFUNCTION
 
End Sub

Dabei musst du natürlich statt "OptionButton1" den Namen deines/r OptionButton(s) einsetzen.

 

Hallo Exl

Vielen Dank für die guten Erklärungen und Deine Hilfe, einfach Super. Kann mich aber erst heute Abend mi der Sache beschäftigen. Mal sehen ob ich das hinbekommen. Melde mich dann später wieder.

Gruß
Detlef

 

Hallo Exl

Sorry das ich mich erst jetzt melde, hatte viel um die Ohren.
So, die Statusaktualisierung funktioniert super, vielen Dank noch mal dafür. Es gibt allerdings noch ein Problem in Excel.

Der Code ist die Datenverbindung zwischen Access und Excel. Hier wird der Tempsheet erzeugt und die Daten übergeben.
Mein Problem ist, dass wenn bei geöffnettem Tool noch ein Exceldatei aufgemacht wird, der Fehler 9 Index aushalb des Bereiches erzeugt wird. Ursache ist das fehlende Arbeitsblatt Temp in der zweiten geöffnetten Datei, da Excel offensichtlich bei Datenaktualisierung danach auch dort sucht.

Meine Frage ist wie kann die Suche des Tempsheet auf nur eine Exceldatei beschränkt werden, wenn mehrere geöffnet
sind ? Hintergrund ist noch, dass bei der Datenaktualisierung im Packsatztool, der Befehl Cell.ClearContens ausgeführt wird
und somit in allen geöffnetten Exceldateien die Zelleninhalte geleert werden. Da darf so nicht sein.

Code:

Option Explicit

Public FilePath, Provider, ConnectionString, QueryString, DataDivision As String
Public Conn, DataSet As Object

Public TempSheet As Worksheet

Sub DeclareVariables()

    FilePath = "C:\Users\Bärchen\Desktop\Database - Kopie - Kopie.accdb"
   
    Provider = "Microsoft.ace.OLEDB.12.0;"
    ConnectionString = "Provider=" & Provider & "Data Source=" & FilePath
   
    DataDivision = "[<>]"
   
    Set TempSheet = Worksheets("Temp")
   
End Sub

Function ListAnlagen() As Variant
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT * FROM Anlagen ORDER BY Anlagen.Bezeichnung ASC"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           TempString = TempString & DataSet.Fields("ID") & DataDivision & DataSet.Fields("Bezeichnung") & vbNewLine
           DataSet.MoveNext
        Loop
    End If
   
    If Len(TempString) > Len(vbNewLine) Then
        TempString = Left$(TempString, Len(TempString) - Len(vbNewLine))
    End If
   
    ListAnlagen = Split(TempString, vbNewLine)
   
    DataSet.Close
    Conn.Close

End Function

Function ListOffeneBestellungen() As Variant
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT * FROM ((Bestellungen" & _
                  " LEFT JOIN Packsaetze ON Bestellungen.ID_Packsatz = Packsaetze.ID)" & _
                  " LEFT JOIN Anlagen ON Bestellungen.ID_Abteilung = Anlagen.ID)" & _
                  " WHERE Status = 1"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    Cells(1, 1).CopyFromRecordset DataSet
   
    DataSet.Close
    Conn.Close

End Function


Function ListPacksaetze() As Variant
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT * FROM Packsaetze ORDER BY Packsaetze.Bezeichnung ASC"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           TempString = TempString & DataSet.Fields("ID") & DataDivision & DataSet.Fields("Bezeichnung") & vbNewLine
           DataSet.MoveNext
        Loop
    End If
   
    If Len(TempString) > Len(vbNewLine) Then
        TempString = Left$(TempString, Len(TempString) - Len(vbNewLine))
    End If
   
    ListPacksaetze = Split(TempString, vbNewLine)
   
    DataSet.Close
    Conn.Close

End Function


Function ListZugeordnetePacksaetze(ByVal Anlage_ID As String) As Variant
   
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT ID_Packsatz,Packsaetze.Bezeichnung FROM Zuordnung_Packsatz_Anlage " & _
                    " LEFT JOIN Packsaetze ON Packsaetze.ID = Zuordnung_Packsatz_Anlage.ID_Packsatz " & _
                    " WHERE Zuordnung_Packsatz_Anlage.ID_Anlage = " & Anlage_ID

    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           TempString = TempString & DataSet.Fields("Bezeichnung") & vbNewLine
           DataSet.MoveNext
        Loop
    End If
   
    If Len(TempString) > Len(vbNewLine) Then
        TempString = Left$(TempString, Len(TempString) - Len(vbNewLine))
    End If
   
    ListZugeordnetePacksaetze = Split(TempString, vbNewLine)
   
    DataSet.Close
    Conn.Close

End Function

Function Abteilung_ID(ByVal AbteilungsBezeichnung As String) As String
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT TOP 1 ID FROM Anlagen WHERE Bezeichnung = '" & AbteilungsBezeichnung & "'"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           Abteilung_ID = DataSet.Fields("ID")
           DataSet.MoveNext
        Loop
    End If
       
    DataSet.Close
    Conn.Close

End Function

Function Packsatz_ID(ByVal PacksatzBezeichnung As String) As String
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT TOP 1 ID FROM Packsaetze WHERE Bezeichnung = '" & PacksatzBezeichnung & "'"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           Packsatz_ID = DataSet.Fields("ID")
           DataSet.MoveNext
        Loop
    End If
       
    DataSet.Close
    Conn.Close

End Function

Function ListMilkrunTimes(ByVal ID_Milkrun As Integer) As Variant
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT * FROM Milkrun_Times WHERE Milkrun_Times.ID_Milkrun = " & ID_Milkrun & " ORDER BY Milkrun_Times.Time ASC"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           TempString = TempString & DataSet.Fields("ID") & DataDivision & DataSet.Fields("Time") & vbNewLine
           DataSet.MoveNext
        Loop
    End If
   
    If Len(TempString) > Len(vbNewLine) Then
        TempString = Left$(TempString, Len(TempString) - Len(vbNewLine))
    End If
   
    ListMilkrunTimes = Split(TempString, vbNewLine)
   
    DataSet.Close
    Conn.Close

End Function

Function ListMilkrunAbteilungen(ByVal ID_Milkrun As Integer) As Variant
       
    Call DeclareVariables
       
    Set Conn = CreateObject("ADODB.Connection")
    Set DataSet = CreateObject("ADODB.recordset")
   
    Dim TempString As String
   
    QueryString = "SELECT Milkrun_Abteilungen.ID as MID, Anlagen.Bezeichnung FROM Milkrun_Abteilungen LEFT JOIN Anlagen ON Milkrun_Abteilungen.ID_Anlage = Anlagen.ID WHERE Milkrun_Abteilungen.ID_Milkrun = " & ID_Milkrun & " ORDER BY Anlagen.Bezeichnung ASC"
   
    Conn.Open ConnectionString
    DataSet.Open QueryString, Conn
   
    If (DataSet.RecordCount <> 0) Then
        Do While Not DataSet.EOF
           TempString = TempString & DataSet.Fields("MID") & DataDivision & DataSet.Fields("Bezeichnung") & vbNewLine
           DataSet.MoveNext
        Loop
    End If
   
    If Len(TempString) > Len(vbNewLine) Then
        TempString = Left$(TempString, Len(TempString) - Len(vbNewLine))
    End If
   
    ListMilkrunAbteilungen = Split(TempString, vbNewLine)
   
    DataSet.Close
    Conn.Close

End Function

Hast Du vieleicht eine Lösung dafür?
Gruß
Detlef

 

Hallo Detlef,

ich habe mir deinen VBA-Code angesehen und dabei ist mir gleich in den ersten Zeilen ein typischer Heimtücke-Fehler aufgefallen, der bei VBA im Vergleich zu leistungsfähigeren Programmiersprachen auftritt - auch das aktuelle Visual-Basic innerhalb von Visual-Studio kann da schon lange mehr:
Du hast nämich folgende Deklarationen auf Modulebene:

Code:

Option Explicit

Public FilePath, Provider, ConnectionString, QueryString, DataDivision As String
Public Conn, DataSet As Object

Public TempSheet As Worksheet

1) Die 1.Zeile "Option Explicit" ist ok und auch bestens.
2) In der 2. Zeile beginnen die Probleme: So wie die Deklaration gehalten ist, wird nur "DataDivision" als String deklariert, während die Variablen "FilePath", "Provider", "ConnectionString" und "QueryString" als Variant deklariert werden durch VBA, weil hinter diesen Variablenbezeichnern kein "As String" folgt.
3) In der 3. Zeile setzt sich das Problem fort: Dort wird nur "DataSet" als Object deklariert, während "Conn" wiederum als Variant deklariert wird vom VBA-Compiler.

Korrekt lautet die Deklaration auf Modulebene wie folgt:

Code:

Option Explicit

Public FilePath As String, Provider As String, ConnectionString As String, QueryString As String, DataDivision As String
Public Conn As Object, DataSet As Object

Public TempSheet As Worksheet

Es ist ein "Schönheitsfehler", der sich fürs Erste nicht auswirkt, denn der Typ "Variant" kann die meisten Typen aufnehmen. Es wird halt mehr Speicher und Rechenzeit verbraten und der VBA-Compiler kann den folgenden Code nicht so effizient parsen und auf Fehler überprüfen.

Ich nehme an, dass die Problemzeile in der SUB DeclareVariables() liegt und zwar bei:
Set TempSheet = Worksheets("Temp")
Richtig dabei ist, dass, wenn ein 2. Workbook (d.h. eine 2. Excel-Datei) geöffnet wurde, in dem es kein Arbeitsblatt "Temp" gibt und dieses das aktive Workbook ist, es bei Aufruf vorgenannter Codezeile zwingend zu einem Fehler "Index außerhalb des Bereiches" kommen muss. Diese Codezeile bewirkt nämlich, dass stets das aktive Workbook (die aktive Excel-Datei = Arbeitsmappe) genommen wird, weil nämlich bei fehlender Workbook-Angabe der VBA-Compiler Folgendes unterstellt:
Set TempSheet = ActiveWorkbook.Worksheets("Temp")
Damit dies nicht passiert, muss man VBA mitteilen, welches Workbook gemeint ist.
Set TempSheet = ThisWorkbook.Worksheets("Temp")
Damit sucht VBA in dem Workbook, in dem dieser VBA-Code enthalten ist, und in diesem Workbook sollte es dann ein Arbeitsblatt "Temp" geben.

 

Hallo Detlef,

in Fortsetzung meines vorigen Postings ist mir nachträglich Folgendes eingefallen:
Ob es nicht besser wäre wie folgt zu programmieren:

Code:

Thisworkbook.Activate
Set TempSheet = Worksheets("Temp")

statt nur Set TempSheet = ThisWorkbook.Worksheets("Temp")
Du verwendest nämlich diese SUB DeclareVariables() noch in weiteren Funktionen mit Zugriffen wie
Cells(1,1)... etc.
Diese Zelle A1 muss sicher auch im richtigen Workbook enthalten sein und nicht etwa in dem zwischenzeitlich dazwischen geschummelten Workbook.