vba Excel 导入 SQL 数据，但导入后更改 1 列的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3426728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel imports SQL Data, but changes the order of 1 column once imported
提问by EKet
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
我从我在 MS Query 中创建的查询中导入数据。假设列在查询编辑器中按 A、B、C、D、E、F 的顺序排列。一旦我保存查询，返回数据到Excel，导入的数据有一个新的列顺序
A,B,C,F,D,E -- note the F column was moved where D was.
A、B、C、F、D、E——注意 F 列移动到 D 所在的位置。
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
回答by Ben McCormack
There are two properties of the QueryTableobject called PreserveColumnInfoand 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:
QueryTable对象有两个名为PreserveColumnInfoand 的属性PreserveFormatting，它们应该可以帮助您解决问题。（还有AdjustColumnWidth，但我不确定您是否需要担心那个）。您应该能够使用类似于以下内容的代码来帮助保留列信息：
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
