Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing from access
i have the below that imports data from a access recorded from a macro. I want to pick up a date from another sheet to use as the (ts_i_hist_agent_data.row_date={ts '2006-08-08 00:00:00'}) i have tried using a my as date and copying the date to mydate but this doesnt work? Also i have to enter a password each time i run this where in the code can i put in the pass word so i dont have to do this each time? ive tried chaning save password to true but no joy? Range("A1").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;Description=dialler_access;DRIVER=SQL Server;SERVER=wrkvictohok13be;UID=dialler_access;; APP=Microsoft Office 2003;WSID=WRKQUEEN" _ ), Array("SRK4EC4")), Destination:=Range("A1")) ..CommandText = Array( _ "SELECT ts_i_hist_agent_data.agent_id, ts_i_hist_agent_data.alloc, ts_i_hist_agent_data.avail, ts_i_hist_agent_data.camp_id, ts_i_hist_agent_data.ccr_id, ts_i_hist_agent_data.row_date, ts_i_hist_agent_" _ , _ "data.row_hour, ts_i_hist_agent_data.talk, ts_i_hist_agent_data.wrap" & Chr(13) & "" & Chr(10) & "FROM d_dialler.dbo.ts_i_hist_agent_data ts_i_hist_agent_data" & Chr(13) & "" & Chr(10) & "WHERE (ts_i_hist_agent_data.row_date={ts '2006-08-08 00:00:00'})" _ ) ..Name = "Query from dialler_access" ..FieldNames = True ..RowNumbers = False ..FillAdjacentFormulas = False ..PreserveFormatting = True ..RefreshOnFileOpen = False ..BackgroundQuery = True ..RefreshStyle = xlInsertDeleteCells ..SavePassword = True ..SaveData = True ..AdjustColumnWidth = True ..RefreshPeriod = 0 ..PreserveColumnInfo = True ..Refresh BackgroundQuery:=False End With End Sub -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=570400 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing from access
s = format(Worksheets("Sheet1").Range("A1").Value,"yyy y-mm-dd 00:00:00")
then (ts_i_hist_agent_data.row_date={ts '" & s & "'}) -- Regards, Tom Ogilvy "ceemo" wrote: i have the below that imports data from a access recorded from a macro. I want to pick up a date from another sheet to use as the (ts_i_hist_agent_data.row_date={ts '2006-08-08 00:00:00'}) i have tried using a my as date and copying the date to mydate but this doesnt work? Also i have to enter a password each time i run this where in the code can i put in the pass word so i dont have to do this each time? ive tried chaning save password to true but no joy? Range("A1").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;Description=dialler_access;DRIVER=SQL Server;SERVER=wrkvictohok13be;UID=dialler_access;; APP=Microsoft Office 2003;WSID=WRKQUEEN" _ ), Array("SRK4EC4")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT ts_i_hist_agent_data.agent_id, ts_i_hist_agent_data.alloc, ts_i_hist_agent_data.avail, ts_i_hist_agent_data.camp_id, ts_i_hist_agent_data.ccr_id, ts_i_hist_agent_data.row_date, ts_i_hist_agent_" _ , _ "data.row_hour, ts_i_hist_agent_data.talk, ts_i_hist_agent_data.wrap" & Chr(13) & "" & Chr(10) & "FROM d_dialler.dbo.ts_i_hist_agent_data ts_i_hist_agent_data" & Chr(13) & "" & Chr(10) & "WHERE (ts_i_hist_agent_data.row_date={ts '2006-08-08 00:00:00'})" _ ) .Name = "Query from dialler_access" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=570400 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing from access
thats great thank you very much. Does anyone have any clues on the password bit. also when i run it, it sets it up from scratch witch moves the old date to the right to fit in the new data. is there anyway i can just get it to refresh over the top of the old import without this happening. i did think of deleteing the columns first but this will interfer with formulas linking to those cells -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=570400 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing from access
does anyone know how to refresh with the new date raher than re-creatng the query from scratch? -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=570400 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing from Access | Excel Worksheet Functions | |||
Importing from Access | Excel Discussion (Misc queries) | |||
Importing from Access | Excel Worksheet Functions | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
Importing from MS Access | Excel Programming |