Syntax anpassen!

Sheets("Mon").Select Range("A2").Select With Selection.ListObject.QueryTable .Connection = Array(Array( _ "ODBC;DSN=arch;UID=aaa;pwd=aaa;DBQ=SARCH;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10 ; _ FDL=10; _ LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM" _ ), Array("=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")) .CommandText = Array( _ "SELECT * FROM Tabelle a WHERE.....") .Refresh BackgroundQuery:=False End With

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub UpDate_QuerryTable() Dim strSheet As String Dim strZelle As String Dim strCon As String, strCom As String Dim strUID As String, strPwd As String, strAPA As String strSheet = "Mon" strZelle = "A2" strUID = "MyUserID" strPwd = "MeinPasswort" strAPA = "T" strCon = "ODBC;DSN=arch;UID=" & strUID strCon = strCon & ";pwd=" & strPwd strCon = strCon & ";DBQ=SARCH;DBA=W;APA=" & strAPA & ";EXC=F;FEN=T;QTO=T;FRC=10;" strCon = strCon & "FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;" strCon = strCon & "NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;" strCom = "SELECT * FROM Tabelle a WHERE....." Sheets(strSheet).Select Range(strZelle).Select With Selection.ListObject.QueryTable .Connection = strCon .CommandText = strCom .Refresh BackgroundQuery:=False End With End Sub

Hallo Snewi,
strAPA war nur ein weiteres Beispiel für einen variablen Parameter.
In deinem Fall ist nicht unbedingt eine Public-Variable erforderlich.
Du kannst die variablen Werte auch als Parameter von einer anderen Prozedur übergeben.
Gruß
Franz

Sub Update_Mon()
Call UpDate_QuerryTable(strSheet:="Mon", _
strDBQ:="SARCH", _
strDSN:="arch", _
strUID:="MySpecialUserID", _
strPwd:="MeinSPecialPasswort", _
strZelle:="A2")
End Sub
'Ohne Optionale Parameter
'Sub UpDate_QuerryTable(ByVal strSheet As String, ByVal strDBQ As String, _
ByVal strDSN As String, ByVal strUID As String, ByVal strPwd As String, _
ByVal strZelle As String)
'Mit Optionalen Parametern
Sub UpDate_QuerryTable(strSheet As String, strDBQ As String, strDSN As String, _
Optional ByVal strUID As String = "MyUserID", _
Optional ByVal strPwd As String = "MeinPasswort", _
Optional ByVal strZelle As String = "A2")
Dim strCon As String, strCom As String
strCon = "ODBC;DSN=" & strDSN & ";UID=" & strUID
strCon = strCon & ";pwd=" & strPwd
strCon = strCon & ";DBQ=" & strDBQ & ";DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;"
strCon = strCon & "FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;"
strCon = strCon & "NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
strCom = "SELECT * FROM Tabelle a WHERE....."
Sheets(strSheet).Select
Range(strZelle).Select
With Selection.ListObject.QueryTable
.Connection = strCon
.CommandText = strCom
.Refresh BackgroundQuery:=False
End With
End Sub

Beliebteste Forumthreads (12 Monate)

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread