エクセルマクロでファイルを開いて保存したいです。

質問

エクセルマクロでファイルを開いて保存したいです。

エクセルファイルでファイル名「編集」という物を開きます。
このSheet1には中央にコマンドボタンが作成して有ります。
このボタンをクリックして以下の作業をマクロで行いたいです。

１．エクセル標準の「ファイルを開く」のメッセージボックスが開く

２．ここは使用者が作業をしてもらう
　　マイドキュメントだったり、マイネットワークだったり、
　　（ファイルの種類はCSVにする）
　　処理をしたいCSVファイルを探してもらい選択後、開くをクリック
　　例えば20100922.CSVを選択し開くをクリック

３．クリックと同時にそのファイルが展開されて、「編集」のエクセルファイルの
　　Ｓheetにシート名「集計」が作成されそのシートにCSVファイルの全内容がセルA1から貼り付く。
　　さらに登録してあるマクロモジュールで編集処理がされ
　　マクロを引き継がず、シート「集計」だけを
　　ファイル名は固定でそのファイル名の後ろに作成日（システム日付）を入れて
　　参照したCSVファイルの保管場所に保存する。
　　ファイル名例：売上20100923．xls（売上は固定）

４．エクセル「編集」のファイルからシート「集計」を削除する。
５．メッセージＢＯＸで「編集終了」と表示

次回エクセルファイル「編集」を開くと、コマンドボタンだけである。
また作成された「売上20100923」はマクロがないから開くときに
マクロのメッセージは出ない。というようにしたいです。

ＮＯ．２のファイルを選択する作業は作業者にしてもらいますが
シート１のコマンドボタン１回を押すだけでＮＯ．１～ＮＯ．５まで完結させたいです。　

NO.3のマクロ処理はマクロの記録でモジュールができています。

このマクロの作成方法と、そのマクロが出来たら
その文のどこに作成済みの処理文を入れればいいのかわかりません。
よろしくお願いします。

ここでつまづいています。この後この選択したファイルの全内容が
シートの集計に展開されません。

Private Sub CommandButton1_Click()
Call 集計
End Sub

Sub 集計()
FullPath = Application.GetOpenFilename("CSV,*.CSV")
If FullPath <> "False" Then
Sheets("Sheet1").Select
Sheets.Add
ActiveSheet.Name = "集計"
With ActiveSheet.QueryTables.Add(Connection:="CSV;" & FullPath, Destination:=Range("A1"))

　　マクロの記録で作成したマクロ

　　シート"集計"をマクロを引きつかずファイル名売上＆システム日付で保存

　　シート"集計"を削除する

回答No.1

utun01 · ベストアンサー

この要件なら新しくworkbookを作ってそちらで編集した方が簡単ですよ。
また、worksheet、workbookを跨ぐソースを記述する場合は
それぞれ使用するオブジェクトを変数に格納しないとバグの元になります。
（SheetsやActiveSheetなど）
マクロの記録はあくまで雛形作成用にした方がいいですよ。

それはともかく、
こんな感じでどうでしょうか。

Sub Macro1()
Dim fName As String
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet
Dim buf As String
Dim rng As Range
Dim i As Integer
Dim fLine As Variant
Dim fso As Object
Dim dPath As String

fName = Application.GetOpenFilename( _
FileFilter:="CSVファイル(*.csv),*.csv" _
, FilterIndex:=1 _
, Title:="CSVファイルを選択してください" _
, MultiSelect:=False)

Application.ScreenUpdating = False
Application.Calculation = xlManual

Set fso = CreateObject("Scripting.FileSystemObject")
dPath = fso.GetParentFolderName(fName)

Application.SheetsInNewWorkbook = 1
Set wBook = Workbooks.Add
Set wSheet = wBook.Worksheets(1)
wSheet.Name = "集計"
Set rng = wSheet.Range("A1")

Dim n As Variant
n = FreeFile
Open fName For Input As #n

i = 0
Do Until EOF(n)
Line Input #n, buf
fLine = Split(buf, ",")
rng.Offset(i).Resize(, UBound(fLine) + 1).Value = fLine
i = i + 1
Loop
Close #n

'ここにメイン処理（集計処理？）を入れてください。
'「rng」がworkbooks("編集").worksheets("集計").range("A1")になっています。

'FileFormatはExcel2007で2003以前に対応したファイルを指定するものです。
'状況に応じて変更してください。
wBook.SaveAs Filename:=dPath & "\売上" & Year(Date) & Month(Date) & Day(Date) & ".xls", _
FileFormat:=XlFileFormat.xlExcel8
wBook.Close

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
MsgBox "編集終了"
End Sub

お礼

回答と、補足に対する質問にも回答くださいまして
ありがとうございました。
また補足をしました。申し訳ありません。
よろしくお願いします。

補足

どうもありがとうございます。思っていたようにできました。

一つ教えていただきたいのですが
該当ＣＳＶファイルをクリックしてエクセルで開いた時と、
この教えていただいたマクロで完成されたエクセルファイルで書式が相違します。

このＣＳＶファイルを直接開いた時には１は１なのですが
このマクロで完成保存されたファイルは１が00001と表示で
「数値が文字列として保存されています」
とセルの左上に緑のマークがあります。

該当ＣＳＶファイルを直接クリックしてエクセルで開いてもこうなっていません。

編集用のマクロが記述されたエクセル１を開きます。
該当ＣＳＶファイルをクリックして新たなエクセル２が開きます。
この新たなエクセル２のシートからデータ部分を全選択でコピーします。
先ほどのエクセル１のシート１にコピーします。
エクセル１に記述されたマクロで編集をします。
この状態でも上記の緑マークの状態のセルはありません　
マクロで編集したあとにマクロ込みでエクセル１を名前を変えて保存をし、
ＣＳＶファイルを開いたエクセル２は閉じます。
その後その保存したファイルをクリックしエクセルで開いても
緑マークのあるセルはひとつもありません。

これは何が影響しているのでしょうか？
よろしくお願いします。

回答No.5

utun01

すいません。
ちょっと忙しくて確認していませんでした。

ActiveSheetはまずいですね。
私のソースでは、特にSelectメソッドでの操作は行っていないので、
しっかりとオブジェクトを特定して指定する必要があります。
最初にも少し触れたと思いますが、ActiveSheetやActiveCell等の曖昧なオブジェクトは極力使用しないほうが良いです。
手軽ではありますが、ちょっとしたことで動作が変わり、バグの元になります。
今回のソースでは、プログラム処理中のExcelの動きを止めているので、その可能性は低くなりますがね。

ActiveSheetは「編集()」に引数としてwSheetを渡し、wSheetのインスタンスをRangeオブジェクトに「編集()」内で格納し、そのオブジェクトをActiveSheetの代わりに使えば良いはずです。

ともあれ、ご解決なさったようでよかったです。

お礼

ありがとうございました。

　Sub　自動編集　()　　

　---教えていただいた記述

　---Call　編集()　私の記述

　---教えていただいた記述

で思ったように動いています。

1が00001と表示も直りました。

Callで呼び出す私の記述内で１５種類の編集のうち1種類だけ処理されない件

→Function ConvertNumbersをやめてSelect Case Trueで対応
　し１５種類の編集が可能になりました。

＞ActiveSheetは「編集()」に引数としてwSheetを渡し、
＞wSheetのインスタンスをRangeオブジェクトに「編集()」内で格納し、
＞そのオブジェクトをActiveSheetの代わりに使えば良いはずです。

全然わかりません。申し訳ないです。

どうもありがとうございます。

回答No.4

utun01

No.2補足に関する回答です。
おそらくですが、編集のブックではR1C1参照形式のオプションが
設定されているのではないでしょうか。

私のご提示したマクロでは新規ブックにて処理を行っておりますので、
>.Formula = "=ConvertNumbers(RC[-1])"
の記述は
.FormulaR1C1 = "=ConvertNumbers(RC[-1])"
でないと正常に処理されないかもしれません。

正確なマクロ内容が分からないので
これで確実とは言えませんが
試して見る価値はあるかと思います。

また、VBAについてですが
Excelの参照形式自体に二通りの参照形式があります。
例１：=Sheet1!A1
例２：=Sheet1!RC
前者がいわゆるA1形式、後者がR1C1形式ですね。
R1C1形式は相対座標の参照が簡単にできて便利ですが
使う場合は明確に区別するといいかもしれません。

そういう意味では私の提示した一文も本来
wSheet.UsedRange.Formula = wSheet.UsedRange.Value
であるべきですね。
混乱させてしまったら申し訳ありません。

お礼

With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
.Value = .Value
End With
End With

---省略---

End Sub
----------------------------------------------　　
Function ConvertNumbers(ByVal arg As Variant) As String

---省略---

End Select
ConvertNumbers = buf
End Function

これは同じBOOK内でないと駄目なようです。
新規BOOKを開いてそこにCSVファイルをインポートしているので
処理されないみたいです。
どうもありがとうございました。

補足

アドバイスありがとうございます。2010年9月28日17:00

.Formula = "=ConvertNumbers(RC[-1])"
↓
.FormulaR1C1 = "=ConvertNumbers(RC[-1])"

wSheet.UsedRange.Formula = wSheet.UsedRange.Value
↓
wSheet.UsedRange.Formula = wSheet.UsedRange.Value

試しましたが駄目でした。

編集する為のプロシージャー内で15種類の編集作業を行います。
14種類は編集してくれますがますが以下の部分だけ駄目です。
もしかして
　With ActiveSheet
が原因でしょうか？
Function ConvertNumbersは同じブック内なら呼び出せるみたいです。
今回教えていただいた物は新規ブックで編集を行いますのでFunction ConvertNumbersを
呼び出せないのでしょうか？
データーファイルに直接このプロシージャーを登録してF8で動かしていくと
.Formula = "=ConvertNumbers(RC[-1])"の次は
Function ConvertNumbersからEnd Functionの間でループして
データがなくなると
.Value = .Value
に移動します。
ところが教えていただいた物を同じくF8で動作を見ると
BOOK2が開きその中でどんどん編集がされていき
With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
までキチンと編集しますが問題の
.Formula = "=ConvertNumbers(RC[-1])"の次は
.Value = .Value
に移動してしまいFunction ConvertNumbersには行きません。
よってFunction ConvertNumbersの編集がされないまま
次の編集作業が進み名前を付けて保存までいきます。

Sub 編集()

---省略---

With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
.Value = .Value
End With
End With

---省略---

End Sub
----------------------------------------------　　
Function ConvertNumbers(ByVal arg As Variant) As String

---省略---

End Select
ConvertNumbers = buf
End Function

回答No.3

utun01

すいません
何が影響しているかとかまったく答えていませんでしたね。

このソースではRangeオブジェクトに値を入れる際、
おそらくはCollectionクラスをそのまま放り込んでいます。
Collectionクラスの各要素はVariant型なので、
Excel側でそれが数値なのか文字なのかを判断できません。
結果として、Range.Valueに入るデータ型はStringに統一されている？ようです。

たぶんですがこんな感じですね。
ちなみに今回追加した一行は、
使用している全てのセルのリスト.数値入力用ボックス = 使用している全てのセルのリスト.中身
といった感じです。

お礼

説明までありがとうございます。
ここでいろいろな方に教わると、
その文章や単語をｗｅｂで検索しながら
内容を調べて覚えてます。
だからこの文を使うのか、とかこういう場合にこれを使うのかなど、
参考書やwebでの検索では限界があります。
記述だけ教えてもらい、そのまま貼り付けて完成では
今後に生かす事ができませんので。
このように説明までしていただけると大変助かります。
ありがとうございました。

補足

アドバイスありがとうございます。2010年9月27日16:12

.FormulaR1C1 = "=ConvertNumbers(RC[-1])"
wSheet.UsedRange.Formula = wSheet.UsedRange.Value

駄目でした。

編集する為のプロシージャー内で15種類の編集作業を行います。
14種類は編集してくれますがますが以下の部分だけ駄目です。
もしかして
　With ActiveSheet
が原因でしょうか？
Function ConvertNumbersは同じブック内なら呼び出せるみたいです。
今回教えていただいた物は新規ブックで編集を行いますのでFunction ConvertNumbersを
呼び出せないのでしょうか？
データーファイルに直接このプロシージャーを登録してF8で動かしていくと
.Formula = "=ConvertNumbers(RC[-1])"の次は
Function ConvertNumbersからEnd Functionの間でループして
データがなくなると
.Value = .Value
に移動します。
ところが教えていただいた物を同じくF8で動作を見ると
BOOK2が開きその中でどんどん編集がされていき
With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
までキチンと編集しますが問題の
.Formula = "=ConvertNumbers(RC[-1])"の次は
.Value = .Value
に移動してしまいFunction ConvertNumbersには行きません。
よってFunction ConvertNumbersの編集がされないまま
次の編集作業が進み名前を付けて保存までいきます。

Sub 編集()

---省略---

With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
.Value = .Value
End With
End With

---省略---

End Sub
----------------------------------------------　　
Function ConvertNumbers(ByVal arg As Variant) As String

---省略---

End Select
ConvertNumbers = buf
End Function

回答No.2

utun01

No.1です。
・
・
・
Close #n
'********以下の一行を追加する*********
wSheet.UsedRange.FormulaR1C1 = wSheet.UsedRange.Value

'ここにメイン処理（集計処理？）を入れてください。
'「rng」がworkbooks("編集").worksheets("集計").range("A1")になっています。
・
・
・
前回ご提示したソースにこの1行を加えればいけると思います。
もしもできなければ、集計処理の途中でまた文字列になっている可能性があるので
集計処理が終わった後にこの1行を移動させてみてください。

お礼

ありがとうございます。
１行加えたら、補足した内容（手動で行った時の状態）
になって保存されていました。

補足

wSheet.UsedRange.FormulaR1C1 = wSheet.UsedRange.Value

でうまくできました。
手動でやった時と同じ状態で保存されています。
ありがとうございます。

ただ、
＞'ここにメイン処理（集計処理？）を入れてください。

ここにCall　編集

と入れて、Sub　編集()を呼び出すようにしました。

このSub　編集()ではかなりの編集をしています。
ですがこの中の一部の編集だけができません。
このSub　編集()を単独で起動時には問題がありません。

Sub　編集()の中には

Sub　編集()
----省略----
.Formula = "=ConvertNumbers(RC[-1])"
.Value = .Value
----省略----
End　Sub
Function ConvertNumbers
---省略----
End Function

が含まれています。編集できないのはこの部分です。
Callがいけないのでしょうか？
Function ConvertNumbersがいけないのでしょうか？