Refresh BackgroundQuery:=False

Forumthread: Refresh BackgroundQuery:=False

26.05.2008 12:50:00

Diana

Hallo zusammen,
ich habe eine Makro mit dem Recorder aufgezeichnet. Dieses soll Daten aus einer Datenbank auslesen und in eine Datei speichern.


Sub H2x()
Dim Jahr As String
Dim Monat As String
Dim Tag As String
Jahr = InputBox("Welches Jahr soll ausgewertet werden: ", "Abfrage Jahr", 2008)
Monat = InputBox("Welcher Monat soll ausgewertet werden: ", "Abfrage Monat")
Tag = InputBox("Welcher Tag soll ausgewertet werden: ", "Abfrage Tag")
Application.Run "PERSONL.XLS!P21z02v01"
'   Application.Run "PERSONL.XLS!M22z02v01"
'   Application.Run "PERSONL.XLS!F23z02v01"
'   Application.Run "PERSONL.XLS!G24z02v01"
'   Application.Run "PERSONL.XLS!Sonz02v01"
End Sub


Sub P21z02v01()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DATABASE=K055;DESCRIPTION=ODBC-Zugriff für VisuData;DSN=K055_Read;OPTION=3;PORT=  _
_
3306;SERVER=Server;UID=K055_Read;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT k055z02_0.Zeit, k055z02_0.A21100001, k055z02_0.A21100101, k055z02_0.A21100102,   _
_
k055z02_0.A21100501, k055z02_0.A21112021, k055z02_0.A21112201, k055z02_0.A21112211, k055z02_0. _
A21112212, k055z02_0" _
, _
".A21112301, k055z02_0.A21112302, k055z02_0.A21112311, k055z02_0.A21112501, k055z02_0.  _
_
A21112511, k055z02_0.A21112512, k055z02_0.A21112521, k055z02_0.A21112601, k055z02_0.A21112611,  _
k055z02_0.A21122021," _
, _
" k055z02_0.A21122101, k055z02_0.A21122102, k055z02_0.A21200001, k055z02_0.A21200101,   _
_
k055z02_0.A21200102, k055z02_0.A21202021, k055z02_0.A21202022, k055z02_0.A21400001, k055z02_0. _
A21400011, k055z02_0." _
, _
"A21400021, k055z02_0.A21422101, k055z02_0.A21532181, k055z02_0.A21532281, k055z02_0. _
A21532381, k055z02_0.A21532481, k055z02_0.A21532581, k055z02_0.A21532681, k055z02_0.A21532781,  _
k055z02_0.A21532881, " _
, _
"k055z02_0.A21532981, k055z02_0.A21542081, k055z02_0.A21542181, k055z02_0.A21542281" &   _
_
Chr(13) & "" & Chr(10) & "FROM k055.k055z02 k055z02_0" & Chr(13) & "" & Chr(10) & "WHERE ( _
k055z02_0.Zeit Between {ts '2008-0'&Monat&'-'&Tag&' 00:00:00'} And {ts '2008-0'&Monat&'-'&Tag&' 23:59:59'})" _
)
.Name = "Abfrage von K055_Read"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
ChDir "S:\RAM4H\21P\" & Jahr
ActiveWorkbook.SaveAs Filename:="S:\RAM4H\21P\" & Jahr & "\21" & Monat & Tag & "11.dat",  _
FileFormat:=xlCSV, _
CreateBackup:=False
End Sub

Beim Ablauf läuft das Makro nicht fehlerfrei. Der Debugger steht bei: Refresh BackgroundQuery:=False. Das heißt doch, dass der Ablauf vorher schon nicht fehlerfrei ist, oder?
Liegt es an den Formaten für die Datumswerte?
Wäre super, wenn jemand eine Lösung hätte.
Gruß Diana

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Refresh BackgroundQuery:=False

27.05.2008 07:52:18

fcs
AW: Refresh BackgroundQuery:=False

27.05.2008 16:40:12

Diana
AW: Refresh BackgroundQuery:=False

27.05.2008 22:18:10

fcs

AW: Refresh BackgroundQuery:=False

27.05.2008 07:52:18

fcs

Hallo Diana,
ob der Datenbank-Abfrage-String korrekt zusammengbaut wird kann ich schlecht beurteilen, der ist beim Posten im Forum scheinbar arg zerfledderd. Im SQL-String sind die Zeilenschaltungen (Chr(10) und Chr(13)) vor FROM und WHERE meines Wissens nicht erforderlich sind. ggf. muss du sie wieder einbauen.
Auf alle Fälle muss du die Variablen Jahr, Monat und Tag so deklarieren, dass sie in allen Prozeduren zur Verfügung stehen. Dies kann man so machen, dass man die Variablen zu Beginn des Moduls als Private oder Public (wenn auch in anderen Modulen verwendet) deklariert oder als Parameter übergibt. Ich hab die Variante der Variablendeklaration gewählt.
Ich hab die Dekaration der Variablen und die Inputboxen angepasst, eine Eingabewert-Prüfung ergänzt, und eine Variable eingefügt, in der das Datum in dem Format erzeugt wird, wie es die Datenbankabfrage benötigt.
Gruß
Franz


'Variablen, die in allen Prozeduren verwendet werden
Private strDatum As String
Private Jahr As Variant
Private Monat As Variant
Private Tag As Variant
Sub H2x()
'Eingabe von Jahr, Monat und Tag als Zahl
Jahr = Application.InputBox(Prompt:="Welches Jahr soll ausgewertet werden: ", _
Title:="Abfrage Jahr", Default:=2008, Type:=1)
Monat = Application.InputBox(Prompt:="Welcher Monat soll ausgewertet werden: ", _
Title:="Abfrage Monat", Type:=1)
Tag = Application.InputBox(Prompt:="Welcher Tag soll ausgewertet werden: ", _
Title:="Abfrage Tag", Type:=1)
'Prüfung der Eingabe auf gültiges Datum
If IsDate(Tag & "." & Monat & "." & Jahr) Then
'Datum im Format für die Datenbank-Abfrage
strDatum = Format(DateSerial(Jahr, Monat, Tag), "YYYY-MM-DD")
'Formate für CSV-Dateiname
Jahr = Format(Jahr, "0000")
Monat = Format(Monat, "00")
Tag = Format(Tag, "00")
'    Call P21z02v01
Application.Run "PERSONL.XLS!P21z02v01"
'   Application.Run "PERSONL.XLS!M22z02v01"
'   Application.Run "PERSONL.XLS!F23z02v01"
'   Application.Run "PERSONL.XLS!G24z02v01"
'   Application.Run "PERSONL.XLS!Sonz02v01"
Else
MsgBox "Ungültiger Wert bei Eingabe von Jahr, Monat oder Tag!" & vbLf _
& "Tag: " & Tag & vbLf & "Monat: " & Monat & vbLf & "Jahr: " & Jahr
End If
End Sub
Sub P21z02v01()
Dim strCon As String, objWks As Worksheet
ActiveWorkbook.Worksheets.Add
Set objWks = ActiveSheet 'Objektvariable für Tabellenblatt
strCon = "ODBC;DATABASE=K055;DESCRIPTION=ODBC-Zugriff für VisuData;"
strCon = strCon & "DSN=K055_Read;OPTION=3;PORT=3306;SERVER=Server;UID=K055_Read;"
With objWks.QueryTables.Add(Connection:=strCon, Destination:=objWks.Range("A1"))
.CommandText = Array("SELECT k055z02_0.Zeit, k055z02_0.A21100001, " _
& "k055z02_0.A21100101, k055z02_0.A21100102, k055z02_0.A21100501, " _
& "k055z02_0.A21112021, k055z02_0.A21112201, k055z02_0.A21112211, " _
& "k055z02_0.A21112212, k055z02_0.A21112301, k055z02_0.A21112302, " _
& "k055z02_0.A21112311, k055z02_0.A21112501, k055z02_0.A21112511, " _
& "k055z02_0.A21112512, k055z02_0.A21112521, k055z02_0.A21112601, " _
& "k055z02_0.A21112611, k055z02_0.A21122021, k055z02_0.A21122101, " _
& "k055z02_0.A21122102, k055z02_0.A21200001, k055z02_0.A21200101, " _
& "k055z02_0.A21200102, k055z02_0.A21202021, k055z02_0.A21202022, " _
& "k055z02_0.A21400001, k055z02_0.A21400011, k055z02_0.A21400021, " _
& "k055z02_0.A21422101, k055z02_0.A21532181, k055z02_0.A21532281, " _
& "k055z02_0.A21532381, k055z02_0.A21532481, k055z02_0.A21532581, " _
& "k055z02_0.A21532681, k055z02_0.A21532781, k055z02_0.A21532881, " _
& "k055z02_0.A21532981, k055z02_0.A21542081, k055z02_0.A21542181, " _
& "k055z02_0.A21542281 FROM k055.k055z02 k055z02_0" _
& " WHERE (k055z02_0.Zeit Between {ts '" & strDatum & " 00:00:00'} And {ts '" _
& strDatum & " 23:59:59'})")
.Name = "Abfrage von K055_Read"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
ChDir "S:\RAM4H\21P\" & Jahr
ActiveWorkbook.SaveAs FileName:="S:\RAM4H\21P\" & Jahr & "\21" & Monat & Tag & "11.dat", _
FileFormat:=xlCSV, CreateBackup:=False
Set objWks = Nothing
End Sub

AW: Refresh BackgroundQuery:=False

27.05.2008 16:40:12

Diana

Hallo Franz,
herzlichen Dank für deine Hilfe. Es scheint schon besser zu laufen. Dennoch hängt es bei dem Sub P21z02v01() ... Laufzeitfehler 13: Typen unverträglich. Die Variablen sind bis auf die Zeit-Variable alles Messwerte (Zahlen). Die Zeit hat das Format YYYY-MM-DD hh-mm-ss.
Kannst du mir da noch einmal helfen?
Vielen Dank im voraus.
Diana

AW: Refresh BackgroundQuery:=False

27.05.2008 22:18:10

fcs

Hallo Diana,
viel machen kann ich da nicht mehr. Ich hab den Code jetzt so modiifziert, dass das Datum/Zeitformat, das mit der SQL-Abfrage übergeben wird, leicht geändert werden kann. Ich hab auch ein paar Variablen eingefügt, so dass man die Werte die verarbeitet werden etws leichter kontrollieren kann.
Ansonsten muss du mal den Ersteller/Betreuer der Datenbank fragen, ob die Syntax der generierten SQL-Abfrage korrekt ist und in welcher Form Datum/Zeit in der Abfrage angegeben werden müssen.
Gruß
Franz


Option Explicit
Private strDatum As String
Private strStart As String
Private strEnde As String
Private Jahr As Variant
Private Monat As Variant
Private Tag As Variant
Sub H2x()
'Eingabe von Jahr, Monat und Tag als Zahl
Dim strZeitFormat As String
Jahr = Application.InputBox(Prompt:="Welches Jahr soll ausgewertet werden: ", _
Title:="Abfrage Jahr", Default:=2008, Type:=1)
Monat = Application.InputBox(Prompt:="Welcher Monat soll ausgewertet werden: ", _
Title:="Abfrage Monat", Type:=1)
Tag = Application.InputBox(Prompt:="Welcher Tag soll ausgewertet werden: ", _
Title:="Abfrage Tag", Type:=1)
'Prüfung der Eingabe auf gültiges Datum
If IsDate(Tag & "." & Monat & "." & Jahr) Then
'Datum/Zeiz im Format für die Datenbank-Abfrage
strDatum = Format(DateSerial(Jahr, Monat, Tag), "YYYY-MM-DD")
strZeitFormat = "YYYY-MM-DD hh:mm:ss" 'ISO-Zeit-Format
'    strZeitFormat = "DD.MM.YYYY hh:mm:ss" 'Deutsches Datumsformat
'    strZeitFormat = "MM/DD/YYYY hh:mm:ss" 'US-Format
'    strZeitFormat = "YYYY-MM-DD hh-mm-ss" '? Format
strStart = Format(DateSerial(Jahr, Monat, Tag) + TimeSerial(0, 0, 0), strZeitFormat)
strEnde = Format(DateSerial(Jahr, Monat, Tag) + TimeSerial(23, 59, 59), strZeitFormat)
'Formate für CSV-Dateiname
Jahr = Format(Jahr, "0000")
Monat = Format(Monat, "00")
Tag = Format(Tag, "00")
Application.Run "PERSONL.XLS!P21z02v01"
'   Application.Run "PERSONL.XLS!M22z02v01"
'   Application.Run "PERSONL.XLS!F23z02v01"
'   Application.Run "PERSONL.XLS!G24z02v01"
'   Application.Run "PERSONL.XLS!Sonz02v01"
Else
MsgBox "Ungültiger Wert bei Eingabe von Jahr, Monat oder Tag!" & vbLf _
& "Tag: " & Tag & vbLf & "Monat: " & Monat & vbLf & "Jahr: " & Jahr
End If
End Sub
Sub P21z02v01()
Dim strCon As String, strSQL As String
Dim strVerzeichnis As String, strDateiname As String
Dim objWKS As Worksheet
ActiveWorkbook.Worksheets.Add
Set objWKS = ActiveSheet
strCon = "ODBC;DATABASE=K055;DESCRIPTION=ODBC-Zugriff für VisuData;"
strCon = strCon & "DSN=K055_Read;OPTION=3;PORT=3306;SERVER=Server;UID=K055_Read;"
strSQL = "SELECT k055z02_0.Zeit, k055z02_0.A21100001, " _
& "k055z02_0.A21100101, k055z02_0.A21100102, k055z02_0.A21100501, " _
& "k055z02_0.A21112021, k055z02_0.A21112201, k055z02_0.A21112211, " _
& "k055z02_0.A21112212, k055z02_0.A21112301, k055z02_0.A21112302, " _
& "k055z02_0.A21112311, k055z02_0.A21112501, k055z02_0.A21112511, " _
& "k055z02_0.A21112512, k055z02_0.A21112521, k055z02_0.A21112601, " _
& "k055z02_0.A21112611, k055z02_0.A21122021, k055z02_0.A21122101, " _
& "k055z02_0.A21122102, k055z02_0.A21200001, k055z02_0.A21200101, " _
& "k055z02_0.A21200102, k055z02_0.A21202021, k055z02_0.A21202022, " _
& "k055z02_0.A21400001, k055z02_0.A21400011, k055z02_0.A21400021, " _
& "k055z02_0.A21422101, k055z02_0.A21532181, k055z02_0.A21532281, " _
& "k055z02_0.A21532381, k055z02_0.A21532481, k055z02_0.A21532581, " _
& "k055z02_0.A21532681, k055z02_0.A21532781, k055z02_0.A21532881, " _
& "k055z02_0.A21532981, k055z02_0.A21542081, k055z02_0.A21542181, " _
& "k055z02_0.A21542281 FROM k055.k055z02 k055z02_0" _
& " WHERE (k055z02_0.Zeit Between {ts '" & strStart & "'} And {ts '" _
& strEnde & "'})"
With objWKS.QueryTables.Add(Connection:=strCon, Destination:=objWKS.Range("A1"))
.CommandText = strSQL
.Name = "Abfrage von K055_Read"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
strVerzeichnis = "S:\RAM4H\21P\" & Jahr
'    strVerzeichnis = "C:\Test\Daten"  'Testzeile
ChDir strVerzeichnis
strDateiname = "21" & Monat & Tag & "11.dat"
ActiveWorkbook.SaveAs FileName:=strVerzeichnis & "\" & strDateiname, _
FileFormat:=xlCSV, CreateBackup:=False
End Sub

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Öffne Excel und drücke ALT + F11, um den VBA-Editor zu starten.
Erstelle ein neues Modul: Klicke im Projekt-Explorer mit der rechten Maustaste auf dein Projekt und wähle Einfügen > Modul.

Füge den folgenden Code ein:

Option Explicit
Private strDatum As String
Private Jahr As Variant
Private Monat As Variant
Private Tag As Variant

Sub H2x()
   Jahr = Application.InputBox("Welches Jahr soll ausgewertet werden: ", "Abfrage Jahr", 2008, Type:=1)
   Monat = Application.InputBox("Welcher Monat soll ausgewertet werden: ", "Abfrage Monat", Type:=1)
   Tag = Application.InputBox("Welcher Tag soll ausgewertet werden: ", "Abfrage Tag", Type:=1)

   If IsDate(Tag & "." & Monat & "." & Jahr) Then
       strDatum = Format(DateSerial(Jahr, Monat, Tag), "YYYY-MM-DD")
       Application.Run "PERSONL.XLS!P21z02v01"
   Else
       MsgBox "Ungültiger Wert bei Eingabe von Jahr, Monat oder Tag!"
   End If
End Sub

Sub P21z02v01()
   Dim strCon As String
   Dim objWKS As Worksheet
   Set objWKS = ActiveWorkbook.Worksheets.Add

   strCon = "ODBC;DATABASE=K055;DESCRIPTION=ODBC-Zugriff für VisuData;DSN=K055_Read;"
   With objWKS.QueryTables.Add(Connection:=strCon, Destination:=objWKS.Range("A1"))
       .CommandText = Array("SELECT * FROM Tabelle WHERE Datum = '" & strDatum & "'")
       .Refresh BackgroundQuery:=False
   End With
End Sub

Führe das Makro aus: Gehe zurück zu Excel und drücke ALT + F8, wähle H2x aus und klicke auf Ausführen.

Häufige Fehler und Lösungen

Fehler 13: Typen unverträglich: Dieser Fehler tritt häufig auf, wenn die Variablen nicht korrekt deklariert oder verwendet werden. Achte darauf, dass die Datentypen übereinstimmen, insbesondere bei Datum und Zeit.
Debugging bei .Refresh BackgroundQuery:=False: Wenn das Makro hier anhält, kann das auf einen vorherigen Fehler in der SQL-Abfrage oder bei der Variablenzuweisung hinweisen. Überprüfe den SQL-String auf Syntaxfehler.
Ungültige Datumsformate: Stelle sicher, dass das Datum im Format "YYYY-MM-DD" übergeben wird, um Komplikationen mit der Datenbank zu vermeiden.

Alternative Methoden

Falls die Verwendung von VBA nicht optimal ist, kannst Du auch die Abfrage über das Excel-Datenabfrage-Tool durchführen:

Daten abrufen: Gehe zu Daten > Abrufen und Transformieren > Aus Datenbank.
Wähle die Verbindung: Stelle die Verbindung zu Deiner Datenbank her und wähle die gewünschte Tabelle aus.
Abfrage anpassen: Nutze den Power Query Editor, um Daten zu filtern und zu transformieren, ohne VBA verwenden zu müssen.

Praktische Beispiele

Hier ist ein Beispiel, wie Du die Refresh-Methode mit BackgroundQuery verwenden kannst:

With ActiveSheet.QueryTables(1)
    .Connection = "ODBC;DSN=Deine_DSN;"
    .CommandText = "SELECT * FROM Deine_Tabelle"
    .Refresh BackgroundQuery:=False
End With

Nutze BackgroundQuery:=False, um sicherzustellen, dass die Abfrage abgeschlossen ist, bevor das Makro fortfährt.

Tipps für Profis

Verwende Debug.Print: Nutze Debug.Print vor dem .Refresh-Befehl, um den SQL-String im Direktfenster zu überprüfen und sicherzustellen, dass er korrekt formatiert ist.

Fehlerbehandlung: Implementiere Fehlerbehandlungsroutinen, um Fehler während der Ausführung des Makros abzufangen, wie:

On Error GoTo Fehlerbehandlung
' Dein Code hier
Exit Sub

Fehlerbehandlung:
MsgBox "Ein Fehler ist aufgetreten: " & Err.Description

FAQ: Häufige Fragen

1. Was bedeutet BackgroundQuery:=False?
Das bedeutet, dass die Abfrage synchron ausgeführt wird. Das Makro wartet, bis die Abfrage abgeschlossen ist, bevor es mit dem nächsten Schritt fortfährt.

2. Wie kann ich die Abfrage im Hintergrund laufen lassen?
Setze BackgroundQuery:=True, um die Abfrage asynchron auszuführen. Das Makro wird fortfahren, während die Abfrage im Hintergrund läuft.

3. Was mache ich, wenn die Abfrage nicht funktioniert?
Überprüfe den SQL-String auf Syntaxfehler und stelle sicher, dass die Datenbankverbindung korrekt konfiguriert ist. Debugge den Code Schritt für Schritt, um den Fehler zu identifizieren.

4. Welche Excel-Version wird benötigt?
Dieses Beispiel funktioniert in Excel 2010 und höher, da die Datenabfrage-Funktionalität in früheren Versionen eingeschränkt sein kann.

Beliebteste Forumthreads (12 Monate)

Forumthread: Refresh BackgroundQuery:=False

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Refresh BackgroundQuery in VBA für Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread