エクセルマクロでデータを取り込む

エクセルマクロを使って、保存されている別のエクセルを開けたり、CSVファイルを開けずに取り込む方法です

フォルダに保存されているエクセルを開ける/閉じる

Workbooks.Open "フォルダのフルパス&ファイル名"

「開ける」は基本的にはこれだけです。フルパスとは、プロパティなどで確認できるそのファイルの置き場所を指します。

Workbooks.Close "フォルダのフルパス&ファイル名"　
SaveChanges:=False　’保存せずに

「閉める」のはこれだけです。単純に「閉める」だけを行うと、変更内容を保存しますか？のウィンドウが出てしまうので、「保存せずに」のコードを入れています。保存するときは

orkbooks.Close "フォルダのフルパス&ファイル名"　
SaveChanges:=True　’保存して

このようになります。

デスクトップにあるファイルを開ける/閉じる

デスクトップのパスはそれぞれのPCで固有のものなので、様々な人が使えるような設計にするには工夫が要ります。パスがPCごとに異なるということです。

Dim Path As String, WSH As Variant　’変数の宣言
   Set WSH = CreateObject("Wscript.Shell")　'オブジェクトの生成
   Path = WSH.SpecialFolders("Desktop") & "\"　'デスクトップpathの取得
Filename = Dir(Path & "*らーめん" & "*.xlsx")　’デスクトップにある、名前にらーめんを含む最初のエクセルの名前を返す
Workbooks.Open (Path & Filename)　’セットしたエクセルを開ける

変数、というものが唐突に出てきますがここでは詳しい説明を省略します。感覚で言うと、方程式を解くときの「○○をxとする」と同じようなものです。上で言うと「pathを今このエクセルがあるデスクトップのパスとする」みたいなことですね。
今回は、エクセルに含まれる名前を*で挟むことで中間一致検索しています。名前がズバリ決まっているのであれば、

Filename = Dir(Path & "らーめん.xlsx")

とすればよいでしょう。
また、上記のコードでは、pathとFilenameを別々に扱っています。Filenameにはpathの情報は入っていません。
特にpathとfilenameを分ける必要がない。ということであれば、以下のような書き方も可能です。だた、個人的にはこの後の「閉める」作業を考えると分けるべきだと思っています。

Dim Path As String, WSH As Variant　FName As String　’変数の宣言
   Set WSH = CreateObject("Wscript.Shell")　'オブジェクト生成
   Path = WSH.SpecialFolders("Desktop") & "\"　'デスクトップpathの取得
   Filename = Path & "つけ麺.xlsx"　'パスと名前をセット
   Workbooks.Open (Filename)　'エクセルを開ける

この場合、Filenameという変数にpath情報も入っています。

次に「閉める」を考えます。Filenameにpath情報を入れずに、分けている場合、Filenameには文字通りエクセルの名前しか入っていません。

Workbooks(Filename).Close SaveChanges:=False　'保存せずに閉じる

ということで上のように書けばいいわけです。
もしFilenameにpath情報も入っている場合、このコードはエラーがでます。path情報も入っている場合、一例ですが

Windows("つけ麺.xlsx").Activate
ActiveWindow.Close SaveChanges:=False

こうすると同じ結果になります。ただし、名前がズバリ指定できるときに限ります。

以上のようなことを考えると、Filenameにはpath情報入れない方がよいというのが私見です。

フォルダにある最新のエクセルを開ける

Dim FileTime As Date
Dim MaxTime As Date
Dim FileName    As String
Dim MaxFileName As String

With CreateObject("WScript.Shell") 'カレントフォルダを指定
   
   .CurrentDirectory = "フォルダのフルパス"
   
End With

FileName = Dir("*.xlsx") 'ワイルドで拡張子「xlsx」ファイルを取得
    
Do While FileName <> "" 'ファイルを取得出来なくなるまでループ

   FileTime = FileDateTime(FileName) '取得したファイルの日時を取得
   
   If FileTime > MaxTime Then '取得している最新時間と比較
   
       MaxTime = FileTime '日付が大きい時、MaxTimeに置き換え
       MaxFileName = FileName '日付が大きい時、MaxFilenameに置き換え
       
   End If
   
   FileName = Dir()
   
Loop

Workbooks.Open MaxFileName, UpdateLinks:=False '最終的に最新日時のファイルをリンク更新なしで開く

フォルダのフルパスだけ書き換えてしまえばすぐに使えます。これも同様にMaxFilenameにはエクセル名しか入っていませんが、カレントフォルダ(作業場のようなもの)を事前に指定することで、pathの記述を省略しています。pathに当たるところは全部このカレントフォルダのパスにするよ、というこどです。
閉めることは全く同じなので省略。

開けたエクセルを動かす

Windows(Filename).Activate　'Filenameのエクセルをアクティブにする
Sheets("sheet1").Select　'Filenameのsheet1を選ぶ

ThisWorkbook.Activate　'マクロを搭載しているこのエクセルをアクティブにする

この2構文だけ覚えておけば、問題なく動かせます。Filenameのエクセルをアクティブにして、コピー、ThisWorkbookをアクティブにしてペーストがよく行う作業になると思います。

CSVデータを取り込む

CSVデータは開ける必要すらありません。クエリーテーブルを使用して取り込みを行います。

Dim WSH As Variant
Dim FSO As Object
Dim Path As String
Set WSH = CreateObject("Wscript.Shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
Path = WSH.SpecialFolders("Desktop") & "\"
FSO.CopyFile "フォルダのフルパス&ファイル名", Path
Set FSO = Nothing

'csvをインポート
    Range("A5").Select　
   With ActiveSheet.QueryTables.Add(Connection:= _
       "TEXT;" & Path & "ファイル名", Destination:=Range _
       ("$AA$5"))　'スタート位置
       .FieldNames = True
       .AdjustColumnWidth = False
       .TextFilePlatform = 932
       .TextFileTabDelimiter = True
       .TextFileCommaDelimiter = True
       .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
       .Refresh BackgroundQuery:=False
   End With
   Columns("AA:AY").Select　
   Selection.QueryTable.Delete　’クエリーテーブルを消す

.TextFilePlatformは注意です。CSVの文字コードが異なると文字化けを起こしたりします。
hift_JIS：932　UTF-8：65001　UTF-16：1200
となります。
正直、このCSV取り込みは詳しくないので、これ以上書くとボロがでます。この辺にしておきます。

次回は作業したエクセルの保存方法です。