Excel VB getting started private sub calendar#click ()
Range ("A1") = calendar1.value
End sub
Private sub calendar#dblclick ()
Range ("A1") = calendar1.value
Calendar1.visible = false
Range ("A1"). Select
End sub
Private sub worksheet_selectionchange (byval target as range)
If target. Address = "$ A $1" then
Calendar1.value = now ()
Calendar1.visible = true
Else
Calendar1.visible = false
End if
End sub
Bytes ---------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------- Excel Connect_DataBase (SQL Server) 'References Microsoft Activex Data Object 2.0 Library
Private Sub commandbutton#click ()
Dim xlsApp As Object
Dim Cnn As New ADODB. Connection
Dim Rs As ADODB. Recordset
Cnn. ConnectionString = "PROVIDER = SQLOLEDB; SERVER = 192.168.0.0; UID = xxx; PWD = xxx; DATABASE = HR_ST_STPS"
If Cnn. State <> ADODB. ObjectStateEnum. adStateClosed Then Cnn. Close
Cnn. Open
Set Rs = New ADODB. Recordset
With Rs
Set. activeconnection = CNN
. Cursorlocation = aduseclient
. Cursortype = adopenstatic
. Locktype = adlockreadonly
. Open "select * from [hr_st_stps]. [DBO]. [tblporgan]"
End
If Rs. EOF then exit sub
Set xlsapp = Createobject ("Excel. application ")
'Ans = msgbox ("Continue ?", Vbyesno)
'Xlsapp. Visible = true
Xlsapp. workbooks. Add
XlsApp. Sheets ("sheet1"). Select
XlsApp. ActiveSheet. Range ("A1"). CopyFromRecordset Rs
If xlsApp. ActiveWorkbook. Saved = False Then
XlsApp. ActiveWorkbook. SaveAs "C: \ Documents and Settings \ hp \ Desktop \ Test.xlsx"
MsgBox ("Save to: C: \ Documents ents and Settings \ hp \ Desktop \ Test.xlsx ")
End If
XlsApp. Quit
Rs. Close
Set Rs = Nothing
Set xlsApp = Nothing
End Sub
Bytes ---------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------- VB automatically selects cell content sub rngfindnext ()
Dim strfind as string
Dim RNG as range
Dim findaddress as string
Strfind = inputbox ("Enter the value to be searched :")
If trim (strfind) <> "" then
With sheet1.range ("B: B ")
Set RNG =. Find (what: = strfind ,_
After: =. cells (. cells. Count ),_
LookIn: = xlValues ,_
LookAt: = xlWhole ,_
SearchOrder: = xlByRows ,_
SearchDirection: = xlNext ,_
MatchCase: = False)
If Not Rng Is Nothing Then
FindAddress = Rng. Address
Do
Rng. Interior. ColorIndex = 6
Set Rng =. FindNext (Rng)
Loop While Not Rng Is Nothing And Rng. Address <> FindAddress
End If
End
End If
End Sub branch ---------------------------------------------------------------------------------------------------------------------
Excel Funtion= LOOKUP (100-A10, {0, 10, 20, 30; "A", "B", "C", "D "})
