Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


3514 / 13644 ツリー ←次へ | 前へ→

【61694】新しいブックを作成するVBA VBAド素人 09/5/28(木) 3:48 質問[未読]
【61695】Re:新しいブックを作成するVBA Hirofumi 09/5/28(木) 7:58 回答[未読]
【61715】Re:新しいブックを作成するVBA VBAド素人 09/5/28(木) 22:44 お礼[未読]
【61699】Re:新しいブックを作成するVBA 具頭幸憲 09/5/28(木) 11:15 回答[未読]
【61718】Re:新しいブックを作成するVBA VBAド素人 09/5/29(金) 2:19 お礼[未読]
【61717】新しいブックを作成するVBA⇒プラスα VBAド素人 09/5/28(木) 23:08 質問[未読]
【61719】Re:新しいブックを作成するVBA⇒プラスα Hirofumi 09/5/29(金) 8:17 回答[未読]
【61734】Re:新しいブックを作成するVBA⇒プラスα VBAド素人 09/5/30(土) 13:09 質問[未読]
【61738】Re:新しいブックを作成するVBA⇒プラスα Hirofumi 09/5/30(土) 17:31 回答[未読]
【61740】Re:新しいブックを作成するVBA⇒プラスα VBAド素人 09/5/30(土) 19:13 お礼[未読]

【61694】新しいブックを作成するVBA
質問  VBAド素人  - 09/5/28(木) 3:48 -

引用なし
パスワード
   ACCESSで、下記のクエリ結果を抽出させました。

   A     B      C      D
1  日付     ID    コメント     結果
2 2009/05/01  1000005  あいうえお    終了
3 2009/05/01  1000384  かきくけこ    終了
4 2009/05/01  1000295  さしすせそ    未対応
5 2009/05/02  1000608  たちつてと    終了
6 2009/05/02  1000594  なにぬねの    未対応
7 2009/05/02  1000788  はひふへほ    終了
8 2009/05/02  1001584  まみむめも    未対応

上記、抽出結果をもとに、「1行」ごとに「1つの新規ブック」を
自動作成するVBAをお教えいただけないでしょうか。

新規ブックを作成する方法は分かるのですが、上記の抽出結果では
8列までしか記載してませんが、抽出結果は何列になるか分からない為
最後の空白の列で終了するようにしたいです。

また、新規ブック名を、B列にある”日付-1〜・・・”のように
同じ日付であれば、日付のあとに-(ハイフン)2、3、4と、自動
連番でブック名(ex.20090501-1.xls)を作成したいと思っているのですが、
この場合の自動で連番をつける方法もお教えいただけないでしょうか。

色々、HPなどで検索しているのですが、どうも、上手くいかないのです。

本当に、ド素人なご質問で恐縮です。

【61695】Re:新しいブックを作成するVBA
回答  Hirofumi  - 09/5/28(木) 7:58 -

引用なし
パスワード
   こんなかな?
ただ、B列はIDで日付はA列に成っているのですが?

Option Explicit

Public Sub Sample()

  '出力するファイルの拡張子
  Const cstrExten As String = ".xls"
  
  Dim i As Long
  Dim lngRows As Long
  Dim lngColumns As Long
  Dim rngList As Range
  Dim rngHeader As Range
  Dim vntBookName As Variant
  Dim dicIndex As Object
  Dim strProm As String
  
  '◆「抽出データ」の先頭セル位置を基準とする(先頭列の列見出し「日付」のセル位置)
  Set rngList = Worksheets("Sheet1").Range("A1")
  
  '「抽出データ」の表に就いて
  With rngList
    '行数の取得
    lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
    If lngRows <= 0 Then
      strProm = "データが有りません"
      GoTo Wayout
    End If
    '列見出し列数の取得
    lngColumns = .Offset(, Columns.Count - _
            .Column).End(xlToLeft).Column - .Column + 1
    '列見出し範囲の取得
    Set rngHeader = .Resize(, lngColumns)
  End With
  
  'Dictionaryオブジェクトを取得
  Set dicIndex = CreateObject("Scripting.Dictionary")
  
  '画面更新を停止
  Application.ScreenUpdating = False
  
  'Key列に就いて繰り返し
  For i = 1 To lngRows
    'データ列数の取得
    With rngList
      lngColumns = .Offset(i, Columns.Count - _
              .Column).End(xlToLeft).Column - .Column + 1
      'B列?の値取得(日付はA列ですが?)
'      vntBookName = .Offset(i, 1).Value
      'A列の日付値取得
      vntBookName = Format(.Offset(i).Value, "yyyymmdd")
    End With
    With dicIndex
      '出力Book名の取得
      .Item(vntBookName) = .Item(vntBookName) + 1
      'Book名の作成
      vntBookName = ThisWorkbook.Path & "\" & vntBookName & "-" _
              & .Item(vntBookName) & cstrExten
    End With
    '1Book分の転記
    DataTransfer rngHeader, rngList.Offset(i).Resize(, lngColumns), vntBookName
  Next i
  
  strProm = "処理が完了しました"
   
Wayout:

  '画面更新を再開
  Application.ScreenUpdating = True
  
  Set dicIndex = Nothing
  Set rngList = Nothing
  Set rngHeader = Nothing
  
  MsgBox strProm, vbInformation
     
End Sub

Private Sub DataTransfer(rngHeader As Range, _
            rngOutput As Range, _
            vntBookName As Variant)

'  1Book分の転記

  '出力シートの出力位置
  Const cstrTop As String = "A1"
  
  Dim rngResult As Range
  
  'Bookを追加してSheetのA1を基準とします
  Set rngResult = Workbooks.Add.Worksheets(1).Range("A1")
  
  With rngResult
    '列見出しを出力
    rngHeader.Copy Destination:=.Cells(1, 1)
    'データを転記
    rngOutput.Copy Destination:=.Offset(1)
    '新規Bookを名前を付けて保存します
    Application.DisplayAlerts = False
    With .Parent.Parent
      .SaveAs Filename:=vntBookName
      'Excel2007の場合
'      .SaveAs Filename:=vntBookName, FileFormat:=xlExcel8
      .Close
    End With
    Application.DisplayAlerts = True
  End With
  
  Set rngResult = Nothing
  
End Sub

【61699】Re:新しいブックを作成するVBA
回答  具頭幸憲  - 09/5/28(木) 11:15 -

引用なし
パスワード
   VBAド素人さん

はじめまして具頭と申します(^0^)/

早速ご依頼のMacroを組んでみました♪
一応僕の環境では問題なかったので、
もしよろしければ是非試してみてください☆ミ

お互いVBAの勉強頑張りましょうネ!

※下記Macroを『標準モジュール』に貼り付けてください
-------------------------------------------------------------------------
Sub Macro1()

Dim OpenBooks As Integer
Dim myCrtPath As Variant
Dim myAns As Integer
Dim TargetPath As String
Dim i As Integer

'現在開かれているBookの数を数えます
OpenBooks = Workbooks.Count
If OpenBooks > 1 Then
MsgBox "※このBook以外の全てのBookを閉じてからMacroを実行してください", vbCritical
Exit Sub
End If

'カレントドライブを確認します
myCrtPath = CurDir("C")
myAns = MsgBox("『" & myCrtPath & "』" & vbCrLf & _
"に新規Bookが保存されますがよろしいですか?", vbYesNo, "Macro実行")

'カレントドライブを変更します
If myAns = vbNo Then
TargetPath = Application.InputBox _
("空欄にご希望の保存先Pathを入力してください", "保存先を入力", _
 Type:=2)
Select Case TargetPath
  Case ""
  MsgBox "処理を中断します"
  Exit Sub
  Case False
  MsgBox "処理を中断します"
  Exit Sub
  Case Else
  ChDir Path:=TargetPath
End Select
End If

'ステートメントが記載されるBook名を変更します
BookName = Application.InputBox _
("ご希望のBook名を入力してください", "保存先を入力", _
 Default:="Test", Type:=2)
Select Case BookName
  Case ""
  MsgBox "処理を中断します"
  Exit Sub
  Case False
  MsgBox "処理を中断します"
  Exit Sub
  Case Else
  ActiveWorkbook.SaveAs Filename:=BookName
End Select

i = 1
Range("A2").Select

Application.ScreenUpdating = False

'ループ処理開始
Do While ActiveCell <> ""
If ActiveCell = ActiveCell.Offset(1) Then
 '新しいBookを作成
 Workbooks.Add
  With ActiveSheet
  .Cells(1, 1).Value = "日付"
  .Cells(1, 2).Value = "ID"
  .Cells(1, 3).Value = "コメント"
  .Cells(1, 4).Value = "結果"
  .Cells(2, 1).Select
  End With
  '新しいBookへコピペ
  Workbooks(BookName).Activate
  ActiveCell.EntireRow.Select
  Selection.Copy
  '新しいBookへ貼り付け
  Workbooks(2).Activate
  ActiveSheet.Paste
  '新しいBookの名前を変更
  ActiveWorkbook.SaveAs Filename:= _
  Format(Cells(2, 1).Value, "yyyy-mm-dd" & "-" & i)
  ActiveWorkbook.Close
  i = i + 1
 ActiveCell.Offset(1).Select
End If

'ActiveCellとActiveCellの上の値は同一だが、 _
ActiveCellとActiveCellの下の値が違う場合
If ActiveCell = ActiveCell.Offset(-1) Then
 If ActiveCell <> ActiveCell.Offset(1) Then
 '新しいBookを作成
  Workbooks.Add
  With ActiveSheet
   .Cells(1, 1).Value = "日付"
   .Cells(1, 2).Value = "ID"
   .Cells(1, 3).Value = "コメント"
   .Cells(1, 4).Value = "結果"
   .Cells(2, 1).Select
  End With
  '新しいBookへ2行目以降をコピペ
   Workbooks(BookName).Activate
   ActiveCell.EntireRow.Select
   Selection.Copy
  '新しいBookへ貼り付け
   Workbooks(2).Activate
   ActiveSheet.Paste
  '新しいBookの名前を変更
   ActiveWorkbook.SaveAs Filename:= _
   Format(Cells(2, 1).Value, "yyyy-mm-dd" & "-" & i)
   ActiveWorkbook.Close
   ActiveCell.Offset(1).Select
  'このケースは一度しか出現しないので、変数iを1に戻す
   i = 1
 End If
End If

'ActiveCellとActiveCellの上下の値が違う場合
If ActiveCell <> ActiveCell.Offset(-1) Then
 If ActiveCell <> ActiveCell.Offset(1) Then
 '新しいBookを作成
  Workbooks.Add
  With ActiveSheet
   .Cells(1, 1).Value = "日付"
   .Cells(1, 2).Value = "ID"
   .Cells(1, 3).Value = "コメント"
   .Cells(1, 4).Value = "結果"
   .Cells(2, 1).Select
  End With
  '新しいBookへ2行目以降をコピペ
   Workbooks(BookName).Activate
   ActiveCell.EntireRow.Select
   Selection.Copy
  '新しいBookへ貼り付け
   Workbooks(2).Activate
   ActiveSheet.Paste
  '新しいBookの名前を変更
   ActiveWorkbook.SaveAs Filename:= _
   Format(Cells(2, 1).Value, "yyyy-mm-dd")
   ActiveWorkbook.Close
   ActiveCell.Offset(1).Select
  'このケースは一度しか出現しないので、変数iを1に戻す
   i = 1
 End If
End If
Loop

Application.ScreenUpdating = True

Workbooks(BookName).Sheets(1).Range("A1").Select
MsgBox "処理が完了しました"

End Sub
-------------------------------------------------------------------------

【61715】Re:新しいブックを作成するVBA
お礼  VBAド素人  - 09/5/28(木) 22:44 -

引用なし
パスワード
   ▼Hirofumi さん:
>こんなかな?
>ただ、B列はIDで日付はA列に成っているのですが?
>
ありがとうございました!!
やりたい操作が、そのままできました。

大変、参考になりました!

【61717】新しいブックを作成するVBA⇒プラスα
質問  VBAド素人  - 09/5/28(木) 23:08 -

引用なし
パスワード
   申し訳ございません。もう一つお教えいただきたいことがあります。

今回、新しいブックにセルの内容を新規作成する方法を質問したのですが
「新しいブック=既定のフォーマット」があり、ACCESSで抽出をした
クエリ結果の内容を、この既定のフォーマットにデータを貼り付けていきたいと
考えています。

↓抽出したクエリ結果(.xls)
   A     B      C      D
1  日付     ID    コメント     結果
2 2009/05/01  1000005  あいうえお    終了
3 2009/05/01  1000384  かきくけこ    終了
4 2009/05/01  1000295  さしすせそ    未対応
5 2009/05/02  1000608  たちつてと    終了
6 2009/05/02  1000594  なにぬねの    未対応
7 2009/05/02  1000788  はひふへほ    終了
8 2009/05/02  1001584  まみむめも    未対応

上記の抽出結果を踏まえて、新しいWorkbookを作成----

'↓2列目の新規Workbook
   A     B      C      D
1  日付   2009/05/01   
2  ID    1000005
3 コメント         あいうえお
4  結果                  終了

'↓3列目の新規Workbook
   A     B      C      D
1  日付   2009/05/01   
2  ID    1000384
3 コメント         かきくけこ
4  結果                  終了

'↓4列目の新規Workbook
   A     B      C      D
1  日付   2009/05/01   
2  ID    1000295
3 コメント         さしすせそ
4  結果                  未対応

Loop------------

新しいWorkbookには、既に既定のフォーマットがありヘッダーやフッダーが
決まっています。
その既定のフォーマットに、新しいWorkbookとして、1つずつ上記のように
決まったセルに、貼り付けをしたいと思っています。
1つのBookに、新しくシートを作成していく方法は分かるのですが、1Bookずつ
を、既定のフォーマットで決まったセル範囲に貼り付ける方法があれば、
ぜひ、お教えいただけないでしょうか。

既定のフォーマットの形を変えずに、新規WorkBookとして作成して、該当の
セル位置に貼り付けができるものでしょうか。

本当に本当にすいません。。。

やりたい事は自分が一番分かっているのですが、「既定のフォーマット」を
新規Workbookとして作成するとなると、????になってしまいます。

どなたか、ご教示いただけたら幸いです。

【61718】Re:新しいブックを作成するVBA
お礼  VBAド素人  - 09/5/29(金) 2:19 -

引用なし
パスワード
   ▼具頭幸憲 さん:
>VBAド素人さん
>
>はじめまして具頭と申します(^0^)/
>
>早速ご依頼のMacroを組んでみました♪
>一応僕の環境では問題なかったので、
>もしよろしければ是非試してみてください☆ミ
想像していたもの以上に、きめ細かい操作指示があり
とても勉強になりました。

本当にありがとうございました!!

【61719】Re:新しいブックを作成するVBA⇒プラスα
回答  Hirofumi  - 09/5/29(金) 8:17 -

引用なし
パスワード
   「VBAド素人さん - 09/5/28(木) 3:48 -」の書き込みで

>新規ブックを作成する方法は分かるのですが、上記の抽出結果では
>8 列までしか記載してませんが、抽出結果は何列になるか分からない為
>最後の空白の列で終了するようにしたいです。

「列」と「行」の言い方が間違っているのでは無いですか?
此れによって、コードが違うのですが?
今回は、上記が違っているとして書きます
また、「既定のフォーマット」在るシートは、
「既定のフォーマット」のBookの先頭シートとします

尚、「既定のフォーマット」のシートには、セルの書式設定も予め設定して置いて下さい

Option Explicit

Public Sub Sample_2()

  '◆「元リスト」のデータ列数(A列〜D列)
  Const clngColumns As Long = 4
  '出力するファイルの拡張子
  Const cstrExten As String = ".xls"
    
  Dim i As Long
  Dim lngRows As Long
  Dim rngList As Range
  Dim strModel As String
  Dim vntPos As Variant
  Dim vntItem As Variant
  Dim vntList As Variant
  Dim vntBookName As Variant
  Dim dicIndex As Object
  Dim strProm As String
  
  '◆「元リスト」の先頭セル位置を基準とする(先頭列の列見出し「日付」のセル位置)
  Set rngList = Worksheets("Sheet1").Range("A1")

  '「既定のフォーマット」のBook名
  strModel = ThisWorkbook.Path & "\" & "既定のフォーマット.xls"
  If Dir(strModel) = "" Then
    strProm = "既定のフォーマットのBookが有りません"
    GoTo Wayout
  End If
  
  '「既定のフォーマット」書き込み位置
  '日付、ID、メント、 結果の順で
  vntPos = Array("B1", "B2", "C3", "D4")
  '上記に対応する「元リスト」の列位置(A列を基準とする列Offset)
  vntItem = Array(0, 1, 2, 3)
  
  '「元リスト」の表に就いて
  With rngList
    '行数の取得
    lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
    If lngRows <= 0 Then
      strProm = "データが有りません"
      GoTo Wayout
    End If
  End With
  
  'Dictionaryオブジェクトを取得
  Set dicIndex = CreateObject("Scripting.Dictionary")
    
  '画面更新を停止
  Application.ScreenUpdating = False
  
  'Key列に就いて繰り返し
  For i = 1 To lngRows
    'データ列数の取得
    With rngList
      '「元リスト」1行分データを配列に取得
      vntList = .Offset(i).Resize(, clngColumns)
      'B列?の値取得(日付はA列ですが?)
'      vntBookName = vntList(1, 2)
      'A列の日付値取得
      vntBookName = Format(vntList(1, 1), "yyyymmdd")
    End With
    With dicIndex
      '出力Book名の取得
      .Item(vntBookName) = .Item(vntBookName) + 1
      'Book名の作成
      vntBookName = ThisWorkbook.Path & "\" & vntBookName & "-" _
              & .Item(vntBookName) & cstrExten
      '「既定のフォーマット」BookのCopy
      FileCopy strModel, vntBookName
    End With
    '1Book分の転記
    DataTransfer vntPos, vntItem, vntList, vntBookName
  Next i
  
  strProm = "処理が完了しました"
   
Wayout:

  '画面更新を再開
  Application.ScreenUpdating = True
  
  Set dicIndex = Nothing
  Set rngList = Nothing
  
  MsgBox strProm, vbInformation
     
End Sub

Private Sub DataTransfer(vntPos As Variant, _
            vntItem As Variant, _
            vntList As Variant, _
            vntBookName As Variant)

'  1Book分の転記

  Dim i As Long
  Dim rngResult As Range
  
  '転記するBookをOpenし、先頭シートのA1を基準セルとします
  Set rngResult = Workbooks.Open(Filename:=vntBookName) _
            .Worksheets(1).Range("A1")
  
  With rngResult
    'データを転記
    For i = 0 To UBound(vntPos)
      .Range(vntPos(i)).Value = vntList(1, vntItem(i) + 1)
    Next i
    '転記するBookの変更を保存します
    .Parent.Parent.Close SaveChanges:=True
  End With
  
  Set rngResult = Nothing
  
End Sub

【61734】Re:新しいブックを作成するVBA⇒プラスα
質問  VBAド素人  - 09/5/30(土) 13:09 -

引用なし
パスワード
   ▼Hirofumi さん:
「列」と「行」の言い方が間違っているのでは無いですか?
⇒大変失礼しました。言い方が間違えておりました。

ありがとうございます。

このマクロで実行すると、既存のフォーマットが複数枚作成
できるのですが、すべての既存フォーマットに元ファイルの
データ全部が貼り付けされてしまいます。

お教えいただいたVBAの中で、どこをどう直したら良いのか分からず
たびたび恐縮ですが、いま一度、お教えいただけないでしょうか。

【61738】Re:新しいブックを作成するVBA⇒プラスα
回答  Hirofumi  - 09/5/30(土) 17:31 -

引用なし
パスワード
   >このマクロで実行すると、既存のフォーマットが複数枚作成
>できるのですが、すべての既存フォーマットに元ファイルの
>データ全部が貼り付けされてしまいます。
>
>お教えいただいたVBAの中で、どこをどう直したら良いのか分からず
>たびたび恐縮ですが、いま一度、お教えいただけないでしょうか。

どのような現象か理解出来ないのですが?

此方の意図しているコードの内容は、

  '「既定のフォーマット」のBook名
  strModel = ThisWorkbook.Path & "\" & "既定のフォーマット.xls"

で指定しているBookを、データ1行に対して、1つ名前を代えてファイルコピーし
コピーしたBookの先頭シートの指定された位置にデータを代入していますが?

此方で試している部分では、上手く行ってると思います
何が違って居るのか詳しく説明して下さい

尚、今考えられるのは、

1、"既定のフォーマット.xls"指定そのものが違っているのでは
 "既定のフォーマット.xls"とは、
 「既に既定のフォーマットがありヘッダーやフッダーが決まっています。」のシートが在るBookです
2、"既定のフォーマット.xls"のBookそのものをCopyしている為、
 データが既に入った何枚ものシートが有ればそのまま残ってしまいます
 詰まり、"既定のフォーマット.xls"で指定したBookには、
 空のフォーマットのシートが先頭に1枚在ることを想定して居ます
 (特にシート自体は何枚在っても善いのですが、対象としているシートは先頭の1枚だけです)

【61740】Re:新しいブックを作成するVBA⇒プラスα
お礼  VBAド素人  - 09/5/30(土) 19:13 -

引用なし
パスワード
   本当に、もうしわけございません。

>尚、今考えられるのは、
>
>1、"既定のフォーマット.xls"指定そのものが違っているのでは
> "既定のフォーマット.xls"とは、
> 「既に既定のフォーマットがありヘッダーやフッダーが決まっています。」のシートが在るBookです

ご指摘の通り、ブックの指定そのものが間違っていました。
再度、指定のブック名を正しくして実行してみたら、思っていた通りの
実行ができました!!!

本当に、つたない文章ながら、ご丁寧にありがとうございました!!!!!

3514 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free