Importing External Data with new DB query

[amyincalgary]

Hi,

I am trying to import data from an external data using VBA and not having ever done this I used someone else's code as a guide:

[VBA]Sub SQLQuery2()
Sheets("RawData").Select
Cells.Select
Selection.ClearContents
Uname = sheetMain.txtUNAME
Pword = sheetMain.txtPWORD

DBASE = "ZET"

STARTDATE = Format(Range("STARTDATE"), "YYYY-MM-DD 00:00:00")
ENDDATE = Format(Range("ENDDATE"), "YYYY-MM-DD 00:00:00")

SQL1 = "DISTINCT bd.CALC_LEVEL, bd.CHARGE_CODE, bd.DATA_SOURCE, bd.DATA_TYPE, bd.DESCRIPTION, bd.INTERVAL_COUNT, bd.NAME, bd.BILL_DETERMINANT_FILE_ID, bd.ID, bd.UOM, bda.NAME, bda.VALUE, bdd.CHARGE_VALUE, bdd.INTERVAL_TIMESTAMP, bdf.OPR_DATE "
SQL2 = "FROM BD_ATTRIBS bda, B_DETERMINANT_DATA bdd, B_DETERMINANT_FILES bdf, B_DETERMINANTS bd "
SQL3 = "WHERE bd.ID = bdd.BILL_DETERMINANT_ID "
SQL4 = "AND bd.ID = bda.BILL_DETERMINANT_ID "
SQL5 = "AND bd.BILL_DETERMINANT_FILE_ID = bdf.ID "
SQL6 = "AND bdf.DOC_TITLE = bd.BILL_DETERMINANT_DOC_TITLE "
SQL7 = "AND (bdf.OPR_DATE>={ts '" & STARTDATE & "'}) "
SQL8 = "AND (bdf.OPR_DATE<={ts '" & ENDDATE & "'}) "
SQL9 = "AND bda.NAME = 'RSRC_ID' "
SQL10 = "AND (bd.CHARGE_CODE like '1%' or bd.CHARGE_CODE like '2%' or bd.CHARGE_CODE like '3%' or bd.CHARGE_CODE like '4%' or bd.CHARGE_CODE like '5%' "
Sheets("RawData").Activate
Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=" & DBASE & ";UID=" & Uname & ";PWD=" & Pword & ";DBQ=" & DBASE & ";"), _
CommandText:=Array("SELECT " & SQL1 & SQL2 & SQL3 & SQL4 & SQL5 & SQL6 & SQL7 & SQL8 & SQL9 & SQL10))

End With

[/VBA]
When I ran the code I got a RUn-Time error 448: Named Argument Not Found. Does anyone know why this would be?

Thank you,
Amy

[alexgiurca]

Hi Amy!

You can view my post at: http://www.vbaexpress.com/forum/showthread.php?t=25157

There you can find an attached Access Database (together with VBA code) and an XLS file. I have demonstrated pretty clearly what and how to do to read from (and export to) an XLS file using ADO and DAO.

For your convenience here is a snippet:
Private Sub btnload_Click()
Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim myrec As DAO.Recordset
Set myrec = CurrentDb.OpenRecordset("reportfc")
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("C:/Data_Local_old/book1.xls")
Set xlsht = xlWrkBk.Worksheets(5)
myrec.AddNew
myrec.Fields("idrfc") = 1
myrec.Fields("idr") = 1
myrec.Fields("ido") = 1
myrec.Fields("idv") = 0
myrec.Fields("name") = xlsht.Cells(4, "A")
myrec.Fields("tehname") = xlsht.Cells(4, "B")
myrec.Fields("flag_activ") = 1
myrec.Fields("data") = "10.02.2009"
MsgBox xlsht.Shapes.Item(1).Name
'myrec.Fields("file") = xlsht.Shapes.Item("Picture 1") ‘ this does not work…
myrec.Fields("nota") = "no comment!"
myrec.Update
myrec.Close
MsgBox ("Successfully loaded data from excel sheet")
End Sub

And with ADO:

Dim sqlcon As String
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim crow As Integer
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
On Error GoTo Except
Set con = New ADODB.Connection
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = "data source=C:\Documents and Settings\x01020750\My Documents\SAPBW1.mdb"
con.Mode = adModeReadWrite
con.Open
MsgBox "Connected via " & con.Provider & " OLE DB Provider!", vbInformation
Except:
MsgBox Err.Description, vbCritical

Let me know if you have further questions and if my code solved your problem!

Regards,
Alex