2024-03-01から1ヶ月間の記事一覧
Private Sub ExcelExport() '変数宣言Dim filePath As String 'Excelエクスポート先のファイルパス ’filePath = "D:\保存先\" & "Export_" & Format(Date, "yymmdd") & ".xlsx" ' Excelファイルを保存するデスクトップのパスを取得 filePath = Environ("USER…
Option Explicit Sub CreateSQLReview() Dim conn As Object Dim rs As Object Dim strSQL As String Dim strOutput As String Dim i As Integer ' Access データベースへの接続 Set conn = CreateObject("ADODB.Connection") conn.Open "Provider=Microsoft…
'//ordersテーブルから顧客ごとの注文数を計算し、その注文数が3以上の顧客のみを抽出 SELECT customer_id, COUNT(*) AS order_countFROM ordersGROUP BY customer_idHAVING COUNT(*) >= 3; '//各部署の平均給与を計算し、平均給与よりも高い給与を持つ部署…
'//サブクエリ サンプル-----------In(SELECT フィールド名1 FROM Q_名称 WHERE( フィールド名2 = 条件1 And フィールド名3 = 条件2 ) OR( フィールド名2 = 条件3 And フィールド名3 = 条件4 ) OR( フィールド名2 = 条件5 And フィールド名3 = 条件6 ) OR( …
Sub Excel→Accessデータ抽出2() Dim AccessApp As Object Dim AccessDb As Object Dim AccessQuery As Object Dim AccessRecordset As Object Dim ExcelApp As Object Dim ExcelSheet As Object Dim AccessPath As String Dim i As Long ' Accessデータベー…
Sub Excel→Accessデータ抽出() Dim AccessPath As String Dim AccessQuery As String Dim ConnectionString As String Dim Conn As Object Dim RS As Object Dim ExcelApp As Object Dim ExcelSheet As Object Dim i As Long Dim paramValue1 As String Dim …
Sub 最新単価抽出() Dim wsData As Worksheet Dim wsOutput As Worksheet Dim lastRow As Long Dim partNumCol As Long, dimCol As Long, bcCol As Long, dateCol As Long, priceCol As Long Dim key As Variant Dim priceDict As Object Dim maxDateDict As…
Sub ExtractLatestPrice() Dim ws As Worksheet Dim lastRow As Long Dim partNumbers As Variant Dim uniquePartNumbers As Variant Dim partNumber As Variant Dim i As Long Dim maxDate As Date Dim latestPrice As Double Dim outputRow As Long ' デー…
Sub ExtractLatestPrices4() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lastRow As Long Dim partNumbers As Object Dim key As String Dim maxDate As Date Dim latestPrice As Double Dim newData() As Variant Dim i As Long Dim newRow …
Sub シート連番() Dim ws As Worksheet Dim i As Integer i = 1 For Each ws In ThisWorkbook.Sheets If Left(ws.Name, 2) = "最新" Then ws.Name = Left(ws.Name, 4) & Format(i, "00") i = i + 1 End If Next wsEnd Sub
Sub日付け変更 () Dim cell As Range Dim originalDate As String Dim convertedDate As String ' 変換したいセルの範囲を指定 For Each cell In Selection ' セルの値を取得 originalDate = cell.Value ' yyyymmdd形式からyyyy/mm/dd形式に変換 If Len(orig…
Sub フォルダ内の全シート1を取りまとめる() Dim フォルダパス As String Dim 対象ファイル As String Dim 対象ブック As Workbook Dim 一時ブック As Workbook Dim シート As Worksheet Dim 合成シート As Worksheet Dim 最終行 As Long Application.Screen…
Option Explicit Sub フォルダ内の全集計ファイルを取りまとめる() Dim フォルダパス As String Dim 対象ファイル As String Dim 対象ブック As Workbook Dim 一時ブック As Workbook Dim シート As Worksheet Dim 合成シート As Worksheet Dim 最終行 As Lo…
Sub ExtractDataFromAccess() Dim conn As Object ' ADO Connection Dim rs As Object ' ADO Recordset Dim strConn As String Dim strSQL As String Dim i As Integer Dim j As Integer ' Access データベースへの接続文字列を設定(IDとパスワードあり) s…
Sub HighlightLastNonEmptyCell() Dim rng As Range Dim cell As Range Dim lastNonEmptyCell As Range Dim inputRange As Range ' ユーザーにセル範囲を指定させるための InputBox を表示 On Error Resume Next Set inputRange = Application.InputBox("セ…
Function rightcellvalue(rng As Range) As Variant Dim lastCell As Range Dim ws As Worksheet ' シートをアクティブにする(指定範囲がどのシートにあるかを確認) Set ws = rng.Worksheet ws.Activate ' 指定範囲内の最後のセルを取得 Set lastCell = rn…
@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にエクスポ…