Excel 导入 SQL 数据，但导入后更改 1 列的顺序

发布于 2024-09-13 05:26:52 字数 611 浏览 3 评论 0原文

我从在 MS Query 中创建的查询导入数据。假设查询编辑器中的列按 A、B、C、D、E、F 的顺序排列。一旦我保存查询，将数据返回到 Excel，导入的数据就会具有新的列顺序

A、B、C、F、D、E - 请注意，F 列已移至 D 所在的位置。

关于如何解决这个问题有什么想法吗？

谢谢你们。假设变量定义正确，并且如果您愿意，则忽略代码试图执行的操作，保留部分不起作用

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柏林苍穹下 2024-09-20 05:27:27

QueryTable 对象有两个名为 PreserveColumnInfo 和 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

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

回复收藏 0 原文

~没有更多了~