VBAで外部ブックの空行を詰めるコードとは？

質問

添付図を見れば、「何やら SQL文を使っているな！」と思う向きもあるかと思います。が、実際は、エクセル関数で処理しています。それも、VBAでシートを読み書きしたユーザーなら多分最初に覚えるもの。ところが、当方は、先頃まではエクセルを開いたことがないド素人、いわゆるマクロを書いたのは、たったの一週間前。ですから、SQL文で該当する行や列を空にすることは出来ても、詰めるなんてことは到底に無理。と、思って、あーでもないこうでもないと思案すること３日。で、さっき、偶然に《空白行の詰め方は一行で出来る》というネット上の回答を発見。で、無事に、添付図のところまで辿り着きました。

　　strTableName = CutStr(CutStr(strSQL, "[", 2), "]", 1)
　　strSheetName = CutStr(strTableName, "$", 1)
　　strRange_S = CutStr(CutStr(strTableName, "$", 2), ":", 1)
　　strRange_E = CutStr(CutStr(strTableName, "$", 2), ":", 2)
　　'
　　'　Set文
　　'
　　Set objWorksheet = ThisWorkbook.Worksheets(strSheetName)
　　'
　　'　空白行を消す
　　'
　　objWorksheet.Range(strRange_S & ":" & strRange_E).SpecialCells(xlCe…

　が、次のステップが、さっぱり分かりません。次のステップとは、外部ブックのシートへの適用。多分、Set文のところに１２行追加するだけかと思います。

　この RowsClear() は、二つのブロックから構成されています。

Public Function RowsClear(ByVal strSQL As String, _
　　　　　　　　　　　　　Optional xlFileName As String = "", _
　　　　　　　　　　　　　Optional isHeader As Boolean = True) As Boolean

　SQL文が削除を指示している行に null を代入するブロック
　　↓
　空白行の削除するブロック

End Function

　前半は、今開いているブックのシート、外部のシートを問わずに更新できています。ここら辺のコードは、エクセルの知識に疎い私にも書けます。問題は、後半部です。

[イミディエイト]
? SQLExecute("DELETE FROM [Sheet2$A1:K11] WHERE ID>4 AND ID<7", "D:\Book2.Xlsx")
True

　これで、前半ブロックは、目的を達しています。なら、後半も出来て当然と思います。でも、そのやり方が分かりません。宜しく、ご協力の程お願いします。

回答No.3

kon555 · ベストアンサー

【補足】
ちなみに余談として
Set objWorksheet = ThisWorkbook.Worksheets～
の"ThisWorkbook."を純粋に削除してもマクロは動きます。
このようなブックの指定が無い場合、アクティブなブックの該当シート名のシートに対し働きます(該当シート名が無い場合はエラー)。
こうしたマクロの状態で、マクロ自体をクイックツールバーに登録しておくと「現在開いているシート」を対象にマクロを実行できます。
実務上はこちらの方が楽なことも多いのでご紹介しておきます。

お礼

お陰様で、何とかSQLのINSERT文、UPDATE文、DELETE文で自分あるいは指定の外部ブックを更新するSQLExecute()を書き上げることが出来ました。マイクロソフトは、Jetエンジンにエクセル固有の制限をかけています。そこで、プログラマ諸氏は、一旦、テーブルを保存して再作成するなどの手法を取ってDELETE文を実現しているようです。「ウン、なーんか違うなー・・・」と思って、今回の挑戦とあいなった次第です。「素直に、DELETE文を実行して発生した空行を詰めるべきでは」が、私の考え。で、その挑戦に自分を追い込むための質問。お付き合いありがとうございました。重ねて、お礼を申し上げて質問を締め切ります。なお、テストはしていません。後々のお楽しみにとっておきます。

回答No.2

kon555

あ、ごめんなさい勘違いしていました。既に目的の動作は達成できているんですね。それを別ブックのシートで行いたいと。
その場合は
Set objWorksheet = ThisWorkbook.Worksheets(strSheetName)

の"ThisWorkbook"の部分を目的のブックに変えてやればよいです。
その場合、
「対照ブックは元々開いているか」
「対照ブックを"ファイルを開く"形式で指定したいか」
などによって変わりますが、既にプログラム関係の知識がおありなら
http://officetanaka.net/excel/vba/file/file02.htm
の辺りを見ればすぐに組めるのではないでしょうか。

お礼

＞対照ブックは元々開いているか」
＞「対照ブックを"ファイルを開く"形式で指定したいか」
＞などによって

　この指摘にドキッ。急いで、次の関数を書きました。

Public Function BookIsOpened(ByVal wbName As String) As Boolean
On Error Resume Next
　　BookIsOpened = Len(Workbooks(wbName).Name & "") > 0
End Function

　　　　'
　　　　'　Set文
　　　　'
　　　　If Not BookIsOpened(xlFileName) Then
　　　　　　Set objWorkbook = Workbooks.Open(xlFileName)
　　　　　　Set objWorksheet = objWorkbook.Worksheets(strSheetName)
　　　　Else
　　　　　　Set objWorksheet = ThisWorkbook.Worksheets(strSheetName)
　　　　End If

　先の1行も6行に変身。テストはしていません。が、多分、この線でいけると思います。ただ、この書き方が、エレガントなそれであるかは大いに疑問。生まれた初めてのの6行ですから・・・。後は、閉じ方。それは、未経験ですのでやってみなくちゃー分かりません。が、山は、越えたようです。

　私も70歳。VBAコードを書くのは、実に23年振り。そして、エクセルの操作経験と関数の知識は完ゼロ。まあ、ともかく苦労の連続です。

＞無知なることに挑むのが一番難しい！

を痛感する日々です。

　昨晩のテストで《削除対象となった実際の列数》《削除後の行数》という課題にもぶつかりました。まあ、これなんかも、エクセルの関数で求めるなんて私には無理。で、あくまでも我流で解決。

　とにもかくにも、SQLExecute()が完成すれば、この度のエクセルへの挑戦は終了します。大変、貴重なアドバイスをありがとうございました。お礼を申し上げておきます。

補足

お礼を投稿して、「えっ！」と気が付いたんですが・・・。ELSE文は間違っていましたね。関数の冒頭で

If Len(xlFileName) = 0 Then
　　xlFileName = ThisWorkbook.FullName
End If

と、指定ブックであれThisWorkbookであれ、xlFileNameでオープンあるいは処理できるように仕掛けていたのに・・・。すっかり、それを失念していました。大分、ボケが進行しているようです。

回答No.1

kon555

ううむ・・・一旦SQL文云々は忘れて、シンプルに「エクセルで何をどうしたいのか」を書いてみて下さい。
おそらくその方面のスキルをお持ちの上で試行錯誤なさってるのだと思うのですが、エクセル上で処理するならもっとシンプルな構文や方法があると思います。

とくにエクセルVBAは「エクセルを熟知した人間ならこんな風に作業する」という考え方で組む向きもありますので、一度その段階から提示していただければ、有用な知見が集まるかと思いますが・・・。

補足

＞シンプルに「エクセルで何をどうしたいのか」を書いてみて下さい。

１、シート[顧客台帳]から[売上台帳]に過去２年間に注文がない顧客を削除する。
２、シート[売上台帳]に２年間を経過した注文記録を削除する。
３、シート[従業員名簿]から退職して２年が経過した従業員を削除する。
４、シート[営業成績]から[従業員名簿]で退職とされた従業員の記録を削除する。

　・ただし、あくまでも[テーブル]として使用している範囲の列を削除すること。

などなどです。