ソース掲示板




すべてから検索

キーワード   条件 表示 現行ログ 過去ログ トピックス 名前 本文
【VB.NET】COM経由の ADODB で EXCEL を 参照
日時: 2013/05/01 23:21
名前: lightbox







↓商品分類マスタ.xls
http://lightbox.on.coocan.jp/download/syobun_xls.lzh

Excel 参照
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

	' exe が存在するディレクトリ
	Dim ProgPath As String = Application.StartupPath
	Dim ExcelPath As String = ProgPath & "\商品分類マスタ.xls"

	Dim ConnectionString As String = _
	   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	   "Data Source=" & ExcelPath & ";" & _
	   "Extended Properties=""Excel 8.0;IMEX=1;"""

	Dim Cn As ADODB.Connection = New Connection()
	Try
		Cn.Open(ConnectionString)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
		Exit Sub
	End Try

	Dim Rs As ADODB.Recordset = New Recordset()
	Dim Query As String = "select * from [商品分類マスタ]"
	Try
		Rs.Open(Query, Cn)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
		Cn.Close()
		Exit Sub
	End Try

	Dim Buffer As String = ""
	Do While Not Rs.EOF
		For i As Integer = 0 To Rs.Fields.Count - 1
			If i <> 0 Then
				Buffer = Buffer & ","
			End If
			Buffer = Buffer & Rs.Fields(i).Value
		Next
		Buffer = Buffer & ControlChars.CrLf

		Rs.MoveNext()
	Loop

	Rs.Close()
	Cn.Close()

	MessageBox.Show(Buffer)

End Sub
メンテナンス

【VB.NET】COM経由の ADODB で EXCEL を MDB に更新 ( No.1 )
日時: 2008/05/19 15:23
名前: lightbox


日時: 2008/05/19 15:23
名前: lightbox




↓MDB のダウンロードページ
http://winofsql.jp/VA003334/download051208140702.htm

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

	' exe が存在するディレクトリ
	Dim ProgPath As String = Application.StartupPath
	Dim ExcelPath As String = ProgPath & "\商品分類マスタ.xls"
	Dim MDBPath As String = ProgPath & "\販売管理B.mdb"

	Dim ConnectionString As String = _
	   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	   "Data Source=" & ExcelPath & ";" & _
	   "Extended Properties=""Excel 8.0;IMEX=1;"""

	Dim ConnectionString2 As String = _
	  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	  "Data Source=" & MDBPath & ";"

	' *********************************************
	' 接続
	' *********************************************
	Dim Cn As ADODB.Connection = New Connection()
	Try
		Cn.Open(ConnectionString)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
		Exit Sub
	End Try
	Dim Cn2 As ADODB.Connection = New Connection()
	Try
		Cn2.Open(ConnectionString2)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
		Cn.Close()
		Exit Sub
	End Try

	' *********************************************
	' 入力
	' *********************************************
	Dim Rs As ADODB.Recordset = New Recordset()
	Dim Rs2 As ADODB.Recordset = New Recordset()
	Rs2.LockType = LockTypeEnum.adLockOptimistic


	Dim Query As String = "select * from [商品分類マスタ]"
	Try
		Rs.Open(Query, Cn)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
		Cn.Close()
		Exit Sub
	End Try

	Dim Buffer As String = ""
	Do While Not Rs.EOF

		UpdateMDB(Rs, Cn2, Rs2)
		Rs.MoveNext()

	Loop

	Rs.Close()
	Cn2.Close()
	Cn.Close()

	MessageBox.Show("更新が終了しました")

End Sub

Private Sub UpdateMDB(ByRef Rs As Recordset, ByRef Cn2 As Connection, ByRef Rs2 As Recordset)

	Dim Query As String = "select * from [商品分類マスタ]"
	Query &= " where 商品分類 = '" & Rs.Fields("商品分類").Value & "'"

	Try
		Rs2.Open(Query, Cn2)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
		Exit Sub
	End Try

	If Not Rs.EOF Then
		Rs2.Fields("名称").Value = Rs.Fields("名称").Value
		Rs2.Update()
	End If

	Rs2.Close()

End Sub
このアーティクルの参照用URLをクリップボードにコピー メンテナンス
【VB.NET】COM経由の OO4O による Oracle 参照 ( No.2 )
日時: 2008/05/19 15:24
名前: lightbox




Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click

	Dim Session As OracleInProcServer.OraSessionClass = New OracleInProcServer.OraSessionClass()
	Dim Cn As OracleInProcServer.OraDatabase

	Cn = Session.OpenDatabase("night/XE", "lightbox/lightbox", 0)

	Dim Rs As OracleInProcServer.OraDynaset
	Rs = Cn.CreateDynaset("select * from 商品分類マスタ", 2)

	Dim Buffer As String = ""
	Do While Not Rs.EOF
		For i As Integer = 0 To Rs.Fields.Count - 1
			If i <> 0 Then
				Buffer &= ","
			End If
			Buffer &= Rs.Fields(i).Value
		Next
		Buffer &= ControlChars.CrLf
		Rs.MoveNext()
	Loop

	MessageBox.Show(Buffer)

End Sub
このアーティクルの参照用URLをクリップボードにコピー メンテナンス