Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
毎月File名が変わるファイルの開き方
投稿日時: 21/03/19 17:52:34
投稿者: Yuko.T

毎月【2021 03 HWI】のように年と月が変更となるFileからデータを取得したいと思っています。
会社独自のカレンダーの都合で3月のデータを4月に取得したり、3月中に取得したりで月によって替わります。毎月規則的に変わるFileの名前をマクロで自動取得する方法などご存じでしょうか?初心者のため、わかりやすくコードを説明していただけますと非常に助かります。
 
よろしくお願いいたします。

回答
投稿日時: 21/03/19 19:06:11
投稿者: WinArrow
投稿者のウェブサイトに移動

GetOpenFiename
でダイアログを開き、オペレータに検索させる
方法をお勧めします。

回答
投稿日時: 21/03/19 23:22:58
投稿者: WinArrow
投稿者のウェブサイトに移動

>毎月規則的に変わるFileの名前をマクロで自動取得する方法
 
規則性が分かれば、そのロジックを考えればよいだけです。
>月によって替わります
勿論、その条件を加味してですが・・・・
 
>月によって替わります
とだけでは、ロジックはわかりませんが・・・・

回答
投稿日時: 21/03/20 10:27:14
投稿者: MMYS

Yuko.T さんの引用:

毎月規則的に変わるFileの名前をマクロで自動取得する方法

どのような条件か知りませんが、例えば、月末・月初めに処理で、
切り替わりか毎月10日だとすると、ファイル名は下記になります。
 
2021/03/11〜04/10  ⇒  2021 03 HWI
2021/04/11〜05/10  ⇒  2021 04 HWI
2021/05/11〜06/10  ⇒  2021 05 HWI
2021/06/11〜07/10  ⇒  2021 06 HWI
2021/07/11〜08/10  ⇒  2021 07 HWI
2021/08/11〜09/10  ⇒  2021 08 HWI
2021/09/11〜10/10  ⇒  2021 09 HWI
2021/10/11〜11/10  ⇒  2021 10 HWI
2021/11/11〜12/10  ⇒  2021 11 HWI
2021/12/11〜01/10  ⇒  2021 12 HWI
2022/01/11〜01/10  ⇒  2022 01 HWI
2022/02/11〜03/10  ⇒  2022 02 HWI
 
これをコードにすると、下記になります。
 
    Dim dt       As Date
    Dim YYYY     As Long
    Dim MM       As Byte
    Dim FileName As String

    dt = "2022/1/10"
   'dt = Now

    YYYY = Year(dt)
    MM = Month(dt)

    If Day(dt) <= 10 Then
        MM = MM - 1
    End If

    dt = DateSerial(YYYY, MM, 1)
    FileName = Format(dt, "yyyy mm") & " HWI"

    MsgBox FileName

投稿日時: 21/03/22 17:20:03
投稿者: Yuko.T

WinArrow 様、MMYS 様
 
ご回答ありがとうございます。
説明が足りず申し訳ありません。月次で名前が変更となるファイルで遅くとも月初10日までには作成するものの為、MMYS様の仰るコードで書いてみようと思ったのですが、初心者過ぎて理解が足りません。現状以下のようにコードを書いてあります。
 
Workbooks.Open Filename:= _
        "https://sharepoint.com/sites/fin/Daily%20Reveue%20report/Daily%20Progress/MTD/2021%2003%20HWI.xlsm"
        Windows("2021 03 HWI.xlsm").Activate
 
こちらをそのまま、MMYS様のコードと置き換えれば良いのでしょうか?
基本的なことをお伺いして申し訳ありません。教えていただけますと助かります。

回答
投稿日時: 21/03/22 18:12:35
投稿者: WinArrow
投稿者のウェブサイトに移動

このマクロを実行する日と、開きたいブック名の関係を説明しましょう。
 
やみくもにコードを記述しても、徒労に終わることになります。
規則性をきちんと説明するようにしましょう。

回答
投稿日時: 21/03/24 13:39:20
投稿者: simple

引き続き、返事をお待ちしていますが、
思いつくままに、独り言的に、ちんたら書いて見ます。
 
4月を例にとると、4月10日には4月分のファイルができるが、
それまでは3月のファイルであると。
ただし、ルールはそうであっても、実態がずれるのは世の常ですよね。
そう考えると、
・当月名のファイルを見に行って、あればそれでOKだし、
・無いときは、前月名のものを使う、
というほうがよいのかも知れない。
 
(いや、そうじゃなくて、毎日見るものじゃないのです、
  4月のデータが出たら見るだけだから、
  4月9日に3月データなんか見ない、ということなのかどうなのか。
  そのあたりがモヤモヤしています。)
 
上記の考えに沿ったコードを参考までに提示しておきます。
ただし、上記の前提が既に違うということなら、以下は無視願います。
・getYearAndMonth で今月(ないし、前月)の文字列を取得します。
・fileExistsで、そのurlがあるかどうかをチェックします。
・test は 全体をまとめたものです。
  test が、ユーザーが実行するプロシージャです。
 

Sub test()
    Const myPath As String _
      = "https://sharepoint.com/sites/fin/Daily%20Reveue%20report/Daily%20Progress/MTD/"
    Dim myPath$, filename$, url$
    Dim wb As Workbook
    
    filename = getYearAndMonth(Date) & "%20" & "HWI.xlsm"
    url = myPath & filename
    '''Debug.Print url

    If Not fileExists(url) Then
        filename = getYearAndMonth(Date, -1) & "%20" & "HWI.xlsm"
        url = myPath & filename
    End If
    Set wb = Workbooks.Open(url)
End Sub

Function getYearAndMonth(d As Date, Optional offset As Long) As String
    Dim myYear As String
    Dim myMonth As String
    If offset = -1 Then
        d = DateAdd("m", -1, Date)
    End If
    myYear = Format(Year(d), "0000")
    myMonth = Format(Month(d), "00")
    getYearAndMonth = myYear & "%20" & myMonth
End Function

Function fileExists(url) As Boolean
    Dim xmlhttp As Object

    Set xmlhttp = CreateObject("Msxml2.XMLHTTP.6.0")
    xmlhttp.Open "HEAD", url, False
    xmlhttp.send
    fileExists = xmlhttp.Status = 200
End Function
(Sharepointを使ったテストはしていません。ご了承ください)
 
以下、スキップして頂いて構いません。
 
【補足】
1 . 上記のファイル存在を調べる方法は、urlのヘッダを読みに行っています。
  ファイルがなければ、statusコードが200以外を返すという前提です。
  もし、statusコードが200で、ファイルがありません、などという返事なら
  話は別になります。
 
2.なお、"%20"などをurlの中に直接埋め込んでいますが、
  汎用性を考えたら、URLエンコードをさせたほうがよいのかもしれない。
  (まあ、この例に限れば、英数字しか使ってはいないので影響ないですが、
  日本語なんかはどういう扱いなんだろうか。
  自分に環境がないので、このあたりもモヤモヤします。)
   
  もちろんURLエンコードのための道具もあって、
  JScriptのencodeURIComponentというのを使いますね。
  ただ、Excel2013以降であれば、ENCODEURL関数というワークシート関数が
  あるので、それを使えばよいのかもしれません。

回答
投稿日時: 21/03/24 18:20:44
投稿者: WinArrow
投稿者のウェブサイトに移動

当日などの日付から、ファイル名を特定する方法
の代案
とにかく、最新版のファイル
という考え方もあります。
 
開くファイル名を決めることは、回答者が決めることではなく、
質問者(または、使用する関係者と相談して)が決めることなので、
充分検討しましょう。
やってみて、問題があれば、再検討ということもあるので・・・・

回答
投稿日時: 21/03/24 21:19:38
投稿者: simple

私は前提を決め打ちするつもりはなく、
相当念入りに、ガード文言を入れたつもりでしたが、
足りなかったですかね。

回答
投稿日時: 21/03/24 23:48:17
投稿者: MMYS

Yuko.T さんの引用:

遅くとも月初10日までには作成するものの為、

月末に処理する。ただし間に合わない時は月始めでも良い。って事かと。
 
 

トピックに返信