calgary_amy
New Member
- Joined
- Jul 24, 2008
- Messages
- 24
Hi,
I'm writing a macro to import data using the Import External Data function. This is new to me and I used someone else's code as a guideline. Here is my code:
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
End Sub
I get a Run-time error '448': Named argument not found. Anyone know why?
Thank you,
Amy
I'm writing a macro to import data using the Import External Data function. This is new to me and I used someone else's code as a guideline. Here is my code:
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
End Sub
I get a Run-time error '448': Named argument not found. Anyone know why?
Thank you,
Amy