Constで現在日付と文字を合わせた値を変数へ代入

質問

質問時刻＝2024/03/11 22:15、タイトル名＝『vlookupを使ったマクロの書き方』で、質問させて頂き、HohoPapa様にご教示頂いたマクロのコードについて、メールアドレスが入っているファイルのシート名に変更が生じましてコードの変更が必要になりました。

修正箇所は、『Const TblSheet = "職員マスタ"』になります。
色々と修正を加えて見ましたが、エラーが出てしまいマクロの正常稼働で出来ませんでした。
変更点は、シート名＝『職員マスタ』を『employee-20240510』への変更になります。『employee-』は固定文字ですが、『20240510』は現在の日付を指定したいです。
Const TblSheet = "employee-" & Format(Now, "yyyymmdd")と
しましたが、エラーがでてしまいます。
お忙しい中、大変恐縮ですが、お手すきの時に対応させたマクロのご教示を頂けましたら大変助かります。
上記1行のマクロのコードのみ、ご教示頂ければ大丈夫です。
今現在まで、ルーチンワークとして便利に使わせて頂いております。

------以下は現在のマクロのコードです-----------------
Option Explicit
Const tgSheet = "Sheet1"
Const TblBook = "C:\Users\watan\Documents\27_EXCEL教習\TEST\11_2024-課題参画者リスト\スクリプト\職員DB.xlsx"
Const TblSheet = "職員マスタ"
Const NumCol = 2 '転記先職員番号列番号
Const AddressCol = 4 '転記先メールアドレス列番号
Dim LogFile As String
Dim tgBookName As String

Sub Mainjob()

Dim tgBook As Workbook
Dim r As Long
Dim HitAddress As String

'１つのログファイルに常に新規作成（処理の都度上書き）する。
LogFile = ThisWorkbook.Path & "\" & _
"MentLog.csv"
Open LogFile For Output As #2
Close #2

Logput "M0", "", "", "", "転記処理開始", ""

'対象ファイル名の組立
tgBookName = _
"C:\Users\watan\Documents\27_EXCEL教習\TEST\11_2024-課題参画者リスト\出力結果\" & _
Format(Now, "YYYY") & _
"参画者リストまとめ_" & _
Format(Now, "YYYYMMDD") & _
".xlsx"

'対象ブックを開く
Set tgBook = Workbooks.Open(tgBookName)

'Main処理
r = 1
With tgBook.Sheets(tgSheet)
Do
r = r + 1
If .Cells(r, NumCol).Value = "" Then Exit Do

If IsNumeric(.Cells(r, NumCol).Value) = False Then
Logput "M2", Format(r, "0"), _
.Cells(r, NumCol).Value, "", "職員番号が不正", .Cells(r, 1).Value
Else
HitAddress = GetMailAdress(.Cells(r, NumCol).Value)

If HitAddress = "Not Found" Then
Logput "M3", Format(r, "0"), _
.Cells(r, NumCol).Value, "", "職員番号が見つからない", .Cells(r, 1).Value

ElseIf ((HitAddress = "Null") Or (HitAddress = "")) Then
Logput "M4", Format(r, "0"), _
.Cells(r, NumCol).Value, "", "マスターのメールアドレスが空欄", .Cells(r, 1).Value

ElseIf .Cells(r, AddressCol).Value <> "" Then
If HitAddress <> .Cells(r, AddressCol).Value Then
Logput "M5", Format(r, "0"), _
.Cells(r, NumCol).Value, .Cells(r, AddressCol).Value, "既に異なるアドレスが埋まっている" & "," & HitAddress, .Cells(r, 1).Value
Else
Logput "M6", Format(r, "0"), _
.Cells(r, NumCol).Value, .Cells(r, AddressCol).Value, "既に同じアドレスが埋まっている", .Cells(r, 1).Value
End If

Else
.Cells(r, AddressCol).Value = HitAddress
Logput "M1", Format(r, "0"), _
.Cells(r, NumCol).Value, HitAddress, "メールアドレスをセット", .Cells(r, 1).Value
End If
End If
Loop
End With

'対象ブックを保存して閉じる
tgBook.Save
tgBook.Close
Logput "M9", "", "", "", "転記処理終了", ""

End Sub

'職員番号を引数にメールアドレスを返す関数
Function GetMailAdress(sNum As Long) As String

Dim SQL As String
Dim cn As Object
Dim rs As Object

'SQL文の実行準備
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Properties("Extended Properties") = "Excel 12.0;HDR=Yes;IMEX=1"
cn.Open TblBook

'SQL文の組立
SQL = ""
SQL = SQL & "select [メールアドレス]" & vbCrLf
SQL = SQL & "FROM [" & TblSheet & "$A1:Z50000]" & vbCrLf
SQL = SQL & "Where [職員番号] = " & sNum & vbCrLf

rs.Open SQL, cn

'ヒットする行が無かったら抜ける
If rs.EOF = True Then
GetMailAdress = "Not Found"
Else
If IsNull(rs("メールアドレス")) Then
GetMailAdress = "Null"
Else
GetMailAdress = rs("メールアドレス")
End If
End If

'後処理
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Function

回答No.2

SI299792 · ベストアンサー

Const は定数なので、数式は入れれません。

　　Const TblSheet = "employee-" & Format(Now, "yyyymmdd")
　　　　👇
　　Dim TblSheet As String
　　TblSheet = "employee-" & Format(Date, "yyyymmdd")

でできると思います。

お礼

作業ホルダーへ保存した職員DBのシート名を『職員マスタ』へ変更する事を思いつきました。シート名変更マクロを作成し、作成頂きましたマクロはそのままで無事メールアドレスを追加する事出来ましたので、これで今回のご質問は閉じます。有難うございました。

補足

質問内容に誤りがある事に今気が付きました。
大変申し訳ありません。
メール情報が入っているファイルは、毎日更新はしておりませんでした。
メール情報が追加されたと思われるタイミングで、手動でサーバから作業ホルダーへコピーしております。よって、シート名＝employee-20240510はファイルを更新した時の日付のままになります。
下記の職員DB.xlsxのシートはemployee-20240510の１つだけになります。
C:\Users\watan\Documents\27_EXCEL教習\TEST\11_2024-課題参画者リスト\スクリプト\職員DB.xlsx

メール挿入マクロはタイムスケジューラで毎日5回稼働させますが、職員DB.xlsxは不定期に更新するので、ご教示頂いた下記で実行すると正常稼働は出来ない事になる事がわかりました。
Dim TblSheet As String
TblSheet = "employee-" & Format(Date, "yyyymmdd")

現在のマクロでは、Const TblSheet = "職員マスタ"　としていますが、シート名指定の代わりに、シート1(シート名は指定せず1番目のシート)として、マクロに反映する事は可能でしょうか？
もし、それが可能でしたら、そのコードのご教示を頂けましたら大変助かります。

回答No.1

type0（@type0）

>Const TblSheet = "employee-" & Format(Now, "yyyymmdd")と
>しましたが、エラーがでてしまいます。
Constに変数は代入出来ないようですね。
Format(Now, "yyyymmdd")が変数扱いで、エラーになっていると思います。
https://vba-create.jp/vba-error-variable-not-const/

お礼

ご教示ありがとうございました。
シート名をマクロで変更し、無事メールアドレス追加が出来ました。
頂いた情報は、今回大変参考になりました。