Problems with ADO copy from one excel file to another

[dahlgrenen]

Hi I am trying to use this code, but I get un error on the row
datConnection.Open "DBQ=" & strDB & amp, "," & strDriver & amp, ",UID=admin,"
I don't fully understand the code or what is wrong, could I get help?
The error says
'runtime error -2147467259(8004005}
[microsoft][ODBC driver Maneger]datasource not found and no default driver specified'

thankyou!


Sub ImporteraExcelTillExcel_ADO()

'--------------------------------------------------------------
'importerar data fr?n en extern Excelbok utan att ?ppna den
'--------------------------------------------------------------

'variabeldeklareringar
Dim datConnection As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim recRubrik As ADODB.Field
Dim strDB, strSQL As String
Dim strDriver As String
Dim i As Long

's?kv?g till den externa Excelfilen
strDB = "C:\documents and setting\tcn0422\desktop\Dealer KPI Process Mapping report ATOC - 2010 - att arbeta med"


'uppkoppling
Set datConnection = New ADODB.Connection
Set recSet = New ADODB.Recordset
strDriver = "DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
datConnection.Open "DBQ=" & strDB & amp, "," & strDriver & amp, ",UID=admin,"

'SQL-f?rfr?gan
strSQL = "SELECT * FROM [Sheet1$B1:B10]" 'cellomr?de p? visst ark
'strSQL = "SELECT * FROM [Cellomr?desnamn]" 'f?r namngivna cellomr?den

'?ppnar ett "recordset"
recSet.Open strSQL, datConnection, adOpenStatic

'kopierar in ny data
ActiveSheet.Range("A2").CopyFromRecordset recSet

'kopierar kolumnrubriker
i = 1
For Each recRubrik In recSet.Fields
ActiveSheet.Cells(1, i) = _
recRubrik.Name: i = i + 1
Next recRubrik

'kopplar ned (viktigt!)
recSet.Close
datConnection.Close

'st?nger ned objekten (viktigt!)
Set recSet = Nothing
Set datConnection = Nothing

End Sub​

 

[CharlesChuckieCharles]

I think you don't need the comma's (",")

try here

http://www.connectionstrings.com/excel-2007

I think "DBQ=" should be "DSN="

 

[dahlgrenen]

okey thank you for the help.
I find that one problem is that all the ”&amp” in the text should just be ”&". Now instead I have a problem a the row
recSet.Open strSQL, datConnection, adOpenStatic. But I don't know what.