Public Function GetVal(s) As Currency
If Nz(s) = "" Then Exit Function
Dim i As Long
For i = 1 To Len(s)
If IsNumeric(Mid(s, i, 1)) Then
GetVal = Val(Mid(s, i))
Exit For
End If
Next
End Function
Private Sub cmd練習_Click()
On Error GoTo Err_Handler
Dim i As Integer
Dim xls As Object
Dim intNumRow, intGNumRoW As Integer
Dim strTemplateDir, strTemplateBook As String
intNumRow = 3
intGNumRoW = 5
'テンプレートの保存先フォルダ
strTemplateDir = Application.CurrentProject.Path & "\データ\テンプレート\"
'テンプレートのファイル名
strTemplateBook = "原紙.xlsx"
'Excelオブジェクトを生成
Set xls = CreateObject("Excel.application")
With xls
'画面の再描画を抑止
.screenUpdating = False
'テンプレートファイルを開く
.Workbooks.Open strTemplateDir & strTemplateBook
'ワークシートをコピー
.Workbooks(strTemplateBook).worksheets("xxx").Copy
'テンプレートファイルを閉じる
.Workbooks(strTemplateBook).Close
'ワークシートに行追加 A
If intNumRow > 1 Then
For i = 1 To intNumRow - 1
.Range("A16").entirerow.Insert
Next
End If
'ワークシートに行追加 B
If intGNumRoW > 1 Then
xls.Range(xls.cesll(21 + intNumRow, 1), xls.cells(21 + intNumRow + intGNumRoW, 2)).entirerow .Insert
End If
'画面の再描画を元に戻す
.screenUpdating = True
End With
MsgBox "保存されました。"
DoCmd.Close acForm, Me.Name
'Excelを終了
xls.Quit
Set xls = Nothing
Exit_here:
If xls Is Nothing Then Exit Sub
Exit Sub
Err_Handler:
MsgBox Err.Number & vbTab & Err.Description, vbExclamation
Resume Exit_here
End Sub
まず、回答の前に、一言。
コードの一部の抜粋を提示するより、可能ならば Sub から End Sub まですべて提示した方が話がはやいでしょう。
もし、コードが提示するには長すぎるなら、処理を複数のサブルーチンに分割することを検討した方がいいでしょう。
今回も、結局リンク先のコードをみて類推して回答することになりますが、認識違いで無駄なやりとりになる可能性もあります。
Sub Accessで新規ブックを開いて行挿入()
Dim xls As Object
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Dim wb As Object 'ワークブックオブジェクト
Set wb = xls.Workbooks.Add
Dim i As Long, j As Integer
i = 1
j = 5
With wb
.Sheets(1).Rows(21 + i).Resize(j).Insert
.Save Filename:="C:\Test\Test.xlsx"
.Close
End With
xls.Quit
Set xls = Nothing
Set wb = Nothing
End Sub
Sub Accessで既存ブックを開いて行挿入()
Dim xls As Object
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Dim wb As Object 'ワークブックオブジェクト
Set wb = xls.Workbooks.Open(Filename:="C:\Test\Test.xlsx")
Dim i As Long, j As Integer
i = 1
j = 5
With wb
.Sheets(1).Rows(21 + i).Resize(j).Insert
.Save
.Close
End With
xls.Quit
Set xls = Nothing
Set wb = Nothing
End Sub
そもそも「数字」を見つけたいのだから「間違ってヒットしてしまわないような単語（
{}）を用意する必要はなく、ヒットさせる文字自体も変換する必要ないのだからもっと短くなるねまだまだ頭が固い証拠
文字列操作はパターンが膨大になりがちで、ちょっと込み入ったことををやろうとすると考えなければならないことが数多く出てきます。「数多く」に対応しようとすれば当然計算式も長くなってしまいますね
このような目的を達成するにはプログラミング的思考が重要になってくるので、そうなれば、その思考をそのまま表現できる手法（VBA）を用いたほうがシンプルにはできるでしょう
どこまでの内容ならVBAにすべきか？というのは難しいところですけどね
もうちょっと頭を柔らかくしてみる
無理やり短くする
hiroton様
Accessの関数を考えて下さり、ありがとうございます。
ものすごく長い計算式になってしまうのですね。
データが少なければExcelでも良いのですが、Volumeがあるので、Accessで出来ればと思い、色々試行錯誤しております。
hatena様
YouTubeもあるのですね。
サンプルを実際に作成→動作確認 やってみます。
的確なアドバイスありがとうございました。
人にもよると思いますが、独学は十分可能だと思います。
まずは入門サイトを検索してみてわかりやすそうなサイトで学習してみてはどうてしょう。
あるいは、本屋さんで入門書をみてわかりやすそうなものを一冊購入して読んでみてもいいでしょう。
最近なら、YouTubeでもいろいろありそうです。
どちらにしても、読むだけではなく、そこにあるサンプルを実際に作成してみて、動作を確認しながら進めていかないと、身にはつきません。ですので、自分が将来使いそうなサンプルがあるサイトや書籍から取り組むのかいいでしょう。
ACCESSの関数を使ってやるなら
その１
その２
既存データの整形をしたいってだけならExcelでごりごりやるのが簡単だと思うけどね
hatena様
VBAは全く分からないのですが、仰る通りにしたら、抽出したいものが返りました。
ありがとうございます。助かりました。
自分でもう少しできるようしたいのですが、VBAは独学では難しいでしょうか。
標準の関数だけでやるのは難しいので、VBAでユーザー定義関数を作成してそれを利用することになりますね。
標準モジュールに下記の関数をコピーして貼り付けてください。
クエリのフィールド欄に下記の式を設定します。
以上です。
データに数値が含まれない場合は、0 を返すようになってます。
言葉足らずで申し訳ありません。
そのフィールドにしか外注単価（1.50とか2.0とか）がないので、数値のみ抽出したいです。
AR→1.50
なら、AR→ という文字列データと 1.5 という数値データに分割ということですか。
TS→全 は数値はないですが、どうしますか。
ありがとうございます。
売上データの中に 規格・型番フィールドがあります。その規格・型番フィールドのデータの中に外注単価が含まれています。
以下のようなデータがあります。空白データもあります。微妙に文字列にブランクが入っているものもあります。
AR→1.50、AR→1.5、AR2.0、N 2.0、N 1.50、TS→全、ﾆﾚｼﾞ→全、中尾ｰ>全、ﾌｼﾞﾜﾗ→全
処理したいデータ例を提示してもらえませんか。
業者名はもちろん仮名でいいので。
Access データベースの並び順序（照合順序）はファイルごとに異なり、
Windows システムやその環境下で実行される他のアプリケーションのそれとは
必ずしも一致しません。
関係あるか分かりませんが
クエリの抽出条件で環境依存の丸数字（㉑～㊿）を含む曖昧検索ができない
「外字置換内字」とは何でしょうか。
「外字Unicode」と「内字Unicode」という用語も初めて見ました。
それらについて解説してもらえますか。
同感です。見た目が同じなのに結果が異なるのはトラブルの元です。
具体的には、テーブルのデザインビューで、フィールドプロパティを下記のように設定すれば、Nullになることはないので空文字列("")かどうかのみ考慮すればOKになります。
質問の式も下記ですみます。
式1: IIf([フィールド２]="","9999","")Like "*"でデータが抜け落ちることもなくなります。お二方ともありがとうございました。
式を書き換え、なんとか今回の目的は解決できました。
このサイトはいつも皆様に助けて頂き、非常に感謝しております。
日々勉強になります。
Nullについて（もう一度学ぶMS-Accessさん）
""（空文字列（長さ0の文字列））とNullは画面上で見たときの見た目が同じだけでデータとしては別物です
異なるデータなので、並び替えをすれば、それぞれがひとまとまりになって並び変わります（どちらが先になるかは若干複雑でACCESSならNULLが先となります）
NULLは非常に厄介な問題（比較は
Is Nullとしなければならない、「全て」を抽出したつもり（Like "*"）でデータが抜け落ちる等々多数）を持っています。できることならテーブルのプロパティで制限がかけられると良いですねおっしゃる通り、テキスト型です。
ご指摘の式1の形に書き換え、[フィールド２]の並べ替え設定を削除し、式1を昇順に設定すると期待通りの並びになりました。
ということは、空白でも""とNullで並び替えの分別対象になる、ということになるという認識であっていますでしょうか？
[フィールド２]のデータ型はなんでしょうか？
テキスト型(短いテキスト)なら、
式1: IIf([フィールド２]="" Or [フィールド２] Is Null,"9999","")です。NullはイコールではなくIs演算子で判定します。
数値型なら、
式1: IIf([フィールド２] Is Null,9999,Null)となります。
""はテキストなので数値型との比較には使えません。ご連絡ありがとございます。
自身でもいろいろと調べていたのですが、クエリのフィールド名【フィールド２】の隣に
【式1: IIf([フィールド２]="" Or [フィールド２]=Null,"9999","")】をいれてクエリを実行すると、
【フィールド２】がすべて空白にもかかわらず、式１のフィールドに【空白】が返ってくるものと、【9999】が返ってくるものが存在しました。
これが並べ替えできない原因な気がするのですが、式１のフィールドに【空白】が返ってくるものは、【フィールド2】に
何が入っていると認識されているのでしょうか？スペースが入っているのかと確認しましたが、入っていません。
【症状の再現できる現状のSQLとデータ例】ですが、多数のフィールドがありレコードも多いので提示できる状態にするには少し時間が掛かりそうです。申し訳ございません。上記の情報でなにかコメント頂けると幸いです。
「レコードの値が空白」ということはNull値ということですか。
当方で作成したサンプルで確認しましたが、そのような症状は確認できませんでした。
症状の再現できる現状のSQLとデータ例を提示してもらえますか。
原因が判明しましたので報告いたします。（現在モニターを２台接続している）
過去に、アクセスを２画面(1,2)で運用していました。今回はメイン（１）のモニターのみ起動していましたので、もしやと思いもう一つのモニターを立ち上げてアクセスを起動したところ（２）の画面に表示され運用できることが判明しました。（2）のモニターケーブルを切断すると（１）の画面に表示できるようになりました。
ただ、２つのモニターを接続した状態で片方のモニターの電源を切断しているのに（ACCESS）のみ運用できないのかわかりません。このような状況を経験され解消（？）されたならご指導いただけると助かります。
この件は、半分解消されましたので「解決」といたします。
原因が判明しましたので報告いたします。（現在モニターを２台接続している）
過去に、アクセスを２画面(1,2)で運用していました。今回はメイン（１）のモニターのみ起動していましたので、もしやと思いもう一つのモニターを立ち上げてアクセスを起動したところ（２）の画面に表示され運用できることが判明しました。（2）のモニターケーブルを切断すると（１）の画面に表示できるようになりました。
ただ、２つのモニターを接続した状態で片方のモニターの電源を切断しているのに（ACCESS）のみデータと取得できないのかわかりません。このような状況を経験され解消（？）されたならご指導いただけると助かります。
この件は、半分解消されましたので「解決」といたします。
11のところで抜粋したものについてはCesllになぜかなっていましたが、
抜粋前の本コードのほうはCellsであっていましたが、hiroton様指摘の通り、entirerowの後にスペースがあることによってエラーが出たことがわかりました。
いずれにせよ、当方の非常に初歩的なミス、プラスおっちょこちょいでした。
お二方には改めてお礼を申し上げます。
今後は、このようなことがないように、利用させていただきます。
失礼しました
Cesllになってました。
直したら、動きました。
こんなくだらないことに、たくさん回答いただき、大変申し訳ないです。
Hiroton様もありがとうございました。
ほかに問題がないか後で検証します。
ありがとうございます。
コメントアウトなどの処置をして、検証しました。
のところで、「実行時エラー‘438‘: オブジェクトは、このプロパティまたはメソッドをサポートしていません。」が出ます。
その前までは、望んでいるような結果が出ています。
ざっと見た感じではコード自体に問題はなさそうです。
（もっとスマートな書き方かあるかもということは置いておいて）
とりあえずうまくいかないとき、原因を特定するためのデバッグ作業をしてみましょう。
まず、
On Error GoTo Err_Handlerはコメントアウトしておきましょう。これがあるとどこでエラーがある分かりません。
次に、
としてエクセルを表示させ、画面の再描画停止をやめましょう。
これでエクセル上での操作を目で確認できます。
そのうえで1行ずつステップ実行して動作を確認しつつ、どこでどのようなエラーが出るのか確認しましょう。ステップ実行 は下記リンクを参考にしてください。
デバッグの基本 – ステップ実行 | ExcelVBA入門 自宅でプログラミング
このデバッグ作業をして、どの行でどのようなエラーがでるのか教えてください。
失礼いたしました。
もともとのコードがぐちゃぐちゃで、そのまま貼り付けるにははばかられるようなものだったので、
修正をして貼り付けたときに、ミスが出たのだと思います。
書き込みがある度、コードが変わりますね
落ち着いて、コピペをしてください
xls.Range(xls.cesll(21 + intNumRow, 1), xls.cells(21 + intNumRow + intGNumRoW, 2)).entirerow .Insert
明らかに不要なところを除きました。
実行時に同様のエラーが出ます。
ご指摘ありがとうございます。
そのままでは3000字を越えてしまうので、少し検討します。
まず、回答の前に、一言。
コードの一部の抜粋を提示するより、可能ならば Sub から End Sub まですべて提示した方が話がはやいでしょう。
もし、コードが提示するには長すぎるなら、処理を複数のサブルーチンに分割することを検討した方がいいでしょう。
今回も、結局リンク先のコードをみて類推して回答することになりますが、認識違いで無駄なやりとりになる可能性もあります。
こちらで、リンク先のコードと提示のコードからサンプルを作成して実験したところ、下記を修正したら正常に動作しました。
ようは不足していた
)を追加しただけです。ただし、この修正前のコードでは構文エラーになり実行すらできませんので、
というようなエラーにはなりませんので、提示されていない他の部分に原因があるでしょう。
ということで、やはりコード全体を提示してください。
お二方回答ありがとうございます。
回答を見たばかりなので、内容確認できてはおりません。
こんな感じです。
https://tsware.jp/tips/tips_689.htm
様のサイトを参照して作っている部分です。
新規ブックを開いて、行挿入する場合は、Openメソッドの代わりにAddメソッドを使います。
保存するときにファイル名を指定するようにします。
既存ブックを開いて、行を挿入する場合のコード例
Openメソッドでブックを開きますが、戻り値は開いたブックなのでそれを変数に代入しておいてあとで利用します。
開いたブックの先頭シートに行挿入してます。
行挿入はRowsを使った方がコードがシンプルになります。
中略した部分も提示してください。
中略した部分でブックを開いてますか。
それとも新規ブックのシートに代入するのですか。
xls.Range() としてますが、xls(エクセルアプリケーション)はRangeプロパティをもってません。
エクセルアプリケーションのブックのシートがRangeプロパティをもっています。
どのブックのどのシートのセルに代入するかをはっきりさせないとだめです。
エクセルのVBAで、Range()としたときは、ActiveBook.ActiveSheet.Range() ということにエクセルが解釈してくれます。Access君はそのように自動で解釈してくれませんので、きちんと指定してあげないと、Access君は困ってしまいます。
うまいこと解説してるサイト見つけてペタッとして終わりたかったんですけど見つけられないですねぇ
このような問題を起こさないためには、「アクティブな」というその瞬間によって変わるような参照を使わないようにする必要があります。そのためには、オブジェクトの親子関係をしっかり把握して、最上位の親から目的の子まで、すべてをつなげる必要があります
Range/Cellsの場合つまり、
xls.Cellsと記述すると「xls.ActiveWorkBook.ActiveSheet.Cells」と同等の結果になります「アクティブな」参照をしないためには
WorkbookとWorksheetを明示してあげればいいので、例えばのような記述が考えられます
また、毎回このような記述は冗長なので、変数で置き換えたり
With ～ End Withステートメントを使ったりすると良いコードになります
Excelでなら動く
を、なんやかんやして
とした場合、このコード部分だけなら動作させることは可能です
つまり、このコードが動くための準備がうまく出来ていません
xlsはSet xls = CreateObject("Excel.application")としていますので、EXCELそのものを示しますApplication.Range プロパティ (Excel)
Application.Cells プロパティ (Excel)
いつもの難しいMicrosoft記述ですが、つまり、「ユーザーが操作できる状態じゃないとエラーになるよ」「ユーザーが操作できる状態のモノが対象になるよ」ということです
解決策としては、「操作したいシートをアクティブにする」か、「アクティブでないRange/Cellsを対象にする記述をする」のどちらかです。とはいえ、実際に使っているExcel上でマクロを動かすならともかく、「アクティブな状態」というあやふやな状態を対象とする記述はできる限りやらないほうが無難です