时间戳

首页 > 解决方案 > 电源查询，vba改源

excel - 电源查询，vba改源

问题描述

我有一件事有问题。我创建了一个打开选择的代码。我想制作宏的第二部分，它将 csv excel 文件（由我选择）转换为正常的正常格式。问题是，如何更改代码第二部分的源代码？我的这种方式有可能吗，如果可以，请帮助我。我刚开始学习vba。

 Sub test()
    Dim sfile As Variant
    Dim ws As Worksheet
    Dim wb As Workbook
    
    sfile = Application.GetOpenFilename
          
        Workbooks.OpenText _
           Filename:=sfile, startrow:=1, DataType:=xlDelimited, semicolon:=True, otherchar:=True, other:=True
       
 ActiveWorkbook.Queries.Add Name:="Tran", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""here i wana put choosen excel file""),[Delimiter="";"", Columns=13, Encoding=1250, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5""," & _
        " type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(#""Change Type"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Replaced Value"" = Table.ReplaceValue(" & _
        "#""Promoted Headers"",""."","","",Replacer.ReplaceText,{""AbsCharge"", ""Charge""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Replaced Value"",{{""AbsCharge"", type number}, {""Charge"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Tran;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Tran]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tran"
        .Refresh BackgroundQuery:=False
    End With

标签： excelvbacsvdatasourcepowerquery

解决方案

推荐阅读