VBA for Excel & Access

Friday, June 26, 2009

Excel Shortcut Keys

Key	Alone	Shift	Ctrl	Alt	Shift Ctrl
F1	Help	What's This Help		Insert Chart Sheet
F2	Edit Mode	Edit Comment		Save As
F3	Paste Name Formula	Paste Function	Define Name		Names From Labels
F4	Repeat Action	Find Again	Close Window	Quit Excel	Find Previous
F5	Goto	Find	Restore Window Size
F6	Next Pane	Prev Pane	Next Window	Previous Window	Prev Workbook
F7	Spell Check		Move Window
F8	Extend Selection	Add To Selection	Resize Window	Macro List
F9	Calculate All	Calculate Worksheet	Minimize Workbook
F10	Activate Menu	Context Menu	Maximize Window
F11	New Chart	New Worksheet	New Macro Sheet	VB Editor
F12	Save As	Save	Open		Print
A			Select All		Formula Arguments
B			Bold
C			Copy
D			Fill Down	Data Menu
E				Edit Menu
F			Find	File Menu	Font Name
G			Goto
H			Replace	Help Menu
I			Italics	Insert Menu
J
K			Insert Hyperlink
L
M
N			New Workbook
O			Open Workbook	Format Menu	Select Comments
P			Print		Font Size
Q
R			Fill Right
S			Save
T				Tools Menu
U			Underline
V			Paste
W			Close Workbook	Window Menu
X			Cut
Y			Repeat Active
Z			Undo
` (~)			Toggle Formula View		General Format
1 (!)			Cell Format		Number Format
2 (@)			Toggle Bold		Time Format
3 (#)			Toggle Italics		Date Format
4 ($)			Toggle Underline		Currency Format
5 (%)			Toggle Strikethru		Percent Format
6 (^)			Toggle Object Display		Exponent Format
7 (&)			Show/Hide Standard Toolbar		Apply Border
*8 ()**			Outline		Select Region
9 (()			Hide Rows		Unhide Rows
0 ())			Hide Columns		Unhide Columns
-			Delete Selection	Control Menu	No Border
= (+)	Formula		Calculate All	Auto Sum	Insert Cells
[			Direct Precendents		All Precendents
]			Direct Dependents		All Dependents
; (semicolon)			Insert Date	Select Visible Cells	Insert Time
' (apostrophe)			Copy Formula From Above	Style	Copy Value Above
: (colon)			Insert Time
/			Select Array		Select Array
\			Select Differences		Select Unequal Cells
Insert	Insert Mode		Copy
Delete	Clear		Delete To End Of Line
Home	Begin Row		Start Of Worksheet
End	End Row		End Of Worksheet
Page Up	Page Up		Previous Worksheet	Left 1 screen
Page Down	Page Down		Next Worksheet	Right 1 screen
Left Arrow	Move Left	Select Left	Move Left Area
Right Arrow	Move Right	Select Right	Move Right Area
Up Arrow	Move Up	Select Up	Move Up Area
Down Arrow	Move Down	Select Down	Move Down Area	Drop down list
Space Bar	Space	Select Row	Select Column	Control Box	Select All
Tab	Move Right	Move Left	Next Window	Next Application	Previous Window
Enter		Move Up	Fill Selection With Active Cell	Insert Row
BackSpace		Collapse Selection To Active Cell	Goto Active Cell

ICC World Twenty20 England '09 exclusively on YAHOO! CRICKET

Thursday, June 25, 2009

Code to open all files(excel) from a specific folder using comman dialog box.

'************** Code Start **************

'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'www.ammara.com

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
            "SHGetPathFromIDListA" (ByVal pidl As Long, _
            ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
            "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
            As Long

Private Const BIF_RETURNONLYFSDIRS = &H1

Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

    With bi
        .hOwner = hWndAccessApp
        .lpszTitle = szDialogTitle
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    dwIList = SHBrowseForFolder(bi)
    szPath = Space$(512)
    X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

    If X Then
        wPos = InStr(szPath, Chr(0))
        BrowseFolder = Left$(szPath, wPos - 1)
    Else
        BrowseFolder = vbNullString
    End If
End Function

Sub openAllfilesInALocation()

Dim i As Integer, wb As Workbook
fldname = BrowseFolder("Choose Folder For Import")

If Len(fldname) > 0 Then
        ' Do something with the selected folder
    Else

        MsgBox "No folder/Directory selected"
        End
    End If

With Application.FileSearch
.NewSearch
.LookIn = fldname
.SearchSubFolders = False
.Filename = "*.csv"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Perform the operation on the open workbook
Range("A1") = Date
'Save and close the workbook

wb.Close savechanges:=True
'On to the next workbook
Next i
End With

End Sub

'************** Code End **************

ICC World Twenty20 England '09 exclusively on YAHOO! CRICKET

Friday, June 19, 2009

Remove Missing items from pivot table in entire workbook.

'**********************************************
Author: Debra Dalgleish, Contextures
http://www.contextures.com/tiptech.html
http://blog.contextures.com/ '
'***********************************************
Sub DeleteMissingItemsAll()

'prevents unused items in non-OLAP PivotTables

Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'refresh all the pivot caches

For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub

Love Cricket? Check out live scores, photos, video highlights and more. Click here.

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!

Saturday, May 16, 2009

e-Books

Pages

Friday, June 26, 2009

Thursday, June 25, 2009

Friday, June 19, 2009

Friday, May 22, 2009

Thursday, May 21, 2009

Sunday, May 17, 2009

Saturday, May 16, 2009