Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2016)
powre queryを使用してcsv取り込み
投稿日時: 20/01/27 16:01:38
投稿者: miso
メールを送信

powre queryを使用してcsvファイル取り込むマクロを組んでます。
ただVBAが組めなくても誰でも使用できるように情報をTOPシートに書き出し、その情報を参照しデータが取り込めるようにしたいです。
なんとなく配列として認識しないといけないのかなとは思うのですが、やり方がわかりません。。。
お知恵をお借りしたく宜しくお願い申し上げます。 miso
 
コメントアウトしてますが、14行目のままだと取り込むことはできるのですが、
できればheadに代入して固定ではなく流動的に表示タイトル名を付けたいです。
ループでheadに入れ込む以下のコードも試しましたがダメでした。

For i = 1 To myCol
        head = "{""""" & Cells(i + 6, "E") & """"", " & Cells(i + 6, "G") & "}, "
        myHead = myHead & head
Next

 
セル情報
・Cells(2, "A")には取り込みたいファイル名(拡張子無し)
・Cells(2, "B")には拡張子のみ
・Cells(1, "F")には取り込みたいファイルのフォルダパス
・Cells(7, "E")には表示タイトル名
・Cells(7, "G")には表示タイトルの型
 
Sub z_決済取込単体_改変()
    
    Worksheets("TOP").Select
    
    Dim OpenFileName As String, Fname As String
    OpenFileName = Cells(1, "F") & "\" & Cells(2, "A") & "." & Cells(2, "B")
    Fname = Cells(2, "A").Value

    Dim myCol, moji, head
    myCol = 8
    moji = 65001
    head = "{{""corporate"", type text}, {""order_id"", type text}, {""merchant_id"", type text}, {""store_id"", type text}, {""payment_state"", type text}, {""trans_amount"", Int64.Type}, {""payment_method"", type text}, {""update_date"", type datetime}}"

    'ActiveWorkbook.Queries.Add Name:=Fname, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Csv.Document(File.Contents(""" & OpenFileName & """),[Delimiter="","", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{""corporate"", type text}, {""order_id"", type text}, {""merchant_id"", type text}, {""store_id"", type text}, {""payment_state"", type text}, {""trans_amount"", Int64.Type}, {""payment_method"", type text}, {""update_date"", type datetime}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    変更された型"""
    ActiveWorkbook.Queries.Add Name:=Fname, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Csv.Document(File.Contents(""" & OpenFileName & """),[Delimiter="","", Columns=" & myCol & ", Encoding=" & moji & ", QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数," & head & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    変更された型"""
    
    
    ActiveWorkbook.Worksheets.Add After:=Sheets(Sheets.Count)
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Fname & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & Fname & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "buf"
        .Refresh BackgroundQuery:=False
    End With
    
     'Query Delete
        Dim wb As Workbook
        Set wb = ActiveWorkbook
    
        Dim qry As WorkbookQuery
        For Each qry In wb.Queries
            qry.Delete
        Next
        
      'テーブル解除
        With ActiveSheet.ListObjects("buf")
            .TableStyle = ""
            .Unlist
        End With
        
        ActiveSheet.Name = "決済データ"
'        Range("F:F").NumberFormatLocal = "#,##0"
        Range("H:H").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

回答
投稿日時: 20/01/27 21:15:19
投稿者: simple

回答ではありません。確認です。
 
>できればheadに代入して固定ではなく流動的に表示タイトル名を付けたいです。
ということは、
CSVファイルには項目名はないのですか?
そうではなくて、CSVファイルの一行目に項目見出しがあるとすると、
それと異なる項目名を指定して、読み出しができる、という見立てなんでしょうか?
それは実際に確認されていますか?
エラーにならないですか?

投稿日時: 20/01/27 22:49:22
投稿者: miso
メールを送信

simpleさん、
ご確認ありがとうございます。
 
csvに項目はあります
取り込む前に項目を抜き出すなんてできるのですか!?
それができたら一番の理想です。
 
>それと異なる項目名を指定して、読み出しができる、という見立てなんでしょうか?
これはエラーがでました。(涙)

回答
投稿日時: 20/01/28 00:46:18
投稿者: simple

項目名がなければ、既定の項目名(Column1,Column2,・・・)が自動付与されます。
項目名の変更に相当するクエリー(骨子は、Table.RenameColumnsです)を追加して、
任意の項目名に変更することは可能です。
(本当に)必要であれば、トライしてみて下さい。
 
というのは、そこまでするなら、CSVの項目名を変更したほうがずっと楽のように思うからです。
テキストエディターでさくっとできますから。

投稿日時: 20/01/28 11:43:05
投稿者: miso
メールを送信

simpleさん、
ご教授ありがとうございます!その考えはなかったです!!!!!
項目名を無しにして、暫定でColumn1,Column2…とし、取り込んだ後に
1行目を削除する形で理想になりそうです。
型も一旦全て文字列になりましたので、取り込んだ後に変更する形にしようと思います。
(データに20桁の数値を含んでいるので、自動で数値にされると末尾が0に変換されてしまうのでよかったー)
 
>項目名の変更に相当するクエリー(骨子は、Table.RenameColumnsです)を追加して、
任意の項目名に変更することは可能です。

毎回固定のデータなら良いのですが、
カラム数もカラム名も毎回異なり、どこに数値や日付が入ってくるかもわらなので、
できれば汎用性を高めたいと思いご相談させて頂きました。
 
本当にありがとうございました。