none
Excel VBA List Objects Query table format issue RRS feed

  • Question

  • Hi All,

    I am successfully using below code in excel to import data from MS Access db. The data is imported properly but data preserves the MS Access db format with filter. I only want to import data without format.

    Sub Macro23()
    Dim strFile As String
    Dim strpath As String
    
    strFile = ThisWorkbook.Path & "\" & "DB.accdb"
    strpath = ThisWorkbook.Path
    
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
            "ODBC;DSN=MS Access Database;DBQ=" & strFile & ";" & "DefaultDir=" & strpath & ";" & "DriverId=25;FIL=MS " _
            ), Array("Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _
            "$A$10")).QueryTable
            .CommandText = Array( _
            "SELECT [QUERYNAME].* FROM [QUERYNAME];")
            .ListObject.DisplayName = "Query4"
            .Refresh BackgroundQuery:=False
            .PreserveFormatting = False
        End With
    
    end sub

    Thanks,

    Zav


    • Edited by zaveri cc Wednesday, October 14, 2015 4:46 PM
    Wednesday, October 14, 2015 4:45 PM

Answers

  • Hi zaveri,

    This isn’t the original source’s format, it is the table’s default format.

    If you don’t want to use default format, you could change TableStyle property of ListObject.

    For example:

    ActiveSheet.ListObjects("Query4").TableStyle = ""

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 22, 2015 2:21 AM
    Moderator

All replies

  • What do you mean 'import data without format'?  You are simply importing data; you don't import any kind of format.  The format is set in Excel, after the data has been imported.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 14, 2015 10:01 PM
  • I am importing data in a blank sheet with no format. The data is imported with each headers in row 1 having filter and blue color background. Data starts from row 2 to row 8000 which has also light blue color background.
    Thursday, October 15, 2015 3:55 PM
  • Hi zaveri,

    This isn’t the original source’s format, it is the table’s default format.

    If you don’t want to use default format, you could change TableStyle property of ListObject.

    For example:

    ActiveSheet.ListObjects("Query4").TableStyle = ""

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 22, 2015 2:21 AM
    Moderator
  • Thanks, It worked perfectly well.
    Friday, October 23, 2015 1:20 PM