|
Das mit dem Hochkomma ging leider nicht, hatte ich schon probiert.
Hab das jetzt mal umgestellt.
Warum bekomme ich jetzt einenADODB Variable nicht definiert Fehler?
Option Explicit
Sub run_sql_in_excel()
'variable declaration
Dim cn As Object 'Connection
Dim rs As Object 'RecordSet
Dim sql As String
Dim start_row As Integer: start_row = 1
Dim start_col As Integer: start_col = 1
Dim iCol As Integer
'Connect to the Data Source
Set cn = CreateObject(“ADODB.Connecion”)
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & "C:\Users\Marcus\Desktop" & "\" & _
"Kopie von Farbübergangserfassung nach Jahren(107).xlsx" & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'write SQL
sql = "Select * from [Übergangserfassung 2022$]"
'run SQL
Set rs = cn.Execute(sql) 'delete data from previous runs in worksheet "Results"
worksheets("Tabelle6").Cells(start_row, start_col).CurrentRegion.Clear
'insert header row worksheet "Results" (only if HDR=YES)
For iCols = 0 To rs.Fields.Count - 1
worksheets("Tabelle 6").Cells(start_row, start_col + iCols).Value = _
rs.Fields(iCols).Name
Next
'paste result of sql to worksheet "Results"
worksheets("Tabelle6").Cells(start_row + 1, start_col).CopyFromRecordset rs
'clean up and free memory
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
|