Free Microsoft Excel 2013 Quick Reference

MISSING: Microsoft Windows Common Controls-2 6.0

I have windows XP and Excel 2003. In Visual Basic Editor, References, I have missing the Microsoft Windows Common Controls-2 6.0. How can I enable it?
When opening the file, excel prompts Error compiling some modules. Opening the file in another pc with this common controls enabled, it works fine.

Thanks a lot fot the help.

Post your answer or comment

comments powered by Disqus
I have a pop-up calendar in a workbook but when I try to activate it, it errors. In the Tools - References it states the following:
Missing: Microsoft Windows Common Controls-3 6.0 and it refers to
comct332.ocx in Windows-System32.
I checked in the System32 folder and there I have comct232.ocx but not comct332.ocx.
Searching for this on the windows site references to a VB6Cli.exe program as follows: VB6Cli.exe is a utility that fixes design-time licenses used by ActiveX controls installed with Microsoft Visual Basic 6.0.
I have searched the Microsoft site to download either files (VB6Cli.exe and comct332.ocx) but no luck.
I have Microsoft Visual Basic 6.3 and WinXP Professional.
Any help on this matter is greatly appreciated
Thanks and regards.

as my title says I have just got a dell xps with windows 64bit and installed office 2010 64bit. Now my VBA macros in excel willnot work.
I have regestered MSCOMCT2.OCX for 32bit and 64bit without any problem but in vba toolbox Microsoft Common Controls-2 6.0 is not available.
I have searched the internet and tried all suggestions without success. Cna anybody please help me,



Good Morning: I just updated my office program from 2000 to xp. Now my macros don't want to work. I keep getting messages " not in library". I checked Tools>References & found a check box checked but it says "Missing - Microsoft Windows Common Control - 26.0 (SP4)". In my previous version of Office, this was checked & installed. Does anybody know where can I find this control & install it?

I have written a userform. My system crashed and had to be reformatted.
After installation, I started Excel checked ref under VB editor. I was
missing 2
"missing: microsoft windows common controls-2 6.0 (SP4)"
and also mccomctl.dll

I have copied the first file from another system and it seemed to like it, I
no longer got the missing on the mscomct2.ocx. I also reinstalled McAfee
(the supplier of the the second one.

I now have now "missing" entries, however I do get a "compile error" "method
or data member not found" and it pops to an entry with a DTPicker varible
..DTPicker78 = ActiveCell.Offset(0, 77)
This had worked before. I tried it back on the system I have been using for
a backup, and it is NOT working anymore either! I did NOT make any code
changes etc....

On a side note, when I go into the editor becuase of a trap, how can I get
to the referances? I have been exiting out of the editor and then get a box
that says "this command will stop the debugger" and then after returning to
excel, reenter the editor. How else can I do this?


I'm trying to create an input form that contains a Date and time picker
(DTPicker) to be distributed to different versions of Excel. I am using
excel 2000, however when I open it in Excel XP I get the following error
Compile Error
Can't find project or library

And in Tools>References I get Missing: Microsoft Windows Common

Is there an easy alternative instead of the DTPicker or some code to fix this?
I don't have control over other users having any particular add-ins/updates
applied unfortunately, or even what version of Excel or what install options
were chosen.

Any help would be much appreciated.


I am desperate with DTPicker: it used to work, I have several worksheets that use it, but all of a sudden it has stopped working for me.

When I insert the control on a blank worksheet (Developer, Insert, More Controls, Microsoft Date and Time Picker Controls 6.0 SP6), I get a message with "Cannot Insert Object".

Similarly, when I try to place it on a Userform, I receive an "Invalid argument".

The Activex control seems to be well registered: there is a "Microsoft Windows Common Controls-2 6.0 (SP6)" on the list of References and it is checked, location is c:windowssystem32MSCOMCT2.OCX

I have re-registered the control (regsvr32 c:windowssystem32MSCOMCT2.OCX) but nothing changes. I even got a different version of the control (SP4 instead of SP6) and tried registering, nothing changes.

I'm using Excel 2007 and Vista.

What else could I try?


In my work office (XP professional) my vba code works perfectly,
however at home (XP home edition) with a number of actions (like
entering a new name for a cell) I get the message that the
"Microsoft Windows Common Controls 2-6.0 SP-4" is absent in the
library, and that I should add

I did some preliminary research and found that the reason could be the
calendar control I included in the file. (all the other controls are
very basic from the standard toolbox, like command buttons and combo
I don't want to have any exotic controls in my file, because my file
will be used by people worldwide and I don't want error risks. That's
why I removed this calendar control. However, the problem persists,
with certain actions it still keeps asking for this library file, and
even none of the controls on the sheet which included the calendar
control works anymore.

Please some advice on how to permanently remove any calendar control


I'm converting my vba code from v2003 to v2010. However, some computers got failed when running this macro. (actually, seems only my computer can run it successfully).

example of the failu
Cells(n, ui_SmallTools.lb_OCR_wf.Listindex + 2) = Mid(Cells(n, m + 1), 1, 5)

==> the 'Mid' function is not work. After checked more, the 'Left' also failed.

My IT helped to check the problems, but did have me the solution. May I know anyone can help?

Project References:
These references (Libraries and Controls) may not be 64-bit compatible.
You may need to verify their compatibility. For more information see:
1) Microsoft Scripting Runtime, [Scripting], {420B2830-E718-11CF-893D-00A0C9054228}, C:WINDOWSsystem32scrrun.dll
2) Microsoft Windows Common Controls-2 6.0 (SP3), [MSComCtl2], {86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}, c:windowssystem32mscomct2.ocx

also checked the microsoft site. 'left' 'mid' functions were 'Hidden'.
In these tables, the status value for a given object model member may be any of the following:
ChangedóThe syntax for the member has changed.
HiddenóThe member has been deprecated.
RemovedóThe member has been removed from the object model.

Many thinks. Danny.

I've recently found out that the Microsoft Windows Common Controls 2 6.0 (sp6) is not installed with Office 2007 as standard and was wondering if theres any alternative to the datepicker on a userform that i can use that DOES come as standard.

I find it annoying how such an important and common control would not be included as a standard control.

So I'm posting again because the first time I submitted it didn't actually submit...but in the meantime I have found the offending situation.
Basically, last wednesday I finally installed my windows updates (windows 7 entriprise N, running office 2007). After the reboot, I took off for vacation. came back today and went to open up one of my macro-enabled workbooks, and it blew up with "Object library invalid or contains references to object definitions that could not be found."

Now, my references are all the standard ones, I only added : Microsoft Windows Common Controls 2 6.0 (SP6). I added this (MSCOMCT2.OCX) for the DTPicker which I couldn't find anywhere else. After testing, I found it was the "Microsoft StatusBar Control, version 6.0" that was the control blowing up my project. How/Why would a Windows Update explode this functionality? I didn't even install MSCOMCTL.OCX, that was part of windows. I did have to download and install MSCOMCT2.OCX for the DTPicker, but my code works just fine without the statusbar, but now when I try to add the status bar: A) Go to "Additional Controls", select "Microsoft StatusBar Control, version 6.0". It shows up in the control toolbox. B) Grab the statusbar icon and drop it on the UserForm. "Element Not Found" and it doesn't add it.

What's up with this? How do I resolve it?
Jaeden "Sifo Dyas" al'Raec Ruiner

I'd like to have XP styles in my Excel forms.

I'm following some instructions from VbAccelerator with no luck. I put this code in a module:
Private Type tagInitCommonControlsEx
lngSize As Long
lngICC As Long
End Type

Private Declare Function InitCommonControlsEx Lib "comctl32.dll" _
(iccex As tagInitCommonControlsEx) As Boolean

Private Const ICC_USEREX_CLASSES = &H200

Public Function InitCommonControlsVB() As Boolean
On Error Resume Next
Dim iccex As tagInitCommonControlsEx
' Ensure CC available:
With iccex
.lngSize = LenB(iccex)
End With
InitCommonControlsEx iccex
InitCommonControlsVB = (Err.Number = 0)
On Error GoTo 0
End Function

I put this manifest in the same directory as Excel.exe and named is Excel.exe.manifiest.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
type="win32" />
<description>Your application description here</description>
language="*" />

Does anyone know how to do this? Can this also be done with 2003 styles?

Appreciate any assistance.

Does anybody know if it is possible to change the background colour of
the "date box" of a Microsoft Date and Time Picker 6.0 COLOUR - by
"date box" I mean the combobox type object that you see when you this
control does not have the focus

(I know how to change the background of the dropdown calender, it's
just the box that returns the date selected, that I'm having fun with)

Any help greatly appreciated.

Hi there

I have a workbook which I send to a number of people my problem is not everyone has the SP3 installed on their computers which doesn't allow them to use a certain userform.

I know that I could explain for them to go into VBA select-references and then find the missing reference and unselect it but I have a password on the code which I don't wish to giveout to everyone.

What can I do to fix this problem??

The missing reference by the way is
Microsoft Windows Common Controls-2 (SP3).

Any help would be great

I am having problems with a line of code in newer-build pcs. The library
error refernce is as above, but this is the first occurrence of the code:

Set HiddenRange = Worksheets("Hidden").Range("A1")

I have seen reference to this type of message in other questions, but
usually related to obvious library elements.

This development is very worrying as a key system is involved. Current work
around is to use the Terminal server, but I caanot be sure that some update
on that will kill the system. Any ideas, please?


I have problem. I want to use microsoft windows common controls 6.0 (sp6) activex controls in excel 2002. it is not available in the toolbox list. i tried using register custom control option in toolbox to register this ocx file. i did not
get any error msg. but when i go again and see the list
it is not listed there.

can anybody help ??? :



Hi Jim

I would also be very interested it is a brilliant control - I am using Excel
2003 on my home PC I have the control and on my work PC I do not. Points of
note though - the object lives in the System32 folder and Microsoft have a
download (as I am sure your aware) but how could you deploy the control over
a network without visiting every single computer given the users would not
have access rights to add to a system folder?
Also, given the difficulties of working with dates (Well I have problems -
anyway!) why did Microsoft remove the control in the first place? Available
in 97 I believe.

Mark (InWales)

"Jim in Spokane" > wrote in message
> Wanted to add the error is missing this object: MSCOMCT2.OCX
> "Jim in Spokane" wrote:
>> I am using the Date Time Picker to select calendar dates using a VB form
>> Excel 2003
>> It works great for me, however when I send the Excel File to other users
>> who
>> do not have Excel XP or 2003, they do not have the ActiveX Controls to
>> make
>> it work.
>> Is there an easy way to point them to a link, where they can download the
>> ActiveX Controls necessary for the Date Time Picker?
>> Thanks

A useform that was produced on one machine won't load on another. The message that pops up is "Could not load some objects because they are not available on this machine". It is referring to a datepicker control.

I think this is to do with a missing reference. I had a look at the references and there is a missing item called "Microsoft Windows Common Controls" (see picture). Any ideas what I need to do to fix this?


I am using Office 2000+updates on Windows 2003+updates, and I had this

I created a user form in Excel VBE, and added the Microsoft ADO Data
Control, version 6.0 to the toolbox. When I attempt to add the control to the
form, I get a Visual Basic message that says the control can't be created
because it is not properly licensed. The control is maadodc.ocx.

I am trying to connect Excel to the SQL Server Data Engine. Do I need this
control, and if not, what can replace it? If I do need this control, how do I
get the control?

Any help would be appreciated. Thanks.

I am trying to run an Excel based program that works on Excel 2002 but not on
Excel 2003 Professional or Excel 2003 Small Business; the error message says
cannot find project or library. The missing reference appears to be
Microsoft Windows Common Controls 5.0 (SP2) (comctl32.ocx). Any suggestions
on how to obtain this or information on what replaced it would be appreciated.

Hi there

I wanted to add a calendar to an Excel spreadsheet but I can't seem to find
the Microsoft Date and Time Picker Control 6.0 in the ACTIVE X control in the
Developer Tab,

Where do I find it?

Thank you


I tested the following VBA-code (see below) in excel 2000 on Windows XP. And it works.

The code(see below) is used in a userform called CalendarUserForm (height 216 points, width 170
points) with
two CommandButtons (OKButton, CancelButton) on the userform. On the userform, the code creates
a month-calendar-control using the WIN-API.

I know i could use the Microsoft MonthView Control ActiveX-object in MSCOMCT2.OCX.
But it is difficult to distribute and install these ocx-files on other systems.

info about the month-calendar-control:

I have a few questions about the code(see below):

1) Is this the correct way to create and use the month-calendar-control in VBA?
Is it correct code in general? Any remarks, comments about it?

I know i could use the Microsoft MonthView Control ActiveX-object in MSCOMCT2.OCX.
But it is difficult to distribute and install this ocx-files on other systems.

2) The month-calendar-object sends notification messages (MCN_SELCHANGE,
MCN_SELECT, MCN_GETDAYSTATE) to the parent(the userform) when it receives
user input. Currently, I ignore these messages. I do not process these messages.
Should i process these messages or is it safe not to process them?

3) What about destroying the month-calendar-object in UserForm_Terminate()?
Is it correct to send a WM_CLOSE-message to the object before using the DestroyWindow-call?
Or should I only perform the DestroyWindow-call ?

4) I use InitCommonControlsEx(). (A WIN-API-function).It registers the common control classes.
Is it safe the call this function several times, because it is called everytime ShowResult is
Is there an opposite function which unregisters the common classes?
Something like UnInitCommonControlsEx



the code from CalendarUserForm.frm

Option Explicit

' private variables

Private cal_result As VbMsgBoxResult ' return-value of ShowResult
Private cal_selected_date As Date ' current selected-date
Private cal_control_handle As Long 'handle to month-calendar-control

' const month-calendar-control
Private Const MCM_GETCURSEL As Long = &H1001
Private Const MCM_SETCURSEL As Long = &H1002
Private Const MCM_SETTODAY As Long = &H100C

Private Const ICC_DATE_CLASSES As Long = &H100
Private Const MONTHCAL_CLASS As String = "SysMonthCal32"

' windows-message
Private Const WM_CLOSE As Long = &H10

' const style
Private Const WS_CHILD As Long = &H40000000
Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_BORDER As Long = &H800000

dwSize As Long
dwICC As Long
End Type

wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Declare Function MonthCal_GetCurSel Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As SYSTEMTIME) As Long

Private Declare Function MonthCal_SetCurSel Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As SYSTEMTIME) As Long

Private Declare Function InitCommonControlsEx Lib "comctl32" _

Private Declare Function CreateWindowEx Lib "user32" _
Alias "CreateWindowExA" _
(ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal x As Long, ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hwndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) As Long

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hwndParent As Long, _
ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, _
ByVal lpszWindow As String) As Long

Private Declare Function DestroyWindow Lib "user32" (ByVal hWnd As Long) As Long

Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Private Declare Function PostMessage Lib "user32" _
Alias "PostMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

' -------------------------------------------------------------------------------------

' Shows userform after it created the month-calendar-control
' the function returns when the user clicks on the OK-button or Cancel-button
' (SelectedDate is the date selected in the month calendar object)

Public Function ShowResult(ByRef SelectedDate As Date) As VbMsgBoxResult
On Error GoTo errorhandler

' strip time-part of the selected date
SelectedDate = DateSerial(Year(SelectedDate), Month(SelectedDate), Day(SelectedDate))

OKButton.Enabled = False

cal_result = vbCancel ' returned when you use the upperright Close-button
cal_selected_date = SelectedDate
cal_control_handle = 0&

' determine handle to userform
Dim prevCaption As String
Me.Caption = CStr(Timer())

Dim userform_handle As Long
If Val(Application.Version) < 9 Then
userform_handle = FindWindow("ThunderXFrame", Me.Caption) 'XL97
userform_handle = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
End If

Me.Caption = prevCaption

If userform_handle 0& Then

' determine handle to parent of month calendar control
' the parent is the first child of the userform

Dim parent_handle As Long
parent_handle = FindWindowEx(userform_handle, 0&, vbNullString, vbNullString)

If parent_handle 0& Then

initCtrlsStruct.dwICC = ICC_DATE_CLASSES
initCtrlsStruct.dwSize = Len(initCtrlsStruct)

If InitCommonControlsEx(initCtrlsStruct) 0 Then

' create month calendar control

cal_control_handle = CreateWindowEx(0, MONTHCAL_CLASS, vbNullString, _
WS_BORDER Or WS_CHILD Or WS_VISIBLE, 10, 10, 200, 200, _
parent_handle, 0&, 0&, ByVal 0&)

If cal_control_handle 0 Then
OKButton.Enabled = True
End If
End If
End If
End If

' show the userform with the month-calendar-object
' wait untill form is hidden

ShowResult = cal_result

If ShowResult = vbOK Then
SelectedDate = cal_selected_date
End If

Unload Me

Exit Function

ShowResult = vbCancel
Unload Me
End Function

' set cal_result and hide userform

Private Sub CancelButton_Click()
cal_result = vbCancel
End Sub

' determine current selected date
' if it succeeds to determine, set cal_result to vbOk and hide the userform

Private Sub OKButton_Click()
On Error GoTo errorhandler

If cal_control_handle 0& Then
Dim selected_system_time As SYSTEMTIME

If MonthCal_GetCurSel(cal_control_handle, MCM_GETCURSEL, 0&, _
selected_system_time) 0 Then

' strip time-part of current selected date

With selected_system_time
.wHour = 0
.wMinute = 0
.wSecond = 0
End With

cal_selected_date = SystemTimeToDate(selected_system_time)

If CDbl(cal_selected_date) >= 0 Then
cal_result = vbOK
End If
End If
End If

Exit Sub

cal_result = vbCancel
End Sub

' close month calendar control
' destroy month calendar control

Private Sub UserForm_Terminate()
On Error Resume Next

If cal_control_handle 0& Then
SendMessage cal_control_handle, WM_CLOSE, 0&, ByVal 0&
DestroyWindow cal_control_handle

cal_control_handle = 0&
End If
End Sub

' event is fired when you use the upperright Close-button

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error Resume Next

' prevent unload !
' just hide it

If CloseMode = vbFormControlMenu Then
cal_result = vbCancel
Cancel = 1
End If
End Sub

Private Function SystemTimeToDate(ByRef system_time As SYSTEMTIME) As Date
With system_time
SystemTimeToDate = DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With
End Function

Private Function DateToSystemTime(ByVal Date_Value As Date) As SYSTEMTIME
Dim system_time As SYSTEMTIME

With system_time
.wYear = Year(Date_Value)
.wMonth = Month(Date_Value)
.wDayOfWeek = Weekday(Date_Value, vbSunday) - 1
.wDay = Day(Date_Value)
.wHour = Hour(Date_Value)
.wMinute = Minute(Date_Value)
.wSecond = Second(Date_Value)
.wMilliseconds = 0
End With

DateToSystemTime = system_time
End Function

An example of how the userform CalendarUserForm is called:
(SelectedDate is the date selected in the month calendar object)

Public Function ShowCalendar(ByRef SelectedDate As Date) As VbMsgBoxResult
On Error GoTo errorhandler

ShowCalendar = CalendarUserForm.ShowResult(SelectedDate)

Exit Function


ShowCalendar = vbCancel
End Function

Here is an example that show how we can both automate nearly the whole MS Office-suite and exchange data between the programs.

Developing plattform:
Windows 2000 SP-4
MS Visual Basic Enterprise 6.0 SP-6
MS Excel 2003
MS Word 2003
MS Outlook 2003
Jet Database
MDAC 2.5

How the example work
First it import data from a Jet Database to an Excel workbook. In the workbook there is two predefined ranges where one is the target-range for the dataimport.

Then the table is copied to a report in MS Word that is added to an outgoing e-mail in MS Outlook.

The example can be developed further, like asking the user for some comments on the report and also an error-handling.

But it is up to You to develop it further.

Don't forget to set references to all the programs involved as well as ADO.

The full code:

Private Sub Command1_Click() 
     'Variables for ADO/Jet-Database.
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stSQL As String, stConn As String 
     'Variables for MS Excel.
    Dim xlApp As Excel.Application 
    Dim xlWBook As Excel.Workbook 
    Dim xlWSheet As Excel.Worksheet 
    Dim xlRReport As Excel.Range, xlRData As Excel.Range 
     'Variables for MS Word.
    Dim wdApp As Word.Application 
    Dim wdDoc As Word.Document 
    Dim rbmReport As Word.Range 
     'Variables for MS Outlook.
    Dim olApp As Outlook.Application 
    Dim olNewMail As Outlook.MailItem 
     'Instantiate the MS Excel-objects.
    Set xlApp = New Excel.Application 
    Set xlWBook = xlApp.Workbooks.Open(App.Path & "Week.xls") 
    Set xlWSheet = xlWBook.Worksheets("Weekreport") 
    With xlWSheet 
         'Here we use a named range which holds the whole reporttable.
        Set xlRReport = .Range("Report") 
         'Here we use a named range that holds the data from the database.
        Set xlRData = .Range("Data") 
    End With 
     'Instantiate the ADO-objects.
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
    stConn = "Microsoft.Jet.OLEDB.4.0;" _ 
    & "Data Source=" & App.Path & "DB1.mdb;" 
    stSQL = "SELECT TOP 5 * FROM Production_E1 ORDER BY Prod_Output DESC" 
     'Open the connection.
    cnt.Open stConn 
     'Open the Recordset.
    rst.Open stSQL, cnt 
     'Copy the recordset to the table in Week.xls.
    xlRData.CopyFromRecordset rst 
     'Release ADO-objects from memory.
    Set rst = Nothing 
    Set cnt = Nothing 
     'Instantiate the MS Word-objects
    Set wdApp = New Word.Application 
    Set wdDoc = wdApp.Documents.Open(App.Path & "WeekReport.doc") 
     'When executing the procedure for the first time there exist no object to delete.
    On Error Resume Next 
     'Here we assume that the table-object has the index-number 1.
    With wdDoc 
        .Unprotect Password:="XL-Dennis" 
        With .InlineShapes(1) 
        End With 
    End With 
    On Error Goto 0 
     'The bookmark in the Word-document is named XLReport.
    Set rbmReport = wdDoc.Bookmarks("XLReport").Range 
     'Here we copy the Reporttable in the worksheet.
     'The table is copied as a metafile-picture.
    With rbmReport 
        .PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _ 
        Placement:=wdInLine, DisplayAsIcon:=False 
    End With 
     'Activate the document-protection, save and close the Word-file.
    With wdDoc 
        .Protect wdAllowOnlyReading, , "XL-Dennis" 
        .Close SaveChanges:=True 
    End With 
     'Close MS Word.
     'Release objects from the memory.
    Set rbmReport = Nothing 
    Set wdDoc = Nothing 
    Set wdApp = Nothing 
     'Close and save the Excel-workbook.
    xlWBook.Close SaveChanges:=True 
     'Close MS Excel.
     'Release objects from the memory.
    Set xlRData = Nothing 
    Set xlRReport = Nothing 
    Set xlWBook = Nothing 
    Set xlApp = Nothing 
     'Instantiate MS Outlook-objects.
    Set olApp = New Outlook.Application 
    Set olNewMail = CreateItem(olMailItem) 
     'Add values to the properties of the created mail and attach
     'the report to the outgoing mail.
    With olNewMail 
         'We assume that all the recipients have been registrated
         'in the contacts including the e-mailaddresses.
        .Recipients.Add "XL-Dennis" 
        .CC = "Groupteam" 
        .BCC = "CEO" 
        .Subject = "Weekly report" 
        .Body = "Report as per agreement" 
        With .Attachments 
            .Add App.Path & "WeekReport.doc" 
            .Item(1).DisplayName = "Weekly report" 
        End With 
    End With 
     'Release objects from the memory.
    Set olNewMail = Nothing 
    Set olApp = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As usual comments are most welcome

Enjoy coding!

Was puzzled why the code only takes out the data from the template.xls, bk sheet, and bypass the other 2 sheets(mac and 711)? Anyone can help me out here?

If box1 = 1 Then
    Dim wb As Workbook, ws As Worksheet, i As Integer
    Set wb = Workbooks.Open(ThisWorkbook.Path & "template.xls")
    irows = 1
        Dim intRow As Integer
        intRow = 2
    ThisWorkbook.Sheets("sheet1").Cells = ""
    For Each ws In wb.Worksheets
        i = i + 1
        ThisWorkbook.Sheets("define").Range("A" & 1 + i).Value =
        Do While ws.Cells(intRow, 1).Value  ""
                cusid = ws.Cells(5 + intRow, 1).Value
                cus = ws.Cells(5 + intRow, 2).Value
                ThisWorkbook.Sheets("sheet1").Range("a" & intRow).End(xlUp).Offset(1, 0).Resize(, 3) = Array(, cusid,
        intRow = intRow + 1
        Loop 'end of do-while
    Next ws
    wb.Close savechanges:=False

End If

output(only bk shown, others are missing!)
******** ******************** ************************************************************************>Microsoft Excel - protoV6.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC7=
ABCD1    2bkBK1Burger Store 1 Bedok 3bk   4bkBK2Burger Store 2 Tampines 5bk   6bkBK3Burger Store 3 Jurong 7bk   Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

******** ******************** ************************************************************************>Microsoft Excel - template.XLS___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC16=
ABCD1Order Date   2Delivery Date   3Posting Date   4Unit Price   5Item No RFG-**510RFG-**5086Product Name Chicken Roll & CheeseCalifornia Tuna7BK1Burger Store 1 Bedok  8  KGKG9BK2Burger Store 2 Tampines  10  KGKG11BK3Burger Store 3 Jurong  12  KGKG13BK4Burger Store 4 Sengkang  14  KGKG15BK5Burger Store 5 Kranji  16  KGKGbk 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

******** ******************** ************************************************************************>Microsoft Excel - template.XLS___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC34=
ABCD1Order Date   2Delivery Date   3Posting Date   4Unit Price   5Item No RFG-**510RFG-**5086Product Name Chicken Roll & CheeseCalifornia Tuna7MC1Mac 1 Plaza Singapura  8  KGKG9MC2Mac 2 Marina Square  10  KGKG11MC3Mac 3 Jurong Point  12  KGKG13MC4Mac 4 Yishun  14  KGKG15MC5Mac 5 Far East Plaza  16  KGKG17BB01BB_JURONG  18  KGKG19BB02BB_BEDOK  20  KGKG21BB03BB_TAMPINES  22  KGKG23SW1Sweetz 1 Orchard  24  KGKG

I am trying to save my self time by not manually entering in the same values in the previous cell. I would like to be able to have the formula give me the current value of the cell but if it is blank, I need it to give me the value of the cell above it. I hope my example explains it better.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC1C2C3C4C5C6C7C8C9C10C11C12C13C14=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

No luck finding an answer? You could always try Google.