Sirius Forza1063: PQ

Sub Macro1()
'
' Macro1 Macro
' https://excel-ubara.com/excelvba4/EXCEL_VBA_408.html
'https://officeforest.org/wp/2022/10/03/vba%E3%81%A7power-query%E3%81%AE%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E4%BD%9C%E6%88%90%E3%83%BB%E5%89%8A%E9%99%A4%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95/
'
'クエリをすべて削除する
'ワークシートに接続
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim qry As WorkbookQuery

For Each qry In wb.Queries
qry.Delete
Next

'---------------------------------

Dim sSRC As String

sSRC = "C:\Users\forza1063\Desktop\WMREPOマン\QB7.html"

ActiveWorkbook.Queries.Add Name:="Table 0 (2)", _
Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" ソース = Web.Page(File.Contents(""C:\Users\forza1063\Desktop\WMREPOマン\QB7.html""))," & Chr(13) & "" & Chr(10) & _
" Data0 = ソース{0}[Data]," & Chr(13) & "" & Chr(10) & _
" 変更された型 = Table.TransformColumnTypes(Data0,{{""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt2"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt"", type text}, {""C" & _
":\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
""

'ActiveWorkbook.Queries.Add Name:="Table 0 (2)", _
' Formula:= _
' "let" & Chr(13) & "" & Chr(10) & _
' " ソース = Web.Page(File.Contents(""C:\Users\forza1063\Desktop\WMREPOマン\QB7.html""))," & Chr(13) & "" & Chr(10) & _
' " Data0 = ソース{0}[Data]," & Chr(13) & "" & Chr(10) & _
' " 変更された型 = Table.TransformColumnTypes(Data0,{{""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt2"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt"", type text}, {""C" & _
' ":\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
' ""

'ActiveWorkbook.Queries.Add Name:="Table 0 (2)", _
'Formula:= _
'"let" & Chr(13) & "" & Chr(10) & _
'" ソース = Web.Page(File.Contents(""C:\Users\forza1063\Desktop\WMREPOマン\QB7.html""))," & Chr(13) & "" & Chr(10) & _
'" Data0 = ソース{0}[Data]," & Chr(13) & "" & Chr(10) & _
'" 変更された型 = Table.TransformColumnTypes(Data0,{{""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt2"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt"", type text}, {""C" & _
'":\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
'""
'ActiveWorkbook.Queries.Add Name:="Table 0 (2)", Formula:= _
' "let" & Chr(13) & "" & Chr(10) & " ソース = Web.Page(File.Contents(""C:\Users\forza1063\Desktop\WMREPOマン\QB7.html""))," & Chr(13) & "" & Chr(10) & " Data0 = ソース{0}[Data]," & Chr(13) & "" & Chr(10) & " 変更された型 = Table.TransformColumnTypes(Data0,{{""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\NEW\QB7.txt2"", type text}, {""C:\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt"", type text}, {""C" & _
' ":\Users\forza1063\Desktop\WMREPOマン\GEN\QB7.txt2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (2)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 0 (2)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
' .ListObject.DisplayName = "テーブル_Table_0__2"
.Refresh BackgroundQuery:=False
End With
Sheets("Table 0 (2)").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("Table 0 (2)").Select
ActiveSheet.ListObjects("テーブル_Table_0__2").ShowAutoFilterDropDown = False
ActiveSheet.ListObjects("テーブル_Table_0__2").ShowAutoFilterDropDown = True
ActiveSheet.ListObjects("テーブル_Table_0__2").ShowAutoFilterDropDown = False
ActiveSheet.ListObjects("テーブル_Table_0__2").TableStyle = ""
Range("B18").Select
End Sub

2023年7月16日日曜日

PQ