CODEWITHSUNDEEP
sqlexcelvba
user3149650
user3149650

Reputation: 203

running sql query from sql into excel

I have recorded a macro that basically runs sql query from within excel and pastes the data into the sheet. However, the problem is that when the data is pasted into the sheet the cells' data order is distorted. For example, I paste the sql query starting from Range("B3") but if I have got other data in say cell D4 after the query this data moves to cell F4 for no reason. The same is happening with all other data which I have got on the sheet. In other words the order of the cells data is distorted after pasting the results from the sql query. How can I resolve that?

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "ODBC;Driver=MySQL ODBC 5.1    
     Driver;SERVER=localhost;UID=root;DATABASE=second;PORT=3306" _
    , Destination:=Range("$B$4")).QueryTable
    .CommandText = Array( _
    "SELECT employees_0.eid, employees_0.ename, employees_0.age" & Chr(13) & "" & 
    Chr(10) & "FROM second.employees employees_0" _
    )
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query1"
    .Refresh BackgroundQuery:=False
    End With
   End Sub

Upvotes: 0

Views: 181

Answers (1)

sten
sten

Reputation: 380

I believe your issue is:

.RefreshStyle = xlInsertDeleteCells

Should be:

.RefreshStyle = xlOverwriteCells

You can read more about the method here.

Upvotes: 1

Related Questions