エクセル：VBAで自動的にCSV形式で保存について

質問

複数のシートからなるエクセルブックから、決まった一つのシートを
CSV形式で保存するマクロを作成しております。

その際、CSV化するシートは他のシートの値を参照し、数値が""、
もしくは"0"の場合は、表示しないとする数式が1000行まであります。
これは、CSVで保存した際に、無用なデータを含めないようにと
考えたものですが、下記VBAを作りCSV化し保存したファイルは、確かに
数値がある行のみ表示しているものの、データ量が重くなってしまいます。

Application.DisplayAlerts = False
Application.Dialogs(xlDialogSaveAs).Show arg1:=MyFile, arg2:=6

この作成されたCSVをあらためて、「名前を付けて保存」でCSVで保存し、
「～には、CSV(カンマ区切り)と互換性のない機能が含まれている可能性
があります。この形式で保存しますか？・・・」の所で、「はい」を選択
すると、データ量が大変少ない量で保存できます。

おそらく、マクロで作成したCSVは、その「互換性のない機能」が含まれて
いるのであろうと思いますが、必要としているのは値のデータのみです。

この「互換性のない機能」を省いてCSVで保存する方法をどなたか教えて
いただけないでしょうか？

分かりにくい文章で申し訳ありませんが宜しくお願い致します。

回答No.2

Wendy02

こんにちは。

VBAのご質問で、ある程度、VBAが分かる方だと思っていましたが、まず、基本的な誤解があるようです。今回の質問のケースは、特殊な要求を持った内容であるということを認識してほしいです。

たぶん、
ご質問の、Qno.4015635 の延長にあるものかもしれません。
「複数のエクセルファイルをアクセスにインポートし自動で一つのデータにまとめたい」

CSVの出力で、

「○○.csvには、CSV(カンマ区切り）と互換のない機能が含まれている可能性があります。この形式でブックを保存しますか？」

と出て、[いいえ]をクリックした後は、一旦、csv ファイルが出力されて、残りの書式情報などが入ったファイルは、Excelの標準ファイル形式(xls)で保存しなければなりません。

○○.csv で、「このまま保存するには、[はい]をクリック」としても、「[いいえ]をクリックして」も、○○.csv 自体には変化はありません。

CSVは、あくまでも、CSV(テキストファイル）です。互換性がないというのは、

例えば、

　　数式、書式、グラフ、オブジェクト、他のワークシートの内容

のことで、それらは、保存されないということです。

>「～には、CSV(カンマ区切り)と互換性のない機能が含まれている可能性
>があります。この形式で保存しますか？・・・」の所で、「はい」を選択
>すると、データ量が大変少ない量で保存できます。

これは、何かの間違いだと思います。CSVファイルを、そのままインポートしても、加工しなければ、行の情報までなくなるということはありえません。

次に、Accessの仕様の問題をExcelの標準的な機能で求めることは出来ません。最初に、Excelで出力をしたものを確認してください。

Accessのインポートの状態にしたい、ということでしたら、そうした仕組みをVBAで作らなくてはなりません。しかし、それよりも前に、CSVのテキストファイル出力ですから、そのテキストファイルが、どのよう出力されたらまずいのか、またはどのように出力したらよいのかは、ご質問者自身で考えて確認していただくというのが、基本だと思います。

回答者側では、仕様以外の出力に対して、空行が不要だと言えば、空行を抜くようなプログラムを考えなくてはなりません。

そうしたリクエストを明確にしていかないで、VBAで出来ないでしょうか、という質問には、Yes とも No とも回答できないのです。しかし、逆に、それは、行の情報がなくなっていますから、不可逆なものだということもご理解していただかないといけません。それを元に戻す方法はありません。

Excelでは、CSV出力では、「行のデータは改行記号で区切られる」という仕様があります。それに対して、空行は、行情報が不要だということだと思います。

ためしに、変更してみました。ただし、これは、標準的なCSVの出力とは大幅に違います。

行情報だけを落とすなら、以下の三行は不要です。右側のカンマだけの情報も落ちます。
　　　　　　　 Do While Right$(buf, 1) = ","
　　　　　　　　 buf = Mid$(buf, 1, Len(buf) - 1)
　　　　　　　 Loop

また、文字の空白情報を失っては困る場合は、
　　　　　buf = buf & "," & Trim(.Cells(i, j).Value)
　　を
　　　　　buf = buf & "," & .Cells(i, j).Value
にしてください。通常は、これらは、そのまま、以下の状態のままでよいはずです。

----------------------------------

Sub OutputCSVr()
　 '空行を抜くCSV 出力
　　Dim myFname As Variant
　　Dim Fno As Integer
　　Dim buf As String
　　Dim i As Long
　　Dim j As Long

　　myFname = Application.GetSaveAsFilename("", "テキスト ファイル (*.csv), *.csv", , _
　　"CSV出力")
　　If myFname = False Then
　　　　Exit Sub
　　ElseIf Dir(myFname) <> "" Then
　　　　If MsgBox("同じ名前のファイルがあります。上書きしますか？", vbQuestion) = vbCancel Then
　　　　　　Exit Sub
　　　　End If
　　End If

　　With ActiveSheet.UsedRange
　　　　If WorksheetFunction.Count(.Cells) = 0 Then
　　　　　　MsgBox "データが一つもありません。", vbCritical
　　　　　　Exit Sub
　　　　End If
　　　　Fno = FreeFile()
　　　　Open myFname For Output As #Fno
　 For i = 1 To .Rows.Count
　　　　　　For j = 1 To .Columns.Count
　　　　　　　　buf = buf & "," & Trim(.Cells(i, j).Value)
　　　　　　Next j
　　　　　　buf = Mid$(buf, 2)
　　　　　　If Len(Replace(buf, ",", "")) > 0 Then
　　　　　　　 Do While Right$(buf, 1) = ","
　　　　　　　　 buf = Mid$(buf, 1, Len(buf) - 1)
　　　　　　　 Loop
　　　　　　　 Print #Fno, buf
　　　　　　End If
　　　　　　buf = ""
　　 Next i
　　End With
　　Close #Fno
　　MsgBox myFname & "は出力されました。", 64, "CSV出力終了"
End Sub

回答No.1

Wendy02

こんばんは。

>「～には、CSV(カンマ区切り)と互換性のない機能が含まれている可能性があります。この形式で保存しますか？・・・」

というのは、CSVで保存すると、文字列だけで書式や数式などがないので、それ以外のものを保存しますか、と聞いてくるわけで、Yesでも、No でも、CSVファイル自体には関係ありません。

以下は、古典的なコードです。
-----------------------------------------
'標準モジュールがよい

Sub OutputCSV()
　　Dim myFname As Variant
　　Dim Fno As Integer
　　Dim buf As String
　　Dim i As Long
　　Dim j As Long

　　myFname = Application.GetSaveAsFilename("", "テキスト ファイル (*.csv), *.csv", , _
　　"CSV出力")
　　If myFname = False Then
　　　　Exit Sub
　　ElseIf Dir(myFname) <> "" Then
　　　　If MsgBox("同じ名前のファイルがあります。上書きしますか？", vbQuestion) = vbCancel Then
　　　　　　Exit Sub
　　　　End If
　　End If

　　With ActiveSheet.UsedRange
　　　　If WorksheetFunction.Count(.Cells) = 0 Then
　　　　　　MsgBox "データが一つもありません。", vbCritical
　　　　　　Exit Sub
　　　　End If
　　　　Fno = FreeFile()
　　　　Open myFname For Output As #Fno
　　　　For i = 1 To .Rows.Count
　　　　　　For j = 1 To .Columns.Count
　　　　　　　　'必要なら、ここでIf～Else ～End If構文で振り分ける
　　　　　　　　buf = buf & "," & .Cells(i, j).Value
　　　　　　Next j
　　　　　　Print #Fno, Mid$(buf, 2)
　　　　　　buf = ""
　　　　Next i
　　End With
　　Close #Fno
　　MsgBox myFname & "は出力されました。", 64, "CSV出力終了"
End Sub

補足

ご返答大変ありがとうございます。
早速いただいたVBAで試したのですが、作られるCSVファイルは容量1MB。
この作られたCSVを開き、名前を付けて保存(CSV)し、「～互換性のない～」
で「はい」で保存すると、100KBと全然容量が変わります。
なんででしょうか？さっぱり分かりません？？？

また、CSVを作成する元のエクセルシートですが、1,000行全てに下記のような
"報告書"という別シートの値を参照する数式が入っており、

=IF(報告書!G8="","",IF(報告書!G8=0,"",報告書!G8))

例えば、200行から300行の値が""、すなわち表示していないとすると、
このシートから作成したCSVをアクセスへインポートした場合、この200行
から300行は何も数値が入っていない空白の行として読み込まれます。（全部で1,000行）
しかし、先ほどのVBAで作成したCSVを再度「名前を付けて保存」で「～互換性の
ない～」で「はい」で作成したCSVを、アクセスでインポートすると、
空白行が除かれたデータ（900行）がインポートされます。。。

やはり、何かデータが含まれるものが作成されてしまうのでしょうか？？？
VBAで「～互換性のない～」で「はい」で作成される値のみのCSVは
作成できないものでしょうか？