Articles

Problems with ADO copy from one excel file to another

April 22, 2012 at 18:50:28
Specs: Windows XP

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


See More: Problems with ADO copy from one excel file to another

Report •


#1
April 27, 2012 at 04:34:01

Omigosh - so many mistakes in the code. You gotta do some homework, mate.

Couple of observations -
1. missing '.xls' in file name in strDB value
2. missing curly braces in strDriver value

Go to msdn.com and search this.

You should also add an ODBC DSN to Excel so you can skip all the connection syntax.


Report •
Related Solutions


Ask Question