codewala

code it

Excel – get week number from date

Task
When we create a time plan – sometimes the client wants to know in which week number the different phases of the project will start or end.

Problem
While creating the time plan had to refer the calendar for the week number.

Solution
Use the in-build excel function TRUNC.

 Syntax

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

where StartDate is the date whose week number is to be returned.

Advertisements

September 6, 2012 Posted by | Excel | 1 Comment

Exclude weekends from planned dates in Excel

Learnt something new today while working with excel. Details are given below.

Task
To create a detailed project plan.

Problem
While working on creating project plan in excel had to constantly refer the calendar to avoid adding the weekend/holidays into the plan. Also this task become cumbersome when the plan gets changed/updated.

Solution
Use the in-build excel function WORKDAY

What do WORKDAY function do?
Use this function to calculate a past or future date based on a starting date and a specified number of days.
The function excludes weekends and holidays and can therefore be used to calculate end/delivery dates.

Syntax
=WORKDAY(StartDate,Days,Holidays)

Note
For the WORKDAY function to work, the “Analysis ToolPak” add-in needs to be installed and should be active. To activate the add-in, go to Developer tab –> click on the “Add-ins” button –> tick the “Analysis ToolPak” checkbox –> click ok.

July 30, 2012 Posted by | Excel, VBA | , | Leave a comment

Excel tips

To get current row and column of the selected cell
ActiveCell.Row
ActiveCell.Column

To get the last filled row
Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

To get hyperlink address from a cell
Range(“A1”).Hyperlinks(1).Address

 

June 11, 2011 Posted by | Excel, VBA | Leave a comment

Extract embeded Flash file from MS-Office documents

Found a nice code from web on how to extract .SWF file from a scrap file.
Steps on how to extract the embeded Flash File from MS-Office Documents.

1. Select and copy the embbeded flash file from the document.

2. Paste it in a folder. A scrap file will be created.

3. Paste the VBA code given below in Excel and run it. It will ask for the scrap file. Select it and click ok. The Flash will be extracted on the same path where the scrap file is.


'------------------------------------------------------
Sub ExtractFlashFromScrapFile()

Dim tmpFileName As String
Dim FileNumber As Integer
Dim myFileId As Long
Dim MyFileLen As Long
Dim myIndex As Long
Dim swfFileLen As Long
Dim i As Long
Dim swfArr() As Byte
Dim myArr() As Byte

tmpFileName = Application.GetOpenFilename(“Select Scrap File (*.*), *.*”, , “Select Scrap File”)

If tmpFileName = “False” Then Exit Sub

myFileId = FreeFile

Open tmpFileName For Binary As #myFileId

MyFileLen = LOF(myFileId)

ReDim myArr(MyFileLen – 1)

Get myFileId, , myArr()

Close myFileId

Application.ScreenUpdating = False

i = 0

Do While i < MyFileLen

If myArr(i) = &H46 Then

If myArr(i + 1) = &H57 And myArr(i + 2) = &H53 Then
MsgBox “&H46”

swfFileLen = CLng(&H1000000) * myArr(i + 7) + CLng(&H10000) * myArr(i + 6) + CLng(&H100) * myArr(i + 5) + myArr(i + 4)

ReDim swfArr(swfFileLen – 1)

For myIndex = 0 To swfFileLen – 1
swfArr(myIndex) = myArr(i + myIndex)
Next myIndex
Exit Do

Else
i = i + 3
End If

Else
i = i + 1
End If

Loop

myFileId = FreeFile

tmpFileName = Left(tmpFileName, Len(tmpFileName) – 4) & “.swf”

Open tmpFileName For Binary As #myFileId

Put #myFileId, , swfArr

Close myFileId

MsgBox “Save the extracted SWF Flash as [ ” & tmpFileName & ” ]”

End Sub

‘——————————————————

December 26, 2010 Posted by | Excel, VBA | Leave a comment