Friday, May 22, 2009

Add column to access table using SQL in VBA


Sub Addcolumns_Table()

Dim strsql As String
strsql = "ALTER TABLE [tablename] ADD COLUMN Column_name varChar(25);"
CurrentDb.Execute strsql

End Sub

Create Update Query Using SQL in VBA excel


Sub Update_Table()

Dim strsql As String
 
strsql = "Update [Table_name] set [FieldName]=""FT"" where [FieldName]=""Full-Time"""
 
CurrentDb.Execute strsql
 
End Sub


Cricket on your mind? Visit the ultimate cricket website. Enter now!

Create Select Query Using SQL in VBA

Add Microsoft DAO 3.6 object librery...from Tools > Referance
 
Sub Create_Query()
Dim strsql As String
Dim myquery As DAO.QueryDef
 
Set myquery = CurrentDb.CreateQueryDef("Mytable")
 
strsql = "Select * from [TableNme] where [Fieldname]=""Criteria"""
 
myquery.SQL = strsql
myquery.Close
 
DoCmd.OpenQuery "Mytable"
 
End Sub


Explore and discover exciting holidays and getaways with Yahoo! India Travel Click here!

Thursday, May 21, 2009

Worksheet password cracker

Just Paste the code in VBA window and press F5
 
Sub PasswordBreaker()

Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
 

End Sub


Explore and discover exciting holidays and getaways with Yahoo! India Travel Click here!

Sunday, May 17, 2009

Web Query / Web import query

Sub import_web()

   With ActiveSheet.QueryTables.Add(Connection:= _
       "TEXT;http://www.nseindia.com/content/historical/DERIVATIVES/
2009/MAY/fo06MAY2009bhav.csv
" _
       , Destination:=Range("A1"))
       .Name = "fo06MAY2009bhav_1"
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .RefreshStyle = xlInsertDeleteCells
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .TextFilePromptOnRefresh = False
       .TextFilePlatform = 437
       .TextFileStartRow = 1
       .TextFileParseType = xlDelimited
       .TextFileTextQualifier = xlTextQualifierDoubleQuote
       .TextFileConsecutiveDelimiter = False
       .TextFileTabDelimiter = True
       .TextFileSemicolonDelimiter = False
       .TextFileCommaDelimiter = True
       .TextFileSpaceDelimiter = False
       .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1)
       .TextFileTrailingMinusNumbers = True
       .Refresh BackgroundQuery:=False
   End With
End Sub


Explore and discover exciting holidays and getaways with Yahoo! India Travel Click here!