Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable with Copy/Paste Functions
The following code is part of an Excel 2000 VBA module. The routine Copy_And_Query is called to copy the format template in the Range(“A1:AH50”) The Cell(“C61:Hxx) values are magically obtained from the Query function where xx is = RecordCount + 60 of the SQL selection. All of this works great except for the fact that Row 61 always loses the format properties obtained from Row11, while Rows 62 through 100 remain like rows 12 through 50 regardless of the Row count returned from the SQL selection. Queue Paso???? Regards, Hudel Thanks you much if you have any understanding of this. Sub Copy_And_Query() Dim lCnt as Long Dim xSheet as Excel.WorkSheet Dim xBook as Excel.WorkBook Dim sSql as String Dim sCopyTo as String Dim sCopyFrom as String Set xBook = ActiveWorkBook Set xSheet = .Sheets(“Report”) sCopyTo = "A51" sCopyFrom = "A1:AH50" With xSheet ..Range(sRefer).Copy ..Range(sCopyTo).PasteSpecial xlPasteAll End With ‘xSheet SSql = “Select Col3, Col4, Col5, Col6, Col7, Col8 “ _ & “From My_Table “ _ & “Where Col1=’Y’ and Col210” LCnt = Query_Run(xSheet, sSql, “C11”) End Sub Function Query_Run _ (xSheet As Excel.Worksheet, _ sSql As String, _ sRange As String) _ As Long Dim rsQuery As ADODB.Recordset Dim qt As QueryTable On Error GoTo Query_Run_Error With xSheet Set rsQuery = Get_Rs(sSql, Query_Run) ‘Get AdoDB RecortSet _ where Query_Run is set to the Record Count Set qt = .QueryTables.Add(rsQuery, .Range(sRange)) With qt ..Name = "Path Import" ..FieldNames = False ..RowNumbers = False ..FillAdjacentFormulas = False ..PreserveFormatting = True ..RefreshOnFileOpen = False ..BackgroundQuery = True ..RefreshStyle = xlOverwriteCells ..SavePassword = False ..SaveData = True ..AdjustColumnWidth = False ..RefreshPeriod = 0 ..WebFormatting = xlWebFormattingAll ..WebPreFormattedTextToColumns = True ..WebConsecutiveDelimitersAsOne = True ..WebSingleBlockTextImport = False ..WebDisableDateRecognition = False ..Refresh BackgroundQuery:=False End With 'qt End With Exit Function Query_Run_Error: vX = Err.Description Resume Next End Function ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cut/Copy and Paste Functions without old links | Excel Worksheet Functions | |||
Copy/Paste of QueryTable doesn't adjust relative parameters | Excel Discussion (Misc queries) | |||
Format Problems using Copy/Paste with QueryTable | Excel Programming | |||
Using Copy/Paste and QueryTable Operations | Excel Programming | |||
Using VBA Copy/Paste and QueryTable | Excel Programming |