EXCEL VBA で、RecordSet を使いまわしする

みなさん、こんにちは。


今回も先日に続き EXCEL VBA についてです。

シートのデータを RecordSet に取り込んで利用されている方も多いかと思います。

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic

'何かRecordSetを操作する処理を行う
dbRes.MoveFirst
Do Until dbRes.EOF
 dbRes.MoveNext
Loop

dbRes.Close

こんな感じでしょうか。

RecordSet を Close する前に希望の処理を記述します。
この場合、dbRes.MoveNext です。
しかし Close した後では RecordSet は使用できなくなります。

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic

dbRes.Close

'何かRecordSetを操作する処理を行う
dbRes.MoveFirst
Do Until dbRes.EOF
 dbRes.MoveNext
Loop

実行時エラー'3704':
オブジェクトが閉じている場合は、操作は許可されません。

閉じる前に全ての処理を記述できればよいのですが、そうもいきません。
最悪、Close しないで処理を続けても動くとは思いますが、なんか気持ち悪いですね。

それにRecodeSet は任意のタイミングで操作したい。

Dim cloneRs As ADODB.Recordset
Set cloneRs = dbRes.clone

として dbRes を cloneRs にコピーをして cloneRs を使いまわそうと思っても、オリジナルの dbRes を Close すると cloneRs まで同じタイミングで破棄されるので結局意味がありません。

2次元配列かRecordSetを作成してレコードごとに書き出していくしかないようです。
どうせなら RecordSet に書き出すようにしてみます。
(cloneRsを変数宣言していますが実際は引数で与えます)

Dim cloneRs As ADODB.Recordset
Dim fld As ADODB.Field

Set cloneRs = New ADODB.Recordset
For Each fld In dbRes.Fields
    cloneRs.Fields.Append fld.Name, fld.Type, fld.DefinedSize,
fld.Attributes
Next

cloneRs.Open

Do Until dbRes.EOF

    cloneRs.AddNew

    For Each fld In dbRes.Fields
        cloneRs.Fields(fld.Name).Value = fld.Value
    Next

    cloneRs.Update
    dbRes.MoveNext

Loop

これで、dbRes を Close しても、cloneRs は、使用ができます。


下記が、コードとなります。
cnStr は、起動時並びにファイルを「名前を付 けて保存」した際に、再読み込みできるよう別プロシージャにしました。

Option Explicit
Public cnStr As String


Sub GetCnStr()  '接続文字列の作成

Dim PathStr As String
PathStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name

cnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="
cnStr = cnStr + PathStr
cnStr = cnStr + "; ReadOnly=False;Extended Properties= "Excel 8.0; HDR=YES;""

End Sub


Sub GetData(cloneRs As ADODB.Recordset, sheetName As String)    'シートデータを取得しRecordSetに変換

Dim SQLstr As String
Dim ColName As String
ColName = Sheets(sheetName).Range("A1").Value
SQLstr = "SELECT * FROM [" & sheetName & "$] WHERE " & ColName & " IS NOT
NULL"


Dim ShtConn As ADODB.Connection
Set ShtConn = New ADODB.Connection

ShtConn.ConnectionString = cnStr
ShtConn.Open

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic

If dbRes.RecordCount = 0 Then
    GoTo Label1
End If

dbRes.MoveFirst

Dim fld As ADODB.Field

Set cloneRs = New ADODB.Recordset
For Each fld In dbRes.Fields
    cloneRs.Fields.Append fld.Name, fld.Type, fld.DefinedSize, fld.Attributes
Next

cloneRs.Open

Do Until dbRes.EOF
    cloneRs.AddNew

    For Each fld In dbRes.Fields
        cloneRs.Fields(fld.Name).Value = fld.Value
    Next

    cloneRs.Update
    dbRes.MoveNext
Loop

Label1:

dbRes.Close
Set dbRes = Nothing
ShtConn.Close

End Sub

こうしておけば、RecordSetを宣言していろいろなRecordSetを取り出せます。


ではでは～☆ミ

ExcelのVBA 「定義されている設定に違反する」のエラー

みなさん、こんにちは。


たまぁ～に、遊び半分でプログラムのコードを書いたりしています。(へっぽこですど)

するとコンピュータのすべてのカテゴリーに精通していると勘違いされます。
マックの設定や使い方を聞かれたりとか、エクセルで超高度なグラフの作り方を聞かれたりとか・・・・・。

エクセルなんて実は表を作成して簡単なグラフしか作れないレベルなんですけどね。
スプレットシートとして考えるならそれで十分だと自分では思っているのですが、世の中、グラフィカルなグラフが氾濫しすぎですよ。

で、「エクセルなら何でもできる」「プログラムを書くなら何でもできる」と考えてる人が少なからずいて、そういった人は偉い人だったりします。

まっ、そんなこんなでいろいろ苦労したりするハメになってます。


どうせならキチンとした環境で書きたいんだけど、やっぱエクセルで書くことになっちゃうんだよねぇ。
慣れないから結構ハマってますので、備忘録と同様な環境で苦労されている同志のためにハマった点をアップしておきます。


シートのデータを RecordSet に取得して操作すると、結構な頻度で下記エラーが出現する

[Microsoft][ODBC Excel Driver]入力した値がこのテーブルまたはリストに対して定義されている設定に違反するため（たとえば、値が最小値未満か、最大値より大きい）、変更を記録できません。エラーを修正してやり直してください。

書いていたコードは以下のとおり。
データの入っているシートは使いまわしがきくように変数(sheetName)にしています。

'接続文字列の作成
Dim PathStr As String
PathStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name

cnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="
cnStr = cnStr + PathStr
cnStr = cnStr + "; ReadOnly=False;Extended Properties=" Excel 8.0; HDR=YES;""


'データの入っているシートは変数化、実際は引数で与える
Dim sheetName As String
sheetName = "DataSheet"

'RecordSet取得
Dim SQLstr As String
SQLstr = "SELECT * FROM [" & sheetName & "$]"

Dim ShtConn As ADODB.Connection
Set ShtConn = New ADODB.Connection

ShtConn.ConnectionString = cnStr
ShtConn.Open

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic


'何かRecordSetを操作する処理を行う
dbRes.MoveFirst
Do Until dbRes.EOF
dbRes.MoveNext
Loop


dbRes.Close
Set dbRes = Nothing
ShtConn.Close

「定義されている設定に違反する」とあるので、まず最初に考えられるのが変数の型の間違い。

エクセルだと文字列の「1」と入れたハズが、数値の「1」になっているのはよくある話です。
データ入力の時に入れた値からエクセルが自動的に判断してくれるようですが、意図しない型になっていることがあります。

しかしながらシートのデータを確認してもおかしなデータは入力されていないようです。


次に考えられるのがいわゆる空白セルってやつ。
データを編集した後にエラーが出やすいので、RecodeSetのデータを確認すると Fields.Value がすべて NULL のレコードを発見。

これが今回のエラーの原因のようです。

エクセルの場合、最初にシートを作成した状態ではセルには何もない状態ですが、一度データを入力した後に、範囲選択してデータをDELETEキーでキレイにしても値がクリアされるだけで最初の状態には戻りません。

値のクリアではなく削除処理をキチンとしないと何かのタイミングで Fields.Value がすべて NULL のレコードを RecordSet が拾ってしまいエラーが出てしまいます。

イメージとしては、最初にシートを作成した時はセルには何もない状態。
データを入力した時にセルにデータを入れる箱ができて、その箱の中にデータが収納される。
で、データをクリアすると箱の中の値はなくなるけど、箱は空箱の状態でセルに残った状態。

その状態で RecordSet を操作しようとするとエラーが発生する。

こんな流れのようです。


使用者にセルをキチンと削除するようにしてもらえばエラーは発生しないと思うけど、クリアと削除を使い分けながら入力作業をするのは鬱陶しいと思われるので、別の方法を検討しなければなりません。

一応データベースを模した入力用のシートになっているので、1列目は入力必須項目になってます。
よって1列目が NULL でない行のみを RecordSet に取得するようにコードを修正いたしました。

'接続文字列の作成
Dim PathStr As String
PathStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name

cnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="
cnStr = cnStr + PathStr
cnStr = cnStr + "; ReadOnly=False;Extended Properties="" Excel 8.0; HDR=YES;"""

'データの入っているシートは変数化、実際は引数で与える
Dim sheetName As String
sheetName = "DataSheet"

'RecordSet取得
Dim SQLstr As String
Dim ColName As String
ColName = Sheets(sheetName).Range("A1").Value
SQLstr = "SELECT * FROM [" & sheetName & "$] WHERE " & ColName & " IS NOT NULL"

Dim ShtConn As ADODB.Connection
Set ShtConn = New ADODB.Connection

ShtConn.ConnectionString = cnStr
ShtConn.Open

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic


'何かRecordSetを操作する処理を行う
dbRes.MoveFirst
Do Until dbRes.EOF
dbRes.MoveNext
Loop


dbRes.Close
Set dbRes = Nothing
ShtConn.Close

これでエラーは回避できました。
めでたしめでたし。


ではでは～☆ミ

エクセルで複数部印刷するとエラーが出て印刷できない

みなさん、こんにちは。


2週間くらい前に「エクセルファイルで複数部印刷するとエラーが出て印刷できない」って相談がありました。
該当ファイルを送付してもらい、同じプリンタに印刷すると

というエラーがたしかに出ます。

LBP3930 ON  E06：でエラーが発生したため、ファイルを印刷できませんでした。
原因としていくつかの理由が考えられます。 

・メモリ不足の可能性があります。ほかの不要なファイルやプログラムを閉じてみてください。
・ネットワークプリンターを使用している場合、ネットワークの接続またはプリンタードライバーに原因がある可能性があります。
・プリンターケーブルが接続されていないか、またはコンピューターとプリンターの接続に問題があります。 

印刷の問題のトラベルシューティングの詳細については、[ヘルプ]をクリックしてください。

特にメモリ不足もネットワークの問題も考えられません。

別のエクセルファイルは問題なく複数部印刷できるのでドライバの問題も考えられません。

ちなみに別のプリンタに印刷してみると問題なく印刷できるプリンタと同じエラーがでるプリンタとありました。

これは Windows Update が怪しいかな、と思ったら・・・・・。

Excelファイルを3枚以上印刷しようとすると2枚しか印刷されません。

とのこと。やっぱりね。


KB3177725をアンインストールすると正常に印刷できるようになりましたが、いまだ修正版は配布されていないようです。
KB3177725のアンインストールで対処された方は、対象更新を非表示処理しないと元通りになってしまいますのでご注意を！！

上記リンクにアンインストール以外の回避策が掲載されてますので、セキュリティ上アンインストールしたくない方は、一度試してみてください。

もしくは、Microsoft XPS Document WriterでXPSファイルにするか、pdfファイルなどにしてから複数部印刷をする方法もあるかと思います。


はやく修正パッチがリリースされないと困りますねぇ。


ではでは～☆ミ

エクセルのピポットテーブルレイアウトを2003形式に戻す

みなさん、こんにちは。

最近のエクセルのピポットテーブル、使いづらくありませんか？

もともとピポットテーブルの使い方があまり分らないまま使っていたのですが、エクセル2007以降のピポットテーブルはレイアウト表示が変わってしまったため、ますますわかりません。

データ集計するのにSQLで集計することが多いのでエクセルのピポットテーブルって使う機会が少ないんですよ。

とわいえエクセルでの集計も避けて通れない状況です。

以前のレイアウト表示に戻して使うこととします。

「分析」→「ピポットテーブル」→「オプション」とすすみます。

「ピポットテーブルオプション」より「表示」タブを選択して「従来のピポットテーブルレイアウトを使用する」にチェックを入れます。

これで以前までのピポットテーブルレイアウトが使用できるようになりました。

ではでは～☆ミ

pdfファイルをExcelファイルに変換する（Windows標準機能で）

みなさん、こんにちは


先日PDFファイルをEXCELファイルに変換する無料のソフトを紹介いたしましたが、量が少ないのであれば、わざわざソフトをインストールするまでもなく、Windows標準のソフトで変換が可能です。

若干の手間はかかりますが、PDFデータ1～2枚程度ならこちらの方が手軽だと思います。


PDFファイルを開き「編集」→「すべてを選択」を行います。

「編集」→「コピー」より選択した部分をコピーします。

続いて「Windowsアクセサリ」より「ワードパッド」を起動してデータを「貼り付け」でデータを貼り付けます。

「Word」でも可ですが、「ワードパッド」の方がレイアウトが維持されてきれいにコピーできます。

「ワードパッド」に貼り付けされたデータを必要があれば編集して、「すべて選択」後、「コピー」を実施して今度はEXCELに貼り付けます。

表形式の状態でコピーができました。

処理できる量に限りがあるのと「ワードパッド」経由でコピーするので少し面倒かもしれませんが、特に追加でソフトをインストールすることなく変換できます。

お手軽かなと思いますよ。

ではでは～☆ミ

pdfファイルをExcelファイルに変換する無料ソフトウェア

みなさん、こんにちは


Excelファイル等をpdfに変換するツールは数多くありますが、逆パターンのpdfファイルをExcelに変換するツールはあまりありません。

大量のpdfデータを集計したい時に結構不便ですよね。

有料無料を含め、いくつかのツールを試してみて使い勝手の良いのが見つかったので紹介したいと思います。
もちろん無償のフリーソフトです。


Renee PDF Aide

ダウンロードはこちらからできます。

「Renee PDF Aide」は、現在はフリーソフトとしてリリースされています。
ライセンスキーの入手にメールアドレスを登録する必要がありますが、現在は無料で入手可能です。

EXCEL以外にも、WORD、PowerPoint、TEXT、JPEGなど多彩な形式に変換できます。


ライセンス認証が済んだら、設定を少しだけ直しておきましょう。

・出力設定

「OCR光学式文字認識機能を使用」にチェックを入れます。

変換処理に時間がかかるので、これは画像として取り込んだpdfの場合に使用するようにしましょう。

・オプションをクリックして「EXCEL」タブを開きます

「レイアウト/書式維持」のチェックを入れます。

・「OCR」タブを選択します

言語を日本語を選択しておきます。

設定は以上です。

後はドラッグアンドドロップでpdfファイルを選択して「変換」をクリックするだけ。

ページ数が多いpdfファイルでもシートごとに変換されるので便利ですね

次回は、ツールを使わずに変換する方法を紹介したいと思います。

ではでは～☆ミ

EXCELが二重起動する（空のEXCELが起動する）

みなさん、こんにちは


EXCELを終了した時、空のEXCELが残っていることがありませんか？

私の環境ではこんな症状は出ていなかったのですが、結構な人からこの症状の報告がありました。

最初はダブルクリックか何かで二重起動でもしてしまったのかと思ったのですが、どうも違うようです。
（EXCEL2013からエクセルファイルが別Windowで起動出来るようになった為、そこら辺が悪さをしているのかと思ったのですが・・・・）


さて、症状の出ているPCを色々みていて共通の事項がありました。

全員エクスプローラーをプレビュー表示で利用しているのです。


Windows7以降のプレビュー機能はかなり使いやすくなった為、利用しているユーザーが多いようです。

いちいちファイルを起動しなくても中身が確認できて大変便利な機能なのですが、実はこれが悪さをしていました。


プレビュー機能をOFFにすると症状が改善いたします。

ただプレビュー表示下では症状が改善できる手立てがないので、Microsoft Support に連絡をいれてみました。

後日 Microsoft Support より連絡がありました。
「症状の確認は取れたが改善の手立ては現在のところプレビュー表示をOFFにしかするしかない」との回答でした。（いや、知ってるよ、それは）

さらに「技術部門に症状を連絡する」とのことでしたので、WindowsUpdate で修正パッチが配布されるかもしれません。



それから半年以上たちますが、WindowsUpdate でリリースされた気配はないですねぇ。orz


ではでは～☆ミ