2023年7月31日月曜日
AA
2023年7月16日日曜日
PQ
'
' 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月13日木曜日
WM
2023年7月9日日曜日
bunnkatu
rem *********************************************
rem * JOBLOG 件数出力
rem * DRAG&DROP マルチファイル対応
rem * UPDATE 2023/07/08
rem *********************************************
@echo off
cls
rem echo %1
rem echo %~0
echo ---------------------------------------------
echo ログからE件数とファイル情報を出力 Ver1.8
echo %~nx0
echo %~dp1
echo ---------------------------------------------
echo;
SET /P ANSWER="実行します。よろしいですか (Y/N)?"
if /i {%ANSWER%}=={y} (goto :yes)
if /i {%ANSWER%}=={yes} (goto :yes)
EXIT /b
:yes
FOR %%a IN (%*) DO (
cscript /nologo bin\RecSplit.vbs %%a
)
pause
endlocal
2022年6月9日木曜日
EHALLAPI
'Code Samples taken from Host Access Class Library V12.pdf manual
Sub cmdStartConnection_Click()
Set Mgr = CreateObject("PCOMM.autECLConnMgr")
Mgr.StartConnection ("profile=iseriesd connname=A")
Mgr.StartConnection ("profile=iseriesd connname=B")
End Sub
Private Sub cmdMinimized_Click()
Dim autECLWinObj As Object
Set autECLWinObj = CreateObject("PCOMM.autECLWinMetrics")
' Initialize the connection
autECLWinObj.SetConnectionByName ("A")
' For example, set the host window to minimized
autECLWinObj.Minimized = True
End Sub
Private Sub cmdSetCursorPos_Click()
Dim autECLPSObj As Object
Dim autECLConnList As Object
Set autECLPSObj = CreateObject("PCOMM.autECLPS")
Set autECLConnList = CreateObject("PCOMM.autECLConnList")
' Initialize the connection with the first in the list
autECLConnList.Refresh
autECLPSObj.SetConnectionByHandle (autECLConnList(1).Handle)
autECLPSObj.SetCursorPos 9, 53
End Sub
Private Sub cmdSendKeys_Click()
Dim autECLPSObj As Object
Dim autECLConnList As Object
Dim Row, Col As LongPtr
Set autECLPSObj = CreateObject("PCOMM.autECLPS")
Set autECLConnList = CreateObject("PCOMM.autECLConnList")
' Initialize the connection
autECLConnList.Refresh
autECLPSObj.SetConnectionByHandle (autECLConnList(1).Handle)
Row = autECLPSObj.CursorPosRow
Col = autECLPSObj.CursorPosCol
autECLPSObj.SendKeys "IBM", Row, Col
End Sub
Private Sub cmdGetText_Click()
Dim autECLPSObj As Object
Dim PSText As String
' Initialize the connection
Set autECLPSObj = CreateObject("PCOMM.autECLPS")
autECLPSObj.SetConnectionByName ("A")
PSText = autECLPSObj.GetText(19, 1, 50)
'I added message box
MsgBox "Text at location R2,C1-C50:" & vbCrLf & PSText, vbInformation
End Sub
Private Sub cmdKeyStroke_Click()
Dim NumFields As Long
Dim autECLPSObj As Object
Dim autECLConnList As Object
Dim autECLOIAObj As Object
Dim teststr As String
' Initialize the connection
Set autECLConnList = CreateObject("PCOMM.autECLConnList")
autECLConnList.Refresh
Set autECLOIAObj = CreateObject("PCOMM.autECLOIA")
autECLOIAObj.SetConnectionByHandle (autECLConnList(1).Handle)
Set autECLPSObj = CreateObject("PCOMM.autECLPS")
autECLPSObj.SetConnectionByHandle (autECLConnList(1).Handle)
autECLPSObj.SendKeys "[Enter]"
autECLOIAObj.WaitForInputReady (1000)
End Sub
Private Sub cmdSetText_Click()
Dim autECLPSObj As Object
'Initialize the connection
Set autECLPSObj = CreateObject("PCOMM.autECLPS")
autECLPSObj.SetConnectionByName ("A")
autECLPSObj.SetText "IBM", 6, 53
End Sub
2022年2月24日木曜日
dats
-- Sep0 ----- STEPA ------------------
- FILE INFO -------------------------
FILEA 1223,451 121 23331
FILEB 1223,452 122 23332
FILEC 1223,453 123 23333
CNTEND
--- Sep1 ----- STEPB ------------------
#
- FILE INFO -------------------------
FILEA 1223,454 124 23334
FILEB 1223,455 125 23335
CNTEND
#
#
fileio
Sub fileio()
'MsgBox "aaaaaa"
'WScript.Echo "TEST開始"
If TestFileCopy = 0 Then
' WScript.Echo "コピー正常"
Else
'WScript.Echo "コピー異常"
End If
WScript.Echo "TEST終了"
WScript.Quit (0)
End Sub
Function TestFileCopy()
'----------------------------------------------------
'TEST testIn.csv copy to testOut.csv
'----------------------------------------------------
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim strPathIn
Dim strPathOut
Dim fs, fr, fw
Dim intSts
strPathIn = "testIn.csv"
strPathOut = "testOut.csv"
strPathIn = "C:\Users\Forza1063\Desktop\fileio\testIn.txt"
strPathOut = "C:\Users\Forza1063\Desktop\fileio\testOut.txt"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.fileexists(strPathIn) Then
Set fr = fs.OpenTextFile(strPathIn, ForReading)
Set fw = fs.OpenTextFile(strPathOut, ForWriting, True)
i = 1
sFG = ""
Do While Not fr.AtEndOfStream
sRec = fr.ReadLine
strSearch = "Sep" ' 検索ワード
If InStr(sRec, strSearch) > 0 Then
sFG = "1"
End If
strSearch = "CNTEND"
If InStr(sRec, strSearch) > 0 Then
sFG = "0"
End If
If sFG = "1" Then
fw.WriteLine i & " " & sRec
End If
'fw.WriteLine sRec
i = i + 1
Loop
fw.Close
fr.Close
Set fw = Nothing
Set fr = Nothing
intSts = 0
Else
Call MsgBox("ファイル見つからない!", 48, "エラー")
intSts = 1
End If
Set fs = Nothing
TestFileCopy = intSts
End Function