Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a query in Excel using VBA giving an error.

    I am creating a query in Excel 2007 using below codes giving an error "Run-time error '1004': A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table."

    I want to replace the current data based on the value received from the input box.


    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
    Dim FileName As String
    Dim FilePath As String
    Dim Location As String
    
    FileName = Application.ActiveWorkbook.FullName
    FilePath = Application.ActiveWorkbook.Path
    Location = InputBox("Location: ")
    
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
            "ODBC;DSN=Excel Files;DBQ=" & FileName & ";DefaultDir=" & FilePath & ";DriverId=1046;Ma" _
            ), Array("xBufferSize=2048;PageTimeout=5;")), Destination:=Range("'Sheet3'!$A$1")). _
            QueryTable
            .CommandText = Array( _
            "SELECT `Lists$`.Location, `Lists$`.`Customer Name`" & Chr(13) & "" & Chr(10) & "FROM `Lists$` `Lists$`" & Chr(13) & "" & Chr(10) & "WHERE (`Lists$`.Location=" & Location & ")" & Chr(13) & "" & Chr(10) & "ORDER BY `Lists$`.`Customer Name`" _
            )
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Customer_List"
            .Refresh BackgroundQuery:=False '                         THIS LINE IS ALSO GIVING AN ERROR "Application-defined or object-defined error.
        End With
    End Sub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,630
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Sanju,

    Although it may not be the solution and a long shot, I would first try the most obvious and change the destination to an unused location or another sheet in the line:

    from

    Destination:=Range("'Sheet3'!$A$1"))

    to

    Destination:=Range("'Sheet3'!$Z$1"))

    As I am not familiar with an SQL statement in the query, out of curiosity, are you querying Access, another database, or SQL server?

    Maud

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you already have a querytable in place, you should simply alter its CommandText, not add a new one.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,630
    Thanks
    114
    Thanked 644 Times in 588 Posts
    If still at a lost, try adding the code line (in blue)
    Code:
    
    FileName = Application.ActiveWorkbook.FullName
    FilePath = Application.ActiveWorkbook.Path
    Location = InputBox("Location: ")
    ActiveSheet.ListObjects.Item(1).Delete
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
            "ODBC;DSN=Excel Files;DBQ=" & FileName & ";DefaultDir=" & FilePath & ";DriverId=1046;Ma" _
            ), Array("xBufferSize=2048;PageTimeout=5;")), Destination:=Range("'Sheet3'!$A$1")). _
            QueryTable
            .CommandText = Array( _ ..............
    
    

  5. #5
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear Maud

    I am using Excel-2007

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,630
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Sanju,

    I figured you were using Excel. I was more curious of what external sources your data was coming from.

    Maud

  7. #7
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud

    Now the line ".Refresh BackgroundQuery:=False" is giving error "Application defined or object defined error".

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •