2024-01-01から1年間の記事一覧
@echo off::フォルダー内list→log化dir /b *.* > log.log ::logにあるリストのみ移動for /f %%a in (log.log) do move "%%a" %%~xaexit
[個人仕様][CodeBackColors] 4 0 0 7 6 4 4 4 0 0 0 0 0 0 0 0 [CodeForeColors] 7 0 5 0 1 2 11 2 0 0 0 0 0 0 0 0 [マトリクス仕様][CodeBackColors] 4 0 1 7 6 4 4 4 4 4 0 0 0 0 0 0 [CodeForeColors] 9 0 15 0 1 2 1 9 9 7 0 0 0 0 0 0
スケール 拡大/縮小 第一候補 第二候補拡大 A4→A3 140% 141%B5→B4A5→A4A4→B4 122% A5→B5B4→A3 114% 115%B5→A4等倍 - 100% 縮小 A4→B5 84% 87%A3→B4B5→A5 81% 82%B4→A4A4→A5 70% 71%B4→B5A3→A4
Sub CreateOriginalTableFromPivotTable() Dim pt As PivotTable Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim rngSource As Range Dim rngDestination As Range Dim srcRow As Long Dim destRow As Long ' ピボットテーブルがあるシート…
Sub 結合() Dim ws As Worksheet Dim lastRow As Long Dim dict As Object Dim key As String Dim i As Long Dim result As String ' 新しいディクショナリを作成 Set dict = CreateObject("Scripting.Dictionary") ' データがあるシートを指定 Set ws = Thi…
Sub ResetComments() Dim cmt As Comment 'すべてのコメントをループして削除する For Each cmt In ActiveSheet.Comments cmt.Delete Next cmtEnd Sub '//---------------------------------------------------------------'エクセルの行と列を入れ変えたシ…
Sub 職場名と在庫数を転記する() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim lastRow1 As Long Dim lastRow2 As Long Dim i As Long, j As Long Dim foundMatch As Boolean Dim maxStock As Long Dim maxStockLocation As String Dim secondMaxStock As…
' 現在の日付を取得Dim currentDatecurrentDate = Year(Date) & "-" & Right("0" & Month(Date), 2) & "-" & Right("0" & Day(Date), 2) ' 保存先フォルダを指定Dim saveFolderPathsaveFolderPath = "C:\Your\Custom\Folder\Path\" ' Internet Explorerのイ…
''vbs------------ ' Create Internet Explorer ObjectSet IE = CreateObject("InternetExplorer.Application") ' Set visibility to true to make it visible, false to hide itIE.Visible = True ' Navigate to the specified webpageIE.Navigate "https:/…
@echo off rem Excelを起動して指定したファイルを開くstart excel "C:\path\to\your\file.xlsx" rem 起動時にウィンドウを最大化する場合rem start /max excel "C:\path\to\your\file.xlsx" rem 起動時にウィンドウを最小化する場合rem start /min excel "C…
Sub 結合シート作成() Dim ws As Worksheet Dim combinedSheet As Worksheet Dim lastRow As Long Dim combinedRow As Long ' 新しいシートを作成して、結合先として使用します Set combinedSheet = ThisWorkbook.Sheets.Add(After:= _ ThisWorkbook.Sheets(…
Sub ExportSelectedSheetsToPDF() Dim selectedSheet As Worksheet Dim savePath As String ' PDFを保存するフォルダのパスを指定します savePath = "C:\Users\YourUsername\Documents\" ' 適切なパスに置き換えてください ' 選択したシートをPDFにエクスポ…
Option Explicit ' ドロップアウトするフォルダを指定しますConst sourceFolder = "Z:\Work\" ' 出力フォルダを指定しますConst outputFolder = "Z:\Work\" Dim objFSO, objFolder, objFileDim shell, newName, fileName ' File System Object を作成しますS…
Sub InsertTodayDate() Range("A1").Value = Format(Date, "YYYYMMDD")End Sub
Sub 罫線案()Dim st As WorksheetSet st = Worksheets("sheet1") Dim myRegion As VariantmyRegion = Range("A1").CurrentRegion Dim z, x, j As LongFor j = LBound(myRegion, 2) To UBound(myRegion, 2) z = st.Cells(Rows.Count, j).End(xlUp).rowx = st.…
Sub WriteFileNamesToSheet() ' フォルダのパスを指定 Dim folderPath As String folderPath = "Z:\Work" ' 出力シートを指定 Dim outputSheet As Worksheet Set outputSheet = ThisWorkbook.Sheets("Sheet1") ' ファイル一覧を取得 Dim objFSO As Object Se…
' フォルダのパスを指定folderPath = "Z:\Work" ' 出力ファイルの保存場所とファイル名を指定 ’desktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")outputFolderPath = "C:\Custom\Path"outputFileName = "output.txt" ' ファイル一覧を…
Option Explicit Private Tree As Worksheet, 作業sheet As Worksheet, 階層図 As WorksheetPrivate Treeの行末 As Long, 表示行 As Long Sub Tree図() Dim 行1 As Long, 行2 As Long, 行末 As Long Set Tree = Worksheets("Tree") Set 作業sheet = Workshee…
Option Explicit Private 親子 As Worksheet, 作業用 As Worksheet, 階層図 As WorksheetPrivate 親子の行末 As Long, 表示行 As Long Sub SwapColumnsAandB() Dim ws As Worksheet Dim lastRow As Long Dim temp As Variant Dim i As Long ' Set a referenc…
Sub ステータスバーに進捗表示() Dim i,r As Long r = 1000 ' 検索値 For i = 0 To r Application.StatusBar = "進捗状況:" & i & "/" & r & "(" & (i / r) * 100 & "%)" Next Application.StatusBar = False End Sub
Option Explicit ’PERSONAL.XLSBのThisWorkbookへ保存 '// Excelのイベント検知Dim WithEvents x As Application '// PERSONAL.XLSBが開いたときPrivate Sub Workbook_Open() '// Excelアプリケーションのイベントを検知する Set x = ApplicationEnd Sub '//…
Sub ステータスバー1() Dim i As Long For i = 1 To 500 Application.StatusBar = i & "回目の処理をしています..." Next i Application.StatusBar = FalseEnd Sub Sub ステータスバー2() ThisWorkbook.Worksheets("Sheet1").Activate Application.Wait [Now…
'vba起動の経過時間、関数の宣言'Declare Function GetTickCount Lib "kernel32.dll" () As Long '←32bitDeclare PtrSafe Function GetTickCount Lib "kernel32.dll" () As Long '←64bit Sub 処理時間計測()stTimer = GetTickCount '//--測定する処理------ …
Sub 可視列連番()'可視列に対して連番 Dim r1, r2 As Range Dim i As Long Set r2 = ActiveSheet.UsedRange.Columns(1) Set r2 = r2.SpecialCells(xlCellTypeVisible) i = 0 For Each r1 In r2.Cells r1.Value = i i = i + 1 Next End Sub
Sub ExecutePassThroughQuery() ’Microsoft ActiveX Data Objects x.x Library Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim connectionString As String Dim sqlQuery As String ' SQL Serverへの接続情報 connec…
’開発タブ¥Excelアドインから取り込み ’C:\Users\ユーザー名\AppData\Roaming\Microsoft\addins\自作関数.xlam Function IndexMatch(検索値 As Variant, 検索範囲 As Range, 戻り範囲 As Range) Set IndexMatch = WorksheetFunction.Index(戻り範囲, Worksh…
Option Explicit Sub ExcelからAccessデータ抽出パラメータ条件付き()'Microsoft ActiveX Data Objects 2.X LibraryとMIcrosoft ADO Ext.x.x for DDL and SecurityをVBEツールから参照設定 Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim cmd …
Sub マトリクス表をデータリストに置き換える() Dim wsInput As Worksheet Dim wsOutput As Worksheet Dim inputRange As Range Dim outputRange As Range Dim i As Long, j As Long, k As Long ' マトリクス表があるシートと範囲を指定 Set wsInput = ThisW…
Sub A列項目の種類別に、B列項目を取りまとめる() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim lastRow As Long Dim uniqueValues As Collection Dim cell As Range Dim key As Variant Dim result As String ' ソースシートと宛先シート…
Sub 各シートに新しい列を挿入して最終行までシート名を記載() Dim ws As Worksheet Dim lastRow As Long Dim newCol As Integer ' 各シートに対してループ For Each ws In Worksheets ' シートの最終行を取得 lastRow = ws.Cells(ws.Rows.Count, "A").End(x…