none
Unexpected result with excel table and filter RRS feed

  • Question

  • Hi

    I have some VB code that queries a Sql Server DB and outputs the result into an excel table (excel 2016).

    If i have no filters on it inserts in the correct place- cell D16 per below.

    But if the table is already filtered it seems to start on column E instead.

    I don't mind that the row filtered wont be the same as original, as new data might now be on the same row- but why is it moving 1 column to the right?

    Any input appreciated

    David

    StrQuery = "SELECT * from Mytable"
       rst.Open StrQuery, cnn
         Sheets("Excel Sheet").Range("D16").CopyFromRecordset rst
    Set rst = Nothing
    

    Thursday, January 5, 2017 5:20 PM

All replies

  • Hi David_1234,

    I think , you did not provided the full code.

    we are unable to find the root cause from the code you had posted.

    with that code I understand that you are executing a query in to database.

    getting data in record set and assign that data to Range ("D16").

    but there is no clue why it changes the output column.

    if you provide a full code then we can try to run/debug on our side and try to solve the issue.

    if you are sure that it happens because of auto filter and don't want to post your code here then you can try to remove the autofilter from the sheet and after that you can assign data to worksheet can solve your issue.

    to remove Auto Filter just add line below in your code.

      Me.AutoFilterMode = False
    

    Reference:

    WorksheetBase.AutoFilter Property

    WorksheetBase.AutoFilterMode Property

    you can find a code example for Auto filter in VB in link above.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 6, 2017 2:58 AM
    Moderator
  • Deepak

    Full code below.

    What I have checked is that using clear rather than delete below doesn't cause a problem- however I want the table to reduce in size as the number of rows output reduces.

    When I use the delete function- it posts only to the next visible cell- not in the table.

    My first field has a unique reference- I normally hide this from the user- as they don't need to do anything with it- but I need it in case I need to update a field back in sql server as that's my link back to the original table.

    If there is any filter on (When using delete in the code) rather than posting details to B6 (1st row under headings of table)- it posts to C6- as that's visible!

    note- (I put the "X" in there in case the table was already blank- as it needs something to delete to avaoid error.

    So essentially I would like the table to reduce as the number of rows on the sql tbale reduces- and post to hidden columns and rows)

    Sub Refresh()
    
    
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim ConnectionString As String
    Dim StrQuery As String
    ConnectionString = "provider=SQLOLEDB;Data Source=My_DB_Instance;Initial Catalog=My_DB;Integrated Security=SSPI;"
    cnn.Open ConnectionString
        cnn.CommandTimeout = 360
    
    
    Application.DisplayAlerts = False
    Sheets("sheet1").Range("B6").Value = "x"
    
    Sheets("sheet1").Range("Table1").Select
        Selection.Delete 'clear
    
    Application.DisplayAlerts = True
    
    
    StrQuery = "SELECT MyTable.* FROM  MyTable"
    
       rst.Open StrQuery, cnn
         Sheets("sheet1").Range("B6").CopyFromRecordset rst
    'rst.Close StrQuery, cnn
    Set rst = Nothing
    
    
    Range("B2").Select
    
    Application.ScreenUpdating = True
    
    
    End Sub

    Monday, January 9, 2017 6:12 PM
  • Hi David_1234,

    is column B is hidden? when you try to add the value to cell B6.

    also can you tell me what is the range of table 1.

    I think that debugging the code will help to find the cause of the issue.

    I don't have db like yours so I can't test your code.

    so please try to debug it step by step.

    and after each step check the workbook.

    I think that there is something in the code that changes the output column.

    let me know about the results of your testing.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 10, 2017 5:15 AM
    Moderator
  • Yes Col B is hidden

    When I use

    Sheets("sheet1").Range("Table1").Select Selection.clear

    It updates the details to B6- so updates the hidden column

    when I use

    Sheets("sheet1").Range("Table1").Select Selection.Delete

    it updates the next "Visible" field so updates from Col C

    I want the table to grow & shrink with the changing data (rows only obviously)- but no idea why the delete performs differently- which is really unusable.

    Thanks

    D

    Tuesday, January 10, 2017 10:00 AM
  • Hi David_1234,

    when you delete the column then next column will move to left side and take place of deleted column.

    because of that you are getting this kind of output.

    please see the example below.

    you can see I have 2 columns. now I will delete column A.

    Sub demo()
    Columns(1).EntireColumn.Delete
    End Sub
    

    Output:

    you can see that Column B is now Column A.

    when you only clear the values then it will just delete the value of cells. the cells will be there. so you get correct output.

    so please take care about this and make appropriate changes to your code to work properly.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 11, 2017 5:22 AM
    Moderator