Excel导入SQL数据，但一旦导入，更改1列的顺序 [英] Excel imports SQL Data, but changes the order of 1 column once imported

问题描述

我从MS Query中创建的查询中导入数据。
让我们说这些列在查询编辑器中是按顺序A，B，C，D，E，F。
一旦我保存查询，返回数据到Excel，导入的数据有一个新的列顺序

A，B，C，F，D，E - 请注意，F列移动到D的位置。

有关如何解决此问题的任何想法？

 对于每个wks在ThisWorkbook.Worksheets 
 
设置qt = wks.QueryTables（1）
 qt.PreserveColumnInfo = True 
 qt.PreserveFormatting = True 
 
 
如果wks.Name<> Master和wks.Name<> 参数然后
 
 
 wks.Range（A2：A1000）。EntireRow.Copy目的地：=工作表（主）。范围（A65536）。 ）.Offset（1，0）
 
 
 
 
结束如果
下一个wks 
  

解决方案

有 QueryTable 对象的两个属性称为 PreserveColumnInfo 和 PreserveFormatting ，这应该可以帮助您。 （还有 AdjustColumnWidth ，但我不知道你是否需要担心那个）。您应该可以使用类似于以下代码来帮助保留列信息：

  Sub PreserveQueryTable（）
 Dim ws As Worksheet 
 Dim qt As QueryTable 
 
设置ws = ActiveSheet 
 
设置qt = ws.QueryTables（0）'< ==我不确定如果这应该是一个'
'0或1.我认为这是一个0.'
 
 qt.PreserveColumnInfo = True 
 qt.PreserveFormatting = True 
 
 End Sub 
  

I do an import data from a query I created in MS Query. Lets say the columns are in order A,B,C,D,E,F in the Query Editor. Once the I save the query, return data to Excel, the imported data has a new column order

A,B,C,F,D,E -- note the F column was moved where D was.

Any ideas on how to solve this issue?

Thanks guys. Assume variables are defined correctly and disregard what the code is trying to do if you want, the preserving part is not working

For Each wks In ThisWorkbook.Worksheets

  Set qt = wks.QueryTables(1)
  qt.PreserveColumnInfo = True
  qt.PreserveFormatting = True


        If wks.Name <> "Master" And wks.Name <> "Parameters" Then


        wks.Range("A2:A1000").EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)




        End If
    Next wks

解决方案

There are two properties of the QueryTable object called PreserveColumnInfo and PreserveFormatting, which should help you out. (There's also AdjustColumnWidth, but I'm not sure if you need to worry about that one). You should be able to use code similar to the following to help preserve the column information:

Sub PreserveQueryTable()
    Dim ws As Worksheet
    Dim qt As QueryTable

    Set ws = ActiveSheet

    Set qt = ws.QueryTables(0) ' <== I am not sure if this should be a     '
                               '     0 or a 1. I think it is a 0.          '

    qt.PreserveColumnInfo = True
    qt.PreserveFormatting = True

End Sub

这篇关于Excel导入SQL数据，但一旦导入，更改1列的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！