Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with result of database query, instead of inserting
I'm using thecode below (derived using Record Macro, and them modified to
include file and directory name variables) to extract data from an Access database table and copy to a worksheet called "LinkedData3". It works, but it inserts the data, and so the sheet grows with every refresh. Could someone tell me how I can change the code to "replace" the full content of the sheet rather than "insert"? Regards... ================================================== ===== With Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & ReportFileName & ";DefaultDir= &" & DataFileDirectory & ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout" _ ), Array("=5;")), Destination:=Worksheets("LinkedData3").Range("A1") ) .CommandText = Array( _ "SELECT `Agent Report`.datetime, `Agent Report`.groupNumber, `Agent Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall, `Agent Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _ , _ "ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime, `Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13) & "" & Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent Report`" & Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _ , _ "t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY `Agent Report`.datetime" _ ) .Name = "Query from MS Access Database" .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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with result of database query, instead of inserting
Android
The best way to do this is to not Add a QueryTable every time you want to run it. You can just change some properties of your QT and Refresh it. It looks like you're just changing the connection string and not the sql. See here http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn Yours will be a little different than what's on that page. You need to extract the old database and directory and replace it with the new one. Here's an example: Dim OldReport As String Dim DBQStart As Long Dim DBQEnd As Long Dim OldDirect As String Dim DefStart As Long Dim DefEnd As Long Const sDBQ As String = "DBQ=" Const sDef As String = "DefaultDir=" With Sheet1.QueryTables(1) DBQStart = InStr(1, .Connection, sDBQ) + Len(sDBQ) DBQEnd = InStr(DBQStart, .Connection, ";") OldReport = Mid(.Connection, DBQStart, DBQEnd - DBQStart) DefStart = InStr(1, .Connection, sDef) + Len(sDef) DefEnd = InStr(DefStart, .Connection, ";") OldDirect = Mid(.Connection, DefStart, DefEnd - DefStart) .Connection = Left(.Connection, DBQStart - 1) _ & Replace(.Connection, OldReport, _ ReportFileName, DBQStart, 1) .Connection = Left(.Connection, DefStart - 1) _ & Replace(.Connection, OldDirect, _ DataFileDirectory, DefStart, 1) .Refresh End With -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Android" wrote in message ... I'm using thecode below (derived using Record Macro, and them modified to include file and directory name variables) to extract data from an Access database table and copy to a worksheet called "LinkedData3". It works, but it inserts the data, and so the sheet grows with every refresh. Could someone tell me how I can change the code to "replace" the full content of the sheet rather than "insert"? Regards... ================================================== ===== With Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & ReportFileName & ";DefaultDir= &" & DataFileDirectory & ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout" _ ), Array("=5;")), Destination:=Worksheets("LinkedData3").Range("A1") ) .CommandText = Array( _ "SELECT `Agent Report`.datetime, `Agent Report`.groupNumber, `Agent Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall, `Agent Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _ , _ "ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime, `Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13) & "" & Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent Report`" & Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _ , _ "t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY `Agent Report`.datetime" _ ) .Name = "Query from MS Access Database" .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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with result of database query, instead of inserting
Thanks for the help. This is a little complex for me for now -- I need to
spend more time before coding it. However, FYI, a workaround I have implemented in the meanwhile is 1) Copy the new data to when my defined Ranges moved to: Worksheets("Linked Data").Range("a1:L65536").Copy Destination:=Worksheets("Linked Data").Range("M1") 2) Delete the original location of the new data Worksheets("Linked Data").Range("A:L").Delete shift:=xlShiftToLeft Not pretty, and the column ranges are hardcoded -- but does an interim job. Thanks again for the code you provided. I will need it to make this more reliable. Android. "Dick Kusleika" wrote in message ... Android The best way to do this is to not Add a QueryTable every time you want to run it. You can just change some properties of your QT and Refresh it. It looks like you're just changing the connection string and not the sql. See here http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn Yours will be a little different than what's on that page. You need to extract the old database and directory and replace it with the new one. Here's an example: Dim OldReport As String Dim DBQStart As Long Dim DBQEnd As Long Dim OldDirect As String Dim DefStart As Long Dim DefEnd As Long Const sDBQ As String = "DBQ=" Const sDef As String = "DefaultDir=" With Sheet1.QueryTables(1) DBQStart = InStr(1, .Connection, sDBQ) + Len(sDBQ) DBQEnd = InStr(DBQStart, .Connection, ";") OldReport = Mid(.Connection, DBQStart, DBQEnd - DBQStart) DefStart = InStr(1, .Connection, sDef) + Len(sDef) DefEnd = InStr(DefStart, .Connection, ";") OldDirect = Mid(.Connection, DefStart, DefEnd - DefStart) .Connection = Left(.Connection, DBQStart - 1) _ & Replace(.Connection, OldReport, _ ReportFileName, DBQStart, 1) .Connection = Left(.Connection, DefStart - 1) _ & Replace(.Connection, OldDirect, _ DataFileDirectory, DefStart, 1) .Refresh End With -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Android" wrote in message ... I'm using thecode below (derived using Record Macro, and them modified to include file and directory name variables) to extract data from an Access database table and copy to a worksheet called "LinkedData3". It works, but it inserts the data, and so the sheet grows with every refresh. Could someone tell me how I can change the code to "replace" the full content of the sheet rather than "insert"? Regards... ================================================== ===== With Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & ReportFileName & ";DefaultDir= &" & DataFileDirectory & ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout" _ ), Array("=5;")), Destination:=Worksheets("LinkedData3").Range("A1") ) .CommandText = Array( _ "SELECT `Agent Report`.datetime, `Agent Report`.groupNumber, `Agent Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall, `Agent Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _ , _ "ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime, `Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13) & "" & Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent Report`" & Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _ , _ "t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY `Agent Report`.datetime" _ ) .Name = "Query from MS Access Database" .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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import New Database Query (Union Query) in Spreadsheet | Excel Discussion (Misc queries) | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Replace ^ in web query result | Excel Worksheet Functions | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) |