Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL and Excel problem
I am trying to connect to an SQL database that is online, (SQL Server of
course) but for some reason I am getting an error within my connection string. I basically want to create a Macro that will allow users to download data from the SQL database into certain fields within the Excel spreadsheet, the code that I have so far is below: Sub SPICEdownload_indexvalue() 'On Error GoTo datapullerr Dim wk As Workbook Dim data As Worksheet, para As Worksheet Dim Sql$ Set wk = ThisWorkbook Set data = wk.Sheets("S&P 500 PKA NET TR 15%") Dim wrkodbc As Workspace Dim db As DAO.Database Dim rs As DAO.Recordset Dim r As Range 'Dim ts As String Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _ "admin", "", dbUseODBC) Set db = wrkodbc.OpenDatabase("Spice", , , "ODBC;DSN=SPICE;UID=UserID;pwd=Password;SERVER=Ser ver;") ts = "" 'i = 3 'While Trim(data.Cells(i, 1)) < "" 'ts = ts & IIf(ts < "", "or ", "") & "(Index_id = " & Trim(data.Cells(i, 1)) & " and index_date='" & _ 'Format(Trim(data.Cells(i, 2)), "dd-MMM-yyyy") & "') " 'i = i + 1 'Wend Sql = "SELECT S&P_500_PKA Ethical, S&P_500_PKA_Ethica_Indx_Div WHERE order by index_id" Set rs = db.OpenRecordset(Sql, dbOpenSnapshot) i = 3 While Trim(data.Cells(i, 1)) < "" data.Range("A" & i & ":C" & i) = Array(rs!index_id, rs!index_dividend, rs!close_index_dividend) rs.MoveNext i = i + 1 Wend rs.Close db.Close Set sb = Nothing Set rs = Nothing End Sub So if anyone can shed some light on this it would be greatly appreciated, I'm thinking maybe it is something with my Syntax, regardless when I debug it, it says there is something wrong with my connection string, tell me what you think |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 | Excel Programming | |||
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 | Excel Programming | |||
Weird problem with Excel 2000...Worksheets disappearing in a shared Excel file | Excel Programming | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) |