form1.frm

一个vb做的使用数据库操作Excel的程序

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   3195
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   4680
   LinkTopic       =   "Form1"
   ScaleHeight     =   3195
   ScaleWidth      =   4680
   StartUpPosition =   3  'Windows Default
   Begin VB.TextBox txtTest 
      Height          =   375
      Left            =   480
      TabIndex        =   2
      Top             =   960
      Width           =   2535
   End
   Begin VB.TextBox Text1 
      Height          =   345
      Left            =   120
      TabIndex        =   1
      Top             =   90
      Width           =   2085
   End
   Begin VB.CommandButton Command1 
      Caption         =   "Command1"
      Height          =   525
      Left            =   240
      TabIndex        =   0
      Top             =   2070
      Width           =   1245
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Dim TmpRs As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim bChange As Boolean

Private Sub Command1_Click()
    Dim XlsApp As New Excel.Application
    Dim XlsBook As New Excel.Workbook
    Dim XlsSheet As New Excel.Worksheet
    Dim i%
    
    
    Set XlsApp = CreateObject("Excel.Application")
    Set XlsBook = XlsApp.Workbooks.Open("D:\项目文档\会计核算系统\会计科目.xls")
    Set XlsSheet = XlsBook.Worksheets(1)
    
    For i% = 3 To 376
        If (i% - 2) Mod 20 <> 0 Then
            XlsSheet.Cells(i%, 4) = (i% - 2) \ 20 + 1
        Else
            XlsSheet.Cells(i%, 4) = (i% - 2) \ 20
        End If
    Next i%
    XlsBook.Close True
    XlsApp.Quit
    MsgBox "OK"
End Sub

Private Sub Form_Load()
    Conn.CursorLocation = adUseClient
    Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=tfitatgd;Persist Security Info=True;User ID=sa;Initial Catalog=tempdb;Data Source=shranlove"
    Conn.Open
    
    'Conn.ConnectionString = "Provider=MSDAORA.1;Data Source=topunion;Persist Security Info=True"
    'Conn.Open , "caiwu", "caiwu"
'    TmpRs.Open "select km_CName From KM order by km_code", Conn
    'MsgBox trs.RecordCount
    bChange = False
End Sub

Private Sub Text1_Change()
    Dim TmpRs As New ADODB.Recordset
    Dim StrText$, StrName$

    If bChange = True Then Exit Sub
    If Trim(Text1.Text) = "" Then Exit Sub
    StrText = Text1.Text
    TmpRs.Open "select name From sysusers where name like '%" & Trim(Text1.Text) & "%'", Conn
    If Not ((TmpRs.RecordCount = 0) Or (TmpRs.RecordCount > 1)) Then
        bChange = True
        StrName$ = TmpRs.Fields("name")
        Text1.Text = TmpRs.Fields("name")
        Text1.SelStart = InStr(1, StrName$, StrText$) - 1
        Text1.SelLength = Len(StrText)
    End If
    End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
    bChange = False
End Sub