rr2001
15-04-15, 12:25
geschätzte Leute,
leider werden bei diesem VBA-Script die in sql definierten Spaltenüberschriften (Filiale, Kunde und Inhalt) nicht mit in die Excel-Tabelle übernommen.
Hat jemand eine Idee?
LG
RR
Sub lesen()
Dim Filiale As String
Dim Kunde As String
Dim Inhalt As String
Set CS = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
'Retrieve values from Cells on the sheet to use as selections in the query below
Filiale = ActiveSheet.Range("B1").Value
Kunde = ActiveSheet.Range("D1").Value
Inhalt = ActiveSheet.Range("F1").Value
'MsgBox (Filiale)
'ISeries connection String
ConnectString = "Driver={ISeries Access ODBC Driver};System=xxx.xxx.xx.x;Uid=xxxxxxxx;Pwd=xxxxx xxx;Library=xxxxxxxx;QueryTimeout=0"
CS.Open (ConnectString)
sqlstring = "select chd1cd as Filiale, chd3cd as Kunde, cthptx as Inhalt" & _
" from y2svgen.svkopfv1 "
sqlstring = sqlstring & " where chd1cd = '" & Filiale & "'"
If Kunde <> "" Then
sqlstring = sqlstring & " and chd3cd = '" & Kunde & "'"
End If
If Inhalt <> "" Then
sqlstring = sqlstring & " and upper(cthptx) like upper('%" & Inhalt & "%')"
End If
'Message box can be used for debugging the SQL statement
MsgBox (sqlstring)
RS.Open sqlstring, CS
'Clear Previous contents of Cells
Cells.Select
ActiveSheet.Range("A7:Z65535").ClearContents
'copy the Recordset to excel sheet starting at A7
ActiveSheet.Range("A7").CopyFromRecordset RS
'Close Connection And Recordset
RS.Close
CS.Close
'Reset cell back to A1
ActiveSheet.Range("A1").Select
End Sub
leider werden bei diesem VBA-Script die in sql definierten Spaltenüberschriften (Filiale, Kunde und Inhalt) nicht mit in die Excel-Tabelle übernommen.
Hat jemand eine Idee?
LG
RR
Sub lesen()
Dim Filiale As String
Dim Kunde As String
Dim Inhalt As String
Set CS = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
'Retrieve values from Cells on the sheet to use as selections in the query below
Filiale = ActiveSheet.Range("B1").Value
Kunde = ActiveSheet.Range("D1").Value
Inhalt = ActiveSheet.Range("F1").Value
'MsgBox (Filiale)
'ISeries connection String
ConnectString = "Driver={ISeries Access ODBC Driver};System=xxx.xxx.xx.x;Uid=xxxxxxxx;Pwd=xxxxx xxx;Library=xxxxxxxx;QueryTimeout=0"
CS.Open (ConnectString)
sqlstring = "select chd1cd as Filiale, chd3cd as Kunde, cthptx as Inhalt" & _
" from y2svgen.svkopfv1 "
sqlstring = sqlstring & " where chd1cd = '" & Filiale & "'"
If Kunde <> "" Then
sqlstring = sqlstring & " and chd3cd = '" & Kunde & "'"
End If
If Inhalt <> "" Then
sqlstring = sqlstring & " and upper(cthptx) like upper('%" & Inhalt & "%')"
End If
'Message box can be used for debugging the SQL statement
MsgBox (sqlstring)
RS.Open sqlstring, CS
'Clear Previous contents of Cells
Cells.Select
ActiveSheet.Range("A7:Z65535").ClearContents
'copy the Recordset to excel sheet starting at A7
ActiveSheet.Range("A7").CopyFromRecordset RS
'Close Connection And Recordset
RS.Close
CS.Close
'Reset cell back to A1
ActiveSheet.Range("A1").Select
End Sub