• 締切済み
  • 困ってます

excelのファイルとセル値を書き出したい

excel2003を利用しています。 とあるフォルダにある excelファイル名(自分自身のファイルを除く) を全て書き出して、 且つ A1セルの値をB列に書き出すことを、やろうとvbaを作ってみましたが。 最後のファイルのA1セルを書きだすところで、エラーになっていまい そこだけ空白になってしまいます。※写真参照 記述は以下の通りです。どのように修正すればよいか 教えていただけないでしょうか? また他にもっと優れた記述があれば、そちらも教えて欲しいです。 よろしくお願いします。 Sub test() Dim buf As String, cnt As Long Dim Path As String Path = ThisWorkbook.Path & "\" buf = Dir(Path & "*.xls") cnt = 2 Do While buf <> "" If buf <> ThisWorkbook.Name Then cnt = cnt + 1 Cells(cnt, 1) = buf buf = Dir() Workbooks.Open Filename:=Path & "\" & buf MsgBox Workbooks(buf).Worksheets(1).Range("A1").Value Cells(cnt, 2) = Workbooks(buf).Worksheets(1).Range("A1").Value Workbooks(buf).Close End If Loop End Sub

共感・応援の気持ちを伝えよう!

  • 回答数2
  • 閲覧数304
  • ありがとう数2

みんなの回答

  • 回答No.2
  • kkkkkm
  • ベストアンサー率59% (931/1577)

buf = Dir() の場所が早すぎるのでフィル名とA1セルの内容が一個ずれていませんか? 最終までファイル名を探し終わってbufが""なのに Workbooks.Open Filename:=Path & "\" & buf とファイル名の無い構文でファイルを開こうとしてエラーになっています。 以下のように訂正してください。 Do While buf <> "" If buf <> ThisWorkbook.Name Then cnt = cnt + 1 Cells(cnt, 1) = buf Workbooks.Open Filename:=Path & "\" & buf MsgBox Workbooks(buf).Worksheets(1).Range("A1").Value Cells(cnt, 2) = Workbooks(buf).Worksheets(1).Range("A1").Value Workbooks(buf).Close End If buf = Dir() Loop

共感・感謝の気持ちを伝えよう!

質問者からのお礼

回答ありがとうございます。 まさに buf = Dir() の記述の意味を理解しておりませんでした。 勉強になりました。

関連するQ&A

  • Excel VBA インデックスが有効範囲にない

      よろしくお願いします。 Excel VBA 初心のものです。 プログラムを作ってみたのですが、 「インデックスが有効範囲にありません」となってその先に進めません。 ソースですが ------------------------------------------------------ Private Sub CommandButton1_Click() Dim buf As String, cnt As Long Dim TMP As Variant Const Path As String = "D:\Excel\sample\" buf = Dir(Path & "*.xls*") Set TMP = Workbooks(buf).Sheets("testdata").Range("A1").Value Do While buf <> "" cnt = cnt + 1 Cells(cnt, 1) = buf Cells(cnt, 2) = FileDateTime(Path & buf) Cells(cnt, 3) = TMP buf = Dir() Loop End Sub ------------------------------------------------------ エラーになる箇所は Set TMP = Workbooks(buf).Sheets("testdata").Range("A1").Value のところです。 このプログラムで何をしたいかと言いますと DドライブのExcel>sample というフォルダの中にある ・すべてのエクセルブック名(ファイル数は3個)と、 ・そのブックの作成日時と、 ・testdataというシート(各ブックに必ずあるシートです)のセルA1に入っている値 を実行ファイルのSheet1に書き出す、 というものです。 プログラムの実行ファイルはExcelフォルダ直下にあります。 どこが問題でエラーになっているのか分かりません。 ご指南よろしくお願いします。   

  • エクセルVBAで他のbookのセルcellsで参照

    エクセルVBAで他のbookのセルの値(一定の範囲)を参照したいのですが、変数を使いたいため、cellsを使用したいのですがうまくいきません。方法はないでしょうか。 下記に例を示します。 rangeを使用すればすべてok((2)(5))(この場合はset文を使用しなくてもok(5))。同じbookならcells使用ok(4)。 他のbookをcells文使用する方法はないでしょうか(もちろんできれば、Thisbookの方もcellsを使用したい)。 よろしくお願いします。 sub test() Dim ThisBook As Workbook Dim Workbook2 As Workbook 'マクロを実行しているワークブック Set ThisBook = ThisWorkbook '他のワークブック Set Workbook2 = Workbooks("test11.xlsx") ' 'ThisBook.Worksheets(1).Range("A1:B2").Value = Workbook2.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value  '(1)だめ 'ThisBook.Worksheets(1).Range("A1:B2").Value = Workbook2.Worksheets(1).Range("a1:b2").Value '(2) OK 'Workbooks("test1.xlsm").Worksheets(1).Range("A1:B2").Value = Workbooks("test11.xlsx").Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value '(3) だめ 'Workbooks("test1.xlsm").Worksheets(1).Range("A1:B2").Value = Workbooks("test1.xlsm").Worksheets(1).Range(Cells(3, 3), Cells(4, 4)).Value  '(4)だめ 'Workbooks("test1.xlsm").Worksheets(1).Range("A1:ii8000").Value = Workbooks("test11.xlsx").Worksheets(1).Range("a1:ii8000").Value  '(5) ok End Sub

  • エクセルマクロ フォルダ内のファイル検索で

    よろしくおねがいします。 下記で、どうも指定フォルダ内のファイル名を検索できていないようで 条件の"ないなら"に反応して中断するハズがファイルを開いてしまいます。 思ったのですが、bufの設定にファイル名は指定できないのでしょうか? Sub Start8() Dim buf As String, IptA As String Const Path As String = "C:\001\" IptA = Workbooks("AAA.xls").Sheets("Sheet1").Cells(1, 1).Value buf = Dir(Path & "" & IptA & ".txt") If buf = "" Then Range("A2").Select ActiveCell.FormulaR1C1 = "" & IptA & "は見つかりません" Exit Sub Else Range("A2").Select ActiveCell.FormulaR1C1 = "" & IptFN & "が見つかりました" End If Workbooks.OpenText Filename:= _ "C:\001\" & IptA & ".txt" End Sub

  • 回答No.1
  • keithin
  • ベストアンサー率66% (5278/7939)

そもそもあなたの今のマクロの結果で,各ブックに実際に記入されてるA1の値が正しく記録できてなくて,一つずつずれてることを確認して下さい。 sub macro1()  dim myPath as string ’予約語Pathを変数名に使わない  dim myFile as string  dim cnt as long  cnt = 2  mypath = thisworkbook.path & "\"  myfile = dir(mypath & "*.xls*")  do until myfile = ""   if myfile <> thisworkbook.name then    cnt = cnt + 1    cells(cnt, "A") = myfile    workbooks.open filename:=mypath & myfile ’¥は既に付けてある    cells(cnt, "B").value = workbooks(myfile).worksheets(1).range("A1").value    workbooks(myfile).close false   end if   myfile = dir() ’次は一連の仕事を終えたあとに取得  loop end sub

共感・感謝の気持ちを伝えよう!

質問者からのお礼

回答ありがとうございます。 なんとか自分で対処したかったのですが、 あともう少し、もう一歩まで、行き届きませんでした。 いつも、多くを勉強させていただき本当にありがとうございます。

関連するQ&A

  • VBAのハイパーリンクにつきまして

    以前に質問をさせていただき、こちらでベストアンサーを決定した後に急きょ変更があったところがあり、わからなくなってしまいこちらに戻ってきた次第です。 http://okwave.jp/qa/q8743521.html にて質問をさせていただきました内容について、以下のVBAで解決できております。 しかし、抽出したファイル名にハイパーリンクが欲しいという要望を受けてしまいました。 ハイパーリンクのVBAについていろいろ調べましたが、この記述方法に追加して実行する方法が全く分かりませんでした。 お分かりになる方がいましたら、この内容にハイパーリンクをつける方法をお教えいただけますでしょうか。よろしくお願いいたします。 Sub Macro1() Dim i As Long Dim myPath As String, Flnm As String ReDim Flnmfp(0) As String Dim WS1 As worksheet Set WS1=ThisWorkbook.sheets("sheet1") myPath="望みのフォルダパスを入力" Call fpFileName(myPath, Flnmfp ) 'フォルダ内のファイル名取得 If Ubound(Flnmfp)=0 Then 'フォルダにファイルが無ければ終了 Exit Sub End if For i =1 to Ubound(Flnmfp) Workbooks.open filename := Flnmfp(i) Flnm=Dir(Flnmfp(i)) With Workbooks(Flnm).sheets("sheet1") WS1.Cells(2, i).value=.Range("G5").value WS1.Cells(3, i).value=.Range("G6").value WS1.Cells(4, i).value=.Range("K7").value WS1.Cells(5, i).value=CStr(.Range("G9").value) & CStr(.Range("N9").value) & CStr(.Range("P9").value) '同じ要領で望みのセルを記入する WS1.Cells(8, i).value=Flnm End with Workbooks(Flnm).close Savechanges:=False Next i End Sub Sub fpFileName(ByVal myPath As String, ByRef Flnmfp() As String) 'サブフォルダも含め全部のxlsファイル名をフルパスで取得する   Dim cnt As Long, buf As String, f As Object   buf = Dir(myPath & "\*.xls")   Do While buf <> ""     cnt = Ubound(Flnmfp) + 1 ReDim Preserve Flnmfp(cnt)     Flnmfp(cnt)= myPath & "\" & buf     buf = Dir()   Loop   With CreateObject("Scripting.FileSystemObject")     For Each f In .GetFolder(myPath).SubFolders       Call fpFileName(f.Path, Flnmfp)     Next f   End With End Sub

  • 変数を名前に使ったシートにデータをコピーする方法

    いつもお世話になります。 hisworkbookにあるVBAから新たに開いたmyFileにデータをコピーさせようとしています。 myFileである統合.xlsにはあらかじめ該当するシートが作成されています。 myBushoとmyGroupはそれぞれセルの値を参照しています。 それを元に対応するシート名にデータのコピーをしたいのです。 当初workbooks(myFile)をactiveworkbookにしていたのですが、うまくコピーされなかったので、 ファイルパスを記述しました。 sub test() dim cnt as long dim lcnt as long dim myFile as string dim myBusho as string dim myGroup as string cnt = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row lcnt = ActiveWorkbook.Sheets(myBusho & "_" & myGroup).Cells(Rows.Count, 1).End(xlUp).Row myFile = "C:\統合.xls" myBusho = range("A1").value myGroup = range("A2").value ThisWorkbook.Sheets("Sheet1").Range(Cells(9, 1), Cells(cnt, 21)).Copy _ Workbooks(myFile).Sheet(myBusho & "_" & myGroup).Cells(lcnt + 1, 1) end sub 上記のコードではうまくコピーできませんでした。 よろしくアドバイスのほど、お願いします。

  • VBAにて複数フォルダのエクセルファイルからデータ抽出を行いたいのですが…

    現在、下記の方法で複数のブックからデータを抽出し、 一覧表示をしています。(一覧表示をしているブックを仮にAとします。) 今のままだと、同一フォルダ内のブックしか抽出されません。 これを、サブフォルダまで対象にするには、どうすれば良いのでしょうか? 簡単に例をあげると、 フォルダ(1)の中にAを入れておいて フォルダ(1)の下にあるサブフォルダ(1)、サブフォルダ(2)の中にあるブックからデータの抽出を行いたいのです。 現在つかっているVBAは Sub 抽出用() Dim FName As String Dim Folder As String Dim wb As Workbook Dim i As Integer, j As Integer Application.ScreenUpdating = False Folder = ThisWorkbook.Path & "\" i = 1: j = 1 Worksheets(1).Cells.ClearContents FName = Dir(Folder & "*.xls") Do While FName <> "" If FName <> ThisWorkbook.Name Then Workbooks.Open (Folder & FName) Workbooks(Workbooks.Count).Worksheets(5).Rows("1:1").Copy _ ThisWorkbook.Worksheets(5).Cells(i + 3, 1) Workbooks(Workbooks.Count).Close Application.StatusBar = j & "ファイル処理済み" i = i + 1: j = j + 1 End If FName = Dir() Loop Application.StatusBar = "" Application.ScreenUpdating = True MsgBox ("完了しました") End Sub です。 いいお知恵があれば、よろしくお願い致します。

  • Excel VBA 引数が2個のマクロの呼び出し方

    ExcelのVBAで、 シート上のボタンがクリックされた時に呼び出す マクロ(プロシージャ)の引数が1個の時は、 コード1のようにできましたが、 引数が2個ある時は、コード2のように記述しても、 ボタンをクリックするとエラーになりますが、 【?】の部分をどのように記述すればよいのでしょうか。 (Windows10,Excel2010) -------------------コード1---------------------------------------- Sub test1()  Dim row As Integer  Dim wave_file_path As String  For row = 1 To 2   wave_file_path = ThisWorkbook.Worksheets("Sheet1").Cells(row, 1).Value   Call ボタン作成(row, wave_file_path)  Next row End Sub Sub ボタン作成(ByVal row As Integer, ByVal wave_file_path As String)  Dim cell_loc As String  cell_loc = ThisWorkbook.Worksheets("Sheet1").Cells(row, 3).Address  ThisWorkbook.Worksheets("Sheet1").Select  With ActiveSheet.Buttons.Add(Range(cell_loc).Left, _   Range(cell_loc).Top, _   Range(cell_loc).Width, _   Range(cell_loc).Height)   .name = "ボタン_" & cell_loc   .OnAction = "'WAVE_PLAY """ & wave_file_path & "" & "'"   .Characters.Text = "再生"  End With End Sub Sub WAVE_PLAY(ByVal wave_file_path As String)  If Dir(wave_file_path) = "" Then   MsgBox wave_file_path & vbCrLf & "がありません。", vbExclamation   Exit Sub  End If  Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close " & wave_file_path End Sub ------------------------------------------------------------------- -------------------コード2---------------------------------------- Sub test2()  Dim row As Integer  Dim wave_file_path As String  For row = 1 To 2   wave_file_path = ThisWorkbook.Worksheets("Sheet1").Cells(row, 1).Value   Call ボタン作成(row, wave_file_path)  Next row End Sub Sub ボタン作成(ByVal row As Integer, ByVal wave_file_path As String)  Dim cell_loc As String  cell_loc = ThisWorkbook.Worksheets("Sheet1").Cells(row, 3).Address  ThisWorkbook.Worksheets("Sheet1").Select  With ActiveSheet.Buttons.Add(Range(cell_loc).Left, _   Range(cell_loc).Top, _   Range(cell_loc).Width, _   Range(cell_loc).Height)   .name = "ボタン_" & cell_loc   .OnAction = "'WAVE_PLAY """ & wave_file_path & "" & "," & row & "'" <==【?】   .Characters.Text = "再生"  End With End Sub Sub WAVE_PLAY(ByVal wave_file_path As String, ByVal row As Integer)  If Dir(wave_file_path) = "" Then   MsgBox wave_file_path & vbCrLf & "がありません。", vbExclamation   Exit Sub  End If  Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close " & wave_file_path  ThisWorkbook.Worksheets("Sheet1").Cells(row, 4).Value = "再生済" End Sub -------------------------------------------------------------------

  • ExcelのVBAについて

    こんにちは、VBA初心者です。 C:\pictureの中に以下のファイルがあります。 DSC_0134.JPG~DSC_0154.JPG これらのファイルをExcelのA列1~20行に書かれた文字△○%&◎~▲▽%%★に.JPGをつけて保存したくて以下のコードを書きました。 Dim buf As String Dim msg As String Dim i As Integer Dim A As Variant i = 1 buf = Dir("dsc*.jpg", vbNormal) Do While buf <> "" Do While i < 21 buf = Dir() msg = buf 'msg=元の名前 A = Worksheets("sheet1").Cells(i, 1).Value     Worksheets("sheet1").Cells(i, 2).Value = msg          Name "C:\picture\msg" As "C:\picture\A.jpg"     i = i + 1 Loop Loop Name "C:\picture\msg" As "C:\picture\A.jpg"のところで、「ファイルがありません。」となってしまいます。 あと、Worksheets("sheet1").Cells(i, 2).Value = msgのところで、\pictureの中の最初のファイル(DSC_0134.JPG)を表示しません。 どこを直せばよいのでしょうか?

  • 複数のExelbookを1シートにまとめるVBA

    Accessクエリから出力したファイルをフォルダへ格納し、Excelbookを1つのExcelへまとめています。 しかし、複数の人間がExcelへ出力する為、上書きされないよう、運用上、Accessからの出力ファイル名がExcel出力時自動的に変更されるようにいたしました。(クエリ名&日付時刻) すると、それに合わせExcelシート名も変更されてしまう為、下記のVBAが使用できなくなってしまいました。 出力されるExcelは1シートのみにデータが入っています。 フォルダ内にある全book・全シートのデータを1シートに統合、もしくは"シート名"を指定せずに複数ファイルの1シート目を1つのExcelにまとめる事は可能でしょうか? どなたかご教授をお願いいたします。 Sub Sample1() Worksheets("Sheet2").Activate Dim buf As String, i As Long Dim j buf = Dir(Sheets("sheet1").Range("A1").Value & "\*.xls") Do While buf <> "" Workbooks.Open Worksheets("sheet1").Range("A1").Value & "\" & buf Sheets("シート名").Range("A2:AL1000").Copy ThisWorkbook.Activate Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Workbooks(buf).Activate Application.CutCopyMode = False Workbooks(buf).Close SaveChanges:=False buf = Dir() Loop End Sub

  • エクセルVBAで

    登録ボタンを作りたいのですが うまくいきません。 応答無しになってしまいます。 仕事でコードを入力して、住所やその他の関連事項を 登録して、検索し、封筒に宛名印刷し、登録内容の修正をしたいと思っています。 登録ボタンは下記のようなものを作りました。 Private Sub CommandButton1_Click() Dim bk As Workbook Dim sh1 As Worksheet Dim sh2 As Worksheet Dim cnt1 As Long Set bk = ThisWorkbook Set sh1 = bk.Worksheets("現場登録検索") Set sh2 = bk.Worksheets("一覧") cnt1 = 6 Do While sh2.Cells(cnt1, 2).Value <> "" cnt = cnt1 + 1 Loop '得意先CD sh2.Cells(cnt1, 2).Value = sh1.Cells(2, 3).Value '現場CD sh2.Cells(cnt1, 3).Value = sh1.Cells(3, 3).Value '送り方 sh2.Cells(cnt1, 22).Value = sh1.Cells(4, 3).Value '封筒 sh2.Cells(cnt1, 23).Value = sh1.Cells(5, 3).Value MsgBox "登録できました。" End Sub 何が悪いのでしょうか? よろしくお願い致します。

  • ファイルが無いときにエラーメッセージを出すようにし

    フォルダ内のcsvファイルを[CSV貼り付け]というシートに インポートさせるVBAをつくったんですが、CSVファイルがないときに エラーメッセージを出すようにしたいのですがどうすればいいでしょうか。 ---------------- Sub 読み込み() Dim Bk As Workbook Dim Rw As Long, ERw As Long Const ShName = "CSV貼り付け" ' <-- 貼り付け先 PathN = ThisWorkbook.Path & " \ " Const FNCom = "" ' <-- ファイル名の先頭共通部分指定 Dim FileN As String Dim Cnt As Integer FileN = Dir(PathN & FNCom & "*.csv") ' <-- 拡張子を指定 sFileName = Dir(sCurDir & "\*.*", vbNormal) sCurDir = ThisWorkbook.Path & "\CSVファイル\" FileN = Dir(sCurDir & FNCom & "*.csv") ' <-- 拡張子を指定 Rw = 1 Application.ScreenUpdating = False Do Until FileN = "" Cnt = Cnt + 1 Set Bk = Workbooks.Open(sCurDir & FileN, ReadOnly:=True) Dim Rws As Long With ThisWorkbook.Sheets(ShName) .Cells.Clear Bk.Sheets(1).Cells.Copy .Range("a1") End With FileN = Dir Loop Bk.Close SaveChanges:=False Set Bk = Nothing Application.ScreenUpdating = True MsgBox " CSV読みこみ完了しました。", vbInformation End Sub

  • EXCEL マクロ につきまして

    お世話になっております。 以前、同様の質問をさせていただきまして、 その拡張バージョンをつくりたいと考えております。 Sub macro1() Dim myPath As String Dim myFile As String Dim c As Long Dim LastRowr As Long Application.ScreenUpdating = False myPath = ThisWorkbook.Path & "\" myFile = Dir(myPath & "*.xls") c = 6 Do Until myFile = "" If myFile <> ThisWorkbook.Name Then Workbooks.Open Filename:=myPath & myFile lastrow = Worksheets("特定のシート").Range("A65536").Row ThisWorkbook.Worksheets(2).Cells(1, c).Resize(lastrow, 1).Value = Worksheets("特定のシート").Range("H1").Resize(lastrow, 1).Value Workbooks(myFile).Close False c = c + 1 End If myFile = Dir() Loop Application.ScreenUpdating = True On Error Resume Next ActiveSheet.Name = Format(Date, "mmdd") On Error GoTo 0 End Sub こちらは"特定のシート"の特定の列(H)のみをひたすらフォーマットに 貼り付けていくものですが、コピペしたい列が増えた場合(数は一定ではない) のバージョンができればと思っております。 正直まったくわかりません。 1)データが何列あるかは不定 2)行1~3には自動的に何らかのデータが振られてしまっており(連番等)  データの終わりとして使えそうなのは、行4に「0」が入っていること  (但し0は非表示にしております) "特定のシート"名・データがH列から始まる等は変わりません。 現状の記述を改修、もしくは全とっかえでも構いません。 なにとぞよろしくお願い致します。

  • VBAで新しい日付順にファイルを検索するには?

    ExcelのVBA初心者です。 ファイルを新しい日付のものから順番に検索したいのですが、幾ら探しても分かりませんでした。どなたか教えていただけないでしょうか? やりたいことは、あるフォルダ内に毎日5&#65374;6個のファイルが保存されていくのですが、その中の決められたセルに指定した文字列が含まれているもの3つ場合分けしてファイルを出力したいのです。 例えば、  ファイル名   セルE1の内容    日付  123.xls     ”111111A”    6/29 15:39:40  456.xls     ”111111N”    6/29 15:35:10  789.xls     ”222222V”    6/29 15:20:43  654.xls     ”222222A”    6/29 14:30:21  321.xls     ”111111V”    6/29 14:10:33  951.xls     ”222222N”    6/28 17:52:15  753.xls     ”333333A”    6/28 17:30:50 とファイルがあり、セルE1に”111111”の文字列を含むファイルを検索し、  末尾に”V”があるもの → f(1)=321.xls  末尾に”N”があるもの → f(2)=456.xls  末尾に”A”があるもの → f(3)=123.xls と出力したいのです。 分からないなりに、いろいろ調べて切り貼りしながら作ってみました。 これで一応うまくいったのですが、検索する文字列は、必ず上記例のように新しい日付の5&#65374;6ファイルの中にあり、検索対象のフォルダ内には1000個以上ファイルがあります。 上記プログラムだと読み込む順番が最後になってしまいますので、恐ろしく処理時間が掛かってしまいます。 Sub ファイル検索() Dim buf As String, cnt As Long Dim i As Integer Dim wb(3) Dim bk As String, lot As String, lt As String Dim Path As String Application.ScreenUpdating = False lt = Cells(1, 5) bk = ActiveWorkbook.Name Path = Cells(1, 5) buf = Dir(Path & "*.xls") i = 1 Do While wb(1) = "" Or wb(2) = "" Or wb(3) = "" cnt = cnt + 1 Workbooks.Open Path & buf Select Case Cells(2, 5) Case Is = lt & "V" wb(1) = buf Case Is = lt & "N" wb(2) = buf Case Is = lt & "A" wb(3) = buf End Select Application.DisplayAlerts = False Workbooks(buf).Close Application.DisplayAlerts = True buf = Dir() Loop For i = 1 To 3 Workbooks(bk).Sheets(1).Cells(i, 1) = "wb(" & i & ")" & "=" & wb(i) Next i Application.ScreenUpdating = True End Sub 日付の新しいファイルから読み込む良い方法はないでしょうか? Excelのバージョンは、2003です。 出来れば、2003&#65374;2010で対応できる方法があれば、ベストです。 よろしくお願い致します。