C# VBA 年月日フォルダ自動生成(会員数保存) 作りかけ

少し試作を作り始めたが、方向性が変わったため。

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 会員数保存
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            this.comboBox1.Items.Add("2017");
            this.comboBox1.Items.Add("2018");

            for (int i = 1; i <= 12; i++ )
            {
                this.comboBox2.Items.Add(
                    string.Format("{0:00}",i)
                    );
            }

            this.comboBox1.Text = DateTime.Today.ToString("yyyy");
            this.comboBox2.Text = DateTime.Today.ToString("MM");

            this.button1.Click += new EventHandler(MakeFolder);

        }

        private void MakeFolder(object sender, EventArgs e)
        {
            try
            {
                DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");

                if(System.IO.File.Exists(Application.StartupPath + @"\dat.txt"))
                {
                    MessageBox.Show("既にdat.txtが存在します。中止します。");
                    return;
                }

                string[] directories = System.IO.Directory.GetDirectories(Application.StartupPath);

                if (directories.Length > 0)
                {
                    MessageBox.Show("既に同一階層にフォルダが存在します。中止します。");
                    return;
                }

                string targetPath = Application.StartupPath + "/" + comboBox1.Text + comboBox2.Text;
                System.IO.Directory.CreateDirectory(targetPath);

                DateTime FromDate = DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");
                DateTime ToDate = FromDate.AddMonths(1);
                ToDate = ToDate.AddDays(-1);

                while(FromDate <= ToDate)
                {
                    System.IO.Directory.CreateDirectory(targetPath + "/" + FromDate.ToString("dd"));
                    FromDate = FromDate.AddDays(1);
                }

                using(System.IO.StreamWriter sw = new System.IO.StreamWriter(
                    Application.StartupPath + @"\dat.txt",false,System.Text.Encoding.GetEncoding("Shift_Jis")))
                {
                    sw.Write(comboBox1.Text + "\r\n" + comboBox2.Text + "\r\n" + ToDate.ToString("dd"));
                    sw.Close();
                }

            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
                return;
            }

            MessageBox.Show("フォルダを作成しました。");

        }
    }
}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

namespace 会員数保存

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.comboBox1.Items.Add("2017");

this.comboBox1.Items.Add("2018");

for (int i = 1; i <= 12; i++ )

{

this.comboBox2.Items.Add(

string.Format("{0:00}",i)

);

}

this.comboBox1.Text = DateTime.Today.ToString("yyyy");

this.comboBox2.Text = DateTime.Today.ToString("MM");

this.button1.Click += new EventHandler(MakeFolder);

}

private void MakeFolder(object sender, EventArgs e)

{

try

{

DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");

if(System.IO.File.Exists(Application.StartupPath + @"\dat.txt"))

{

MessageBox.Show("既にdat.txtが存在します。中止します。");

return;

}

string[] directories = System.IO.Directory.GetDirectories(Application.StartupPath);

if (directories.Length > 0)

{

MessageBox.Show("既に同一階層にフォルダが存在します。中止します。");

return;

}

string targetPath = Application.StartupPath + "/" + comboBox1.Text + comboBox2.Text;

System.IO.Directory.CreateDirectory(targetPath);

DateTime FromDate = DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");

DateTime ToDate = FromDate.AddMonths(1);

ToDate = ToDate.AddDays(-1);

while(FromDate <= ToDate)

{

System.IO.Directory.CreateDirectory(targetPath + "/" + FromDate.ToString("dd"));

FromDate = FromDate.AddDays(1);

}

using(System.IO.StreamWriter sw = new System.IO.StreamWriter(

Application.StartupPath + @"\dat.txt",false,System.Text.Encoding.GetEncoding("Shift_Jis")))

{

sw.Write(comboBox1.Text + "\r\n" + comboBox2.Text + "\r\n" + ToDate.ToString("dd"));

sw.Close();

}

catch (Exception exp)

{

MessageBox.Show(exp.Message);

return;

}

MessageBox.Show("フォルダを作成しました。");

}

VBA

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

With Application.CommandBars("cell").Controls.Add
.OnAction = "main"
.Caption = "会員数をフォルダから取得"
End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete
Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

End Sub

Sub main()

On Error GoTo e

targetPath = ThisWorkbook.Path & "\dat.txt"

If Dir(targetPath) = "" Then

  MsgBox "dat.txtが存在しません。終了します。"
  Exit Sub

End If

Open targetPath For Input As #1
  Line Input #1, y
  Line Input #1, m
  Line Input #1, d
Close #1

For i = 1 To d

  targetPath = ThisWorkbook.Path & "/" & y & m & "/" & Format(i, "00")
  
  If Dir(targetPath) = "" Then
  
    MsgBox targetPath & "が存在しません。終了します。"
    Exit Sub
  
  End If
  
  ActiveSheet.Cells(i + 1, 1).Value = y & "/" & m & "/" & Format(i, "00")
  'ActiveSheet.Cells(i + 1, 1).Font.Color = RGB(255, 0, 0)
  'ActiveSheet.Cells(i + 1, 2).Value = ActiveSheet.Cells(i, 2).Value
  'ActiveSheet.Cells(i + 1, 3).Value = ActiveSheet.Cells(i, 3).Value

Next i

Exit Sub

e:

MsgBox "エラーが発生しています。"

End Sub

Sub initialize()

ActiveSheet.Cells.Delete
ActiveSheet.Columns("A").NumberFormatLocal = "yyyy/mm/dd"
ActiveSheet.Cells(1, 1).Value = "日付"
ActiveSheet.Cells(1, 2).Value = "新規"
ActiveSheet.Cells(1, 3).Value = "更新"

End Sub

Sub finalize()

ActiveSheet.Columns.AutoFit

End Sub

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "main"

.Caption = "会員数をフォルダから取得"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

End Sub

Sub main()

On Error GoTo e

targetPath = ThisWorkbook.Path & "\dat.txt"

If Dir(targetPath) = "" Then

MsgBox "dat.txtが存在しません。終了します。"

Exit Sub

End If

Open targetPath For Input As #1

Line Input #1, y

Line Input #1, m

Line Input #1, d

Close #1

For i = 1 To d

targetPath = ThisWorkbook.Path & "/" & y & m & "/" & Format(i, "00")

If Dir(targetPath) = "" Then

MsgBox targetPath & "が存在しません。終了します。"

Exit Sub

End If

ActiveSheet.Cells(i + 1, 1).Value = y & "/" & m & "/" & Format(i, "00")

'ActiveSheet.Cells(i + 1, 1).Font.Color = RGB(255, 0, 0)

'ActiveSheet.Cells(i + 1, 2).Value = ActiveSheet.Cells(i, 2).Value

'ActiveSheet.Cells(i + 1, 3).Value = ActiveSheet.Cells(i, 3).Value

Next i

Exit Sub

MsgBox "エラーが発生しています。"

End Sub

Sub initialize()

ActiveSheet.Cells.Delete

ActiveSheet.Columns("A").NumberFormatLocal = "yyyy/mm/dd"

ActiveSheet.Cells(1, 1).Value = "日付"

ActiveSheet.Cells(1, 2).Value = "新規"

ActiveSheet.Cells(1, 3).Value = "更新"

End Sub

Sub finalize()

ActiveSheet.Columns.AutoFit

End Sub