VB.NET – VBA SQL

2020年4月21日2020年5月23日

format関数（3回目ぐらい）

format関数はvba、SqlServer、VB.netにありますが少し入力値に違いありです

vbaは文字でも数字でも日付型でも良いみたい
format(“41″,”000”) は041
だけど
SqlServer、VB.netの入力値は数字または日付型のみ許されるようです。
sqlserverでは
format(cast(’41’ as int),’000′)
としないと思った通りに返ってこない

知らなかったがVBAで
strconv(“abc”,vbUpperCase)　は　ABC　だけど
format(“abc”,”>”) 　も　ABC
知らなくても良さそうだが書いておきます。

2020年1月30日2020年1月30日

VB.NETのイミディエイトウィンドウのインテリセンスが不安定？

ソリューションを開く、イミディエイトウィンドウを開く、入力してもインテリセンスは出ない。
ブレークポイントなど置いてステップイン、エディターがデバッグモードになるとインテリセンスが出る。
その後はデバッグ中止して、普通の状態でも出る。
一度ソリューションを閉じて開く、デバッグ開始しなくても出る。
もう一度閉じて開くと出なくなる。
IDE再起動はもちろん出なくなる。

シンボルをローカルに置いても同じ症状。
4Kモニターのスケーリングを変えても同じ。

あまり困ることはないけど気になります。
visual studio2019で win10でもwindows server2019でも同様です。　

2020年1月29日2020年6月29日

VBAからExcelを操作2

前回の

            Call Ary関数
            For j = 0 To UBound(Ary関数, 1)
 
                For I = 1 To UBound(Ary関数, 2)
                    objCells(j + 1, I + 2) = Ary関数(j, I)
                Next
            Next

Call Ary関数

For j = 0 To UBound(Ary関数, 1)

For I = 1 To UBound(Ary関数, 2)

objCells(j + 1, I + 2) = Ary関数(j, I)

この部分耐えがたい遅さです。
vb.netの二次元配列はまあまあ我慢できますがVBAはデータが多くなるとだめです。
一次元目の要素（項目数）が少なければ一次元配列を並べた方が速いです。
さらに速いのはレコードセットから直接ループさせつつ書き込む。
すごく速いのでvb.netもデータテーブルから直接書き込むようにしました。
プロセスも今の所残っていません。コードが冗長になりますけど。

2020年1月25日2020年1月25日

vb.net から Excel を操作 2

前回の続きです。
excelを開いて、あるワークシートのコピーを最前列に追加、
適当なセルから順に配列の値を流し込んで保存して閉じる。

        Dim objExcel As Object = Nothing
        Dim objExApp As Object = Nothing
        Dim objWorkbook As Object = Nothing
        Dim objWorkbooks As Object = Nothing

        Dim fpath As String
        fpath = "ファイル.xlsのパス"

        Dim objSheets As Object = Nothing
        Dim objSheet1 As Object = Nothing
        Dim objSheet2 As Object = Nothing
        Dim objSheet3 As Object = Nothing
        Dim objCells As Object = Nothing
        Dim i As Integer
        Dim j As Integer
        Try
            objExcel = CreateObject("Excel.Application")
            objExApp = objExcel.Application
            objExApp.Visible = True
            'objExApp.EnableEvents = False
            objWorkbooks = objExApp.Workbooks
            objWorkbook = objWorkbooks.Open(fpath)
            objExcel.DisplayAlerts = False
            'System.Threading.Thread.Sleep(5000)
            objSheets = objExApp.Sheets
            objSheet1 = objSheets(1)
            objSheet1.delete '前回の入力は削除
            Marshal.ReleaseComObject(objSheet1)
            objSheet1 = objSheets(1)
            objSheet2 = objSheets("シート名")
            objSheet2.Copy(Before:=objSheet1)
            Marshal.ReleaseComObject(objSheet1)
            Marshal.ReleaseComObject(objSheet1)　'なぜか2回解放が必要
            Marshal.ReleaseComObject(objSheet2)　
            objSheet3 = objSheets("シート名 (2)")
            objCells = objSheet3.Cells

            Call Ary関数()　'別にpublic function Ary関数()で作成、二次元配列　

            For j = 0 To UBound(Ary関数, 1)
                For i = 1 To UBound(Ary関数, 2)
                    objCells(j + 1, i + 2) = Ary関数(j, i)　'この辺は入れたい場所でいろいろ変化
                Next
            Next

            Marshal.ReleaseComObject(objCells)
            Marshal.ReleaseComObject(objSheet3)
            Marshal.ReleaseComObject(objSheets)

            objWorkbook.SaveAs(fpath)

            Marshal.ReleaseComObject(objWorkbook)
            Marshal.ReleaseComObject(objWorkbooks)
            objExApp.Quit()
            objExcel.DisplayAlerts = True
            Marshal.ReleaseComObject(objExApp)
            Marshal.ReleaseComObject(objExcel)

            objCells = Nothing
            objSheets = Nothing
            objSheet1 = Nothing
            objSheet2 = Nothing
            objSheet3 = Nothing
            objWorkbook = Nothing
            objWorkbooks = Nothing
            objExApp = Nothing
            objExcel = Nothing

        Catch ex As Exception
            Throw


        End Try

Dim objExcel As Object = Nothing

Dim objExApp As Object = Nothing

Dim objWorkbook As Object = Nothing

Dim objWorkbooks As Object = Nothing

Dim fpath As String

fpath = "ファイル.xlsのパス"

Dim objSheets As Object = Nothing

Dim objSheet1 As Object = Nothing

Dim objSheet2 As Object = Nothing

Dim objSheet3 As Object = Nothing

Dim objCells As Object = Nothing

Dim i As Integer

Dim j As Integer

Try

objExcel = CreateObject("Excel.Application")

objExApp = objExcel.Application

objExApp.Visible = True

'objExApp.EnableEvents = False

objWorkbooks = objExApp.Workbooks

objWorkbook = objWorkbooks.Open(fpath)

objExcel.DisplayAlerts = False

'System.Threading.Thread.Sleep(5000)

objSheets = objExApp.Sheets

objSheet1 = objSheets(1)

objSheet1.delete '前回の入力は削除

Marshal.ReleaseComObject(objSheet1)

objSheet1 = objSheets(1)

objSheet2 = objSheets("シート名")

objSheet2.Copy(Before:=objSheet1)

Marshal.ReleaseComObject(objSheet1)

Marshal.ReleaseComObject(objSheet1)　'なぜか2回解放が必要

Marshal.ReleaseComObject(objSheet2)　

objSheet3 = objSheets("シート名 (2)")

objCells = objSheet3.Cells

Call Ary関数()　'別にpublic function Ary関数()で作成、二次元配列　

For j = 0 To UBound(Ary関数, 1)

For i = 1 To UBound(Ary関数, 2)

objCells(j + 1, i + 2) = Ary関数(j, i)　'この辺は入れたい場所でいろいろ変化

Marshal.ReleaseComObject(objCells)

Marshal.ReleaseComObject(objSheet3)

Marshal.ReleaseComObject(objSheets)

objWorkbook.SaveAs(fpath)

Marshal.ReleaseComObject(objWorkbook)

Marshal.ReleaseComObject(objWorkbooks)

objExApp.Quit()

objExcel.DisplayAlerts = True

Marshal.ReleaseComObject(objExApp)

Marshal.ReleaseComObject(objExcel)

objCells = Nothing

objSheets = Nothing

objSheet1 = Nothing

objSheet2 = Nothing

objSheet3 = Nothing

objWorkbook = Nothing

objWorkbooks = Nothing

objExApp = Nothing

objExcel = Nothing

Catch ex As Exception

Throw

End Try

難しい

2020年1月18日2020年1月18日

vb.net から Excel を操作

以前ここでExcel出力のことを書いたけど
裏技的で推奨できない。
正攻法で行くことにしました。
Excel参照設定もクライアント環境で違うでしょうから依存しない実行時バインディングで・・
excelのプロセスが残らないようにするのが大変でした
まずは単純に開いて5秒待って閉じるだけ

    Public Shared Sub Excelmain2()
        Dim objExcel As Object = Nothing
        Dim objExApp As Object = Nothing
        Dim objWorkbook As Object = Nothing
        Dim objWorkbooks As Object = Nothing
        Dim fpath As String
        fpath = "xlsファイルのパス"

        Try
            objExcel = CreateObject("Excel.Application")
            objExApp = objExcel.Application
            objExApp.Visible = True
            objWorkbooks = objExApp.Workbooks
            objWorkbook = objWorkbooks.Open(fpath)

            System.Threading.Thread.Sleep(5000)

            objExcel.DisplayAlerts = False

            Marshal.ReleaseComObject(objWorkbook)
            Marshal.ReleaseComObject(objWorkbooks)
            objExApp.Quit()
            objExcel.DisplayAlerts = True
            Marshal.ReleaseComObject(objExApp)
            Marshal.ReleaseComObject(objExcel)

            objWorkbook = Nothing
            objWorkbooks = Nothing
            objExApp = Nothing
            objExcel = Nothing
        Catch ex As Exception
            Throw

        End Try
    End Sub

Public Shared Sub Excelmain2()

Dim objExcel As Object = Nothing

Dim objExApp As Object = Nothing

Dim objWorkbook As Object = Nothing

Dim objWorkbooks As Object = Nothing

Dim fpath As String

fpath = "xlsファイルのパス"

Try

objExcel = CreateObject("Excel.Application")

objExApp = objExcel.Application

objExApp.Visible = True

objWorkbooks = objExApp.Workbooks

objWorkbook = objWorkbooks.Open(fpath)

System.Threading.Thread.Sleep(5000)

objExcel.DisplayAlerts = False

Marshal.ReleaseComObject(objWorkbook)

Marshal.ReleaseComObject(objWorkbooks)

objExApp.Quit()

objExcel.DisplayAlerts = True

Marshal.ReleaseComObject(objExApp)

Marshal.ReleaseComObject(objExcel)

objWorkbook = Nothing

objWorkbooks = Nothing

objExApp = Nothing

objExcel = Nothing

Catch ex As Exception

Throw

End Try

End Sub

開いて閉じるだけでも変数が多くて混乱します
次に開いてセルに文字代入して保存して閉じる。

    Public Shared Sub Excelmain3()

        Dim objExcel As Object = Nothing
        Dim objExApp As Object

        Dim objWorkbook As Object = Nothing
        Dim objWorkbooks As Object
        Dim fpath As String
        fpath = "xlsファイルのパス"

        Dim objSheets As Object = Nothing
        Dim objSheet As Object = Nothing
        'Dim objRange As Object = Nothing
        Dim objCells As Object = Nothing

        Try
            objExcel = CreateObject("Excel.Application")
            objExApp = objExcel.Application
            objExApp.Visible = True
            objWorkbooks = objExApp.Workbooks
            objWorkbook = objWorkbooks.Open(fpath)

            System.Threading.Thread.Sleep(5000)
            objSheets = objExApp.Sheets
            objSheet = objSheets(1)
            objCells = objSheet.Cells
            objCells(2, 2) = "hogehoge:" & Now

            Marshal.ReleaseComObject(objCells)
            Marshal.ReleaseComObject(objSheet)
            Marshal.ReleaseComObject(objSheets)

            objExcel.DisplayAlerts = False
            objWorkbook.SaveAs(fpath)

            Marshal.ReleaseComObject(objWorkbook)
            Marshal.ReleaseComObject(objWorkbooks)
            objExApp.Quit()
            objExcel.DisplayAlerts = True
            Marshal.ReleaseComObject(objExApp)
            Marshal.ReleaseComObject(objExcel)

            objCells = Nothing
            objSheets = Nothing
            objSheet = Nothing
            objWorkbook = Nothing
            objWorkbooks = Nothing
            objExApp = Nothing
            objExcel = Nothing

        Catch ex As Exception
            Throw
        End Try
    End Sub

Public Shared Sub Excelmain3()

Dim objExcel As Object = Nothing

Dim objExApp As Object

Dim objWorkbook As Object = Nothing

Dim objWorkbooks As Object

Dim fpath As String

fpath = "xlsファイルのパス"

Dim objSheets As Object = Nothing

Dim objSheet As Object = Nothing

'Dim objRange As Object = Nothing

Dim objCells As Object = Nothing

Try

objExcel = CreateObject("Excel.Application")

objExApp = objExcel.Application

objExApp.Visible = True

objWorkbooks = objExApp.Workbooks

objWorkbook = objWorkbooks.Open(fpath)

System.Threading.Thread.Sleep(5000)

objSheets = objExApp.Sheets

objSheet = objSheets(1)

objCells = objSheet.Cells

objCells(2, 2) = "hogehoge:" & Now

Marshal.ReleaseComObject(objCells)

Marshal.ReleaseComObject(objSheet)

Marshal.ReleaseComObject(objSheets)

objExcel.DisplayAlerts = False

objWorkbook.SaveAs(fpath)

Marshal.ReleaseComObject(objWorkbook)

Marshal.ReleaseComObject(objWorkbooks)

objExApp.Quit()

objExcel.DisplayAlerts = True

Marshal.ReleaseComObject(objExApp)

Marshal.ReleaseComObject(objExcel)

objCells = Nothing

objSheets = Nothing

objSheet = Nothing

objWorkbook = Nothing

objWorkbooks = Nothing

objExApp = Nothing

objExcel = Nothing

Catch ex As Exception

Throw

End Try

End Sub

セルに代入するだけで変数が増えました。
これを踏み台にdatatableの数値を配列を使ってExcelのセルに代入する。

2019年6月8日

openrowsetで既存Excelファイルへexport

vb.netでSQLserverテーブルをクエリして既存excelファイルに出力

accessのVBAからなら
docmd.transferspreadsheet acexport,acspreadsheettypeexcel9,”クエリ”,”ファイルパス”
でしたがvb.netでは難しいです

SQLserverにはopenrowsetというクエリでexportできるようなのでvb.netからExecutenonqueryで操作

SQLserverで準備
SQLserverは64bitでaccess32bitがすでにインストされているのでACEの32bitが既存
SQLserverのリンクサーバに64bitのACEのプロバイダ登録が必要なので
https://knowledge.autodesk.com/ja/support/autocad/learn-explore/caas/sfdcarticles/sfdcarticles/JPN/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.htmlを参照にACEの64bit版をちょっと強引にインスト

SQLserverのリンクサーバーにACE登録

SQLserverでmasterデータベースで以下クエリ発行
exec sp_configure ‘show advanced options’,1;
reconfigure;
exec sp_configure ‘ad hoc distributed queries’,1;
reconfigure;
exec sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ ,N’AllowInProcess’,1;
reconfigure;
exec sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ ,N’DynamicParameters’,1;
reconfigure;

exportするクエリは
insert into openrowset(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;DATABASE=ファイルパス;imex=0;hdr=1;’,
‘SELECT カラム FROM [excelのワークシート名$]’)
select カラム from sqlserverテーブル where 条件カラム=パラメータ

接続文字がとっても厳格、ここまで試行錯誤数日
vbaでは列名含めて新しいワークシートに出力できたが
openrowsetはワークシートと列名は作成しておく

クエリが問題なければvb.netからExecutenonqueryで出力

最初VB.NETのコードでゴリゴリとやっていたけど難しくて断念
openrowsetもかなり難しいし環境が違えば同様にはいかないだろうし後日再検討しよう

2019年5月19日2019年5月19日

integer と long

VBAでは
integer -32768 から 32767
long　　-2147483648 から 2147483647
vb.netでは
integer -2147483648 から 2147483647
long -923372036854775808 から 9223372036854775807

vbaで
dim i as long
for i=0 to ..
と書いていたがvb.netで同様に書くとなぜだかインテリセンスによく怒られる
vbaのlongはvb.netではinteger
今更だけど

2019年5月12日

datagridview iifエラー

VB.NETでdatagridviewをGUIで作成するとき
クエリに
select iif(bit型カラム=0,’男’,’女’) as 性別,…from sqlserverTable
と書くとエラー
select 性別=case when bit型カラム=0 then ‘男’ else ‘女’ end,…
と書くと大丈夫

VBAのレコードセットも癖が強いがVB.NETも一筋縄では行かない気がする

2019年4月19日2019年4月19日

ちょっとベンチ

バックエンドSQLserverフロントエンドAccessVBA　VS　VB.NET
‘VBA
Public Function Queryspeedtest()
Dim T1 As Double
Dim T2 As Double
T1 = Timer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = “OLEDB接続文字”
cn.Open
rs.cursorlocation=adUseClient
rs.Open “select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn, adOpenStatic, adLockOptimistic

rs.Close
cn.Close

T2 = Timer
Debug.Print T2 – T1
End Function

‘VB.NET
Public Shared Function Queryspeedtest()
Dim SW = New System.Diagnostics.Stopwatch()
SW.Start()

Dim cn = New System.Data.OleDb.OleDbConnection(OLEDB接続文字)
cn.Open()
Dim cmd = New System.Data.OleDb.OleDbCommand(
“select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn
)
Dim DT = New DataTable(“test”)
Dim adp = New System.Data.OleDb.OleDbDataAdapter(cmd)
adp.Fill(DT)
adp.Dispose()
cn.Close()

SW.Stop()
Debug.WriteLine(SW.Elapsed)
End Function

tableAは20万件、TableBは1万件弱のマスターファイル
TableAのレコードのcodeからTableBのcodenameを得るよくあるクエリ
もちろん主キーは両方あり

単純なselect column from tableは若干VBが勝利
上記のクエリはVBA 0.4s NET 0.8s

倍とはいえ差はコンマ数秒なのでまあいいかと思うがdatagridviewでtextbox表示とかcomboboxに入れたりするとちょっと・・
Accessフォームの方が速い、VB.NETではちょっと工夫が要りそうな

vbaでcursolocationがクライアントの時はcursoltype、locktypeによる差はない
サーバサイドでは最速と覚えていたadopenforwardonlyよりadopendynamicの方がなぜか早かった

ADODBとADO.NETはまったく別物なので比較するなと言われそうだけど・・
でもVSのエディタはすばらしい、VBAエディターが残念に思えるほど

2019年4月17日2019年4月17日

datatable のクエリ

が遅い

sqlserverテーブルのシンプルな選択クエリ。
フォームに表示が遅いのではなくデザイナーで作成途中の確認の段階で遅い。

accessから移行中のクエリだがaccessのリンクテーブルのクエリの方がはるかに速い

ADODBのデータソースレス接続の様にコードにクエリを書いてdataadapter作成したほうが速いのか？

ODBC接続のaccessクエリに ADO.NETが速さで劣る？

どこか間違えているのかも、明日落ち着いて見直そう

2024年4月
月	火	水	木	金	土	日
« 11月
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30