untitled

Size: px

Start display at page:

Download "untitled"

しほこおおはし
7 years ago
Views:

1 StiLL StiLL Excel VBA IT Excel StiLL StiLL! Excel2007 StiLL -- --

2 Excel!!!! DB CSV

3 VBA VBA ' VBA Public Sub SampleProgramDAO1() Dim SheetName As String Dim strname As String SheetName = "Sheet2" ' With ThisWorkbook.Worksheets(SheetName).DropDowns(1) strname =.List(.Value) End With If SampleProgramDAOFunc(SheetName, 7, 2, _ "SELECT * FROM WHERE ='" & strname & "'", 0) = True Then ' MsgBox strname & "", vbexclamation, "" End If End Sub VBA VBA VBA ' If DataCount > 0 Then ' With ThisWorkbook.Worksheets(SheetName) '.Range(.Cells(Y, X),.Cells(65535, X + rstpubs.fields.count - 1)).Value = "" With.Cells(Y, X) For lngcol = 0 To rstpubs.fields.count - 1.Offset(0, lngcol).value = rstpubs.fields(lngcol).name Next lngrow = 1 Do While (Not rstpubs.eof) And (lngrow <= DataCount) For lngcol = 0 To rstpubs.fields.count - 1.Offset(lngRow, lngcol).value = rstpubs.fields(lngcol).value Next SetGuage lngrow / DataCount * 15 StiLL StiLL StiLL 18 SQL ' VBA Public Sub SampleProgramDAO2() SampleProgramDAOFunc "Sheet2", 7, 2, "SELECT * FROM ", 0 End Sub ' DAO Function SampleProgramDAOFunc(SheetName As String, Y As Long, X As Integer, _ SQL As String, Limit As Long) As Boolean Dim strname As String ' Dim rngcell As Range, lngrow As Long, lngcol As Long Dim wrkodbc As DAO.Workspace Dim dbspubs As DAO.Database Dim rstpubs As DAO.Recordset Dim DataCount As Long ' On Error GoTo Sub_Err ' ResetGuage UserForm1.Show DoEvents Application.ScreenUpdating = False 'ODBCDirect Workspace Set wrkodbc = DAO.CreateWorkspace(" ", "admin", "", dbuseodbc) 'Database Set dbspubs = wrkodbc.opendatabase(" ", dbdrivercomplete, True) 'Recordset Set rstpubs = dbspubs.openrecordset(sql, dbopensnapshot, dbrunasync) ' Set rstpubs = dbspubs.openrecordset(sql, dbopendynaset, dbrunasync) 'Set NewQRY = db.createquerydef("", QString) ' rstpubs.movelast DataCount = rstpubs.recordcount rstpubs.movefirst ' RecordCount If DataCount = -1 Then DataCount = 0 Do While Not rstpubs.eof DataCount = DataCount + 1 rstpubs.movenext Loop rstpubs.movefirst End If ' Limit Limit (Limit =0 ) If Limit > 0 And DataCount > Limit Then DataCount = Limi lngrow = lngrow + 1 rstpubs.movenext Loop End With End With End If ' rstpubs.close dbspubs.close wrkodbc.close Set rstpubs = Nothing Set dbspubs = Nothing Set wrkodbc = Nothing ' ResetGuage UserForm1.Hide Application.ScreenUpdating = True SampleProgramDAOFunc = True Exit Function Sub_Err: ' ResetGuage UserForm1.Hide Application.ScreenUpdating = True MsgBox "" End Function VBA 137 ' Private Sub SetGuage(n As Integer) Dim i As Integer For i = 1 To n UserForm1.Controls("D" & Trim(i)).BackColor = &H DoEvents Next i End Sub ' Public Sub ResetGuage() Dim i As Integer For i = 1 To 15 UserForm1.Controls("D" & Trim(i)).BackColor = &HE0E0E0

> 0 Then ' With ThisWorkbook.Worksheets(SheetName) '.Range(.Cells(Y, X),.Cells(65535, X + rstpubs.fields.count - 1)).Value = "" With.Cells(Y, X) For lngcol = 0 To rstpubs.fields.count - 1.Offset(0, lngcol).

4 StiLL!! :!!

5 : StiLL! Excel OK Enter Enter!

6 C/S Excel(.XLS)() CSV Excel(.XLS) Excel(.XLS) Excel(.XLS) () CSV Excel(.XLS) Excel(.XLS)

7 StiLL DB

8 StiLL StiLL StiLL Standard Professional Developers StiLL

9 StiLL Developers Professional Professional SQL ok!

10 StiLL 89(StiLLDevelopers) Professional 79 StiLLDevelopers StiLLDevelopers Only StiLLDevelopers Only

11 StiLL Excel Excel StiLL Excel StiLL

12 StiLL StiLL StiLL StiLL StiLL StiLL StiLL StiLL

13 StiLL IT

Excel ダッシュボードのご紹介株式会社アイエルアイ総合研究所内藤慶一

Excel ダッシュボードのご紹介株式会社アイエルアイ総合研究所内藤慶一 Excel ダッシュボードのご紹介株式会社アイエルアイ総合研究所内藤慶一情報系システム開発メンテナンス開発イノベーションビジネスダッシュボード by Excel 概要実務業務マネジメントの知識技術経験のノウハウ + 業務データ分析の見える化共有化シミュレーションを可能としビジネスソリューションを推進する BI システムを実現します! Excel と StiLL + Xcelsius