Selva’s Blog « Selva’s Site

Add Label to Userform Programmatically

November 29, 2008 at 22:55 · Filed under Automation, Userforms, VBA Advanced, VBA Examples

Consolidating Data from >20 workbooks, each workbook Containing >70 worksheets

November 17, 2008 at 01:15 · Filed under Automation, VBA, VBA Code Library, VBA Examples

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Option Explicit
‘___________________________________________________________________________
‘***************************************************************************
‘*
‘* MODULE NAME:     Copy Data from all Excel Files in a Folder
‘* AUTHOR:          Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
‘*
‘* CONTACT:         socko@rediffmail.com
‘* WEB SITE:        http://socko.wordpress.com
‘*
‘* DESCRIPTION:     The following visual basic code was written when
‘*                  I wanted to consolidate the data from 26 workbooks
‘*                  and in each workbook, there were are 77 sheets. It
‘*                  would have been a very difficult task, if I had to
‘*                  do it manually. I believe this procedure will be
‘*                  useful to somebody else
‘* NOTES:
‘*  Before using the codes, you can change all the variables like,
‘*  Folder, File , SearchString, SearchSubfolders (true / false),
‘*  and other variables.
‘*————————————————————————
‘* Other Information
‘*
‘* UPDATES:
‘*  DATE            COMMENTS
‘*  16 Nov 2008     You are free to use , change, and modify this code.
‘___________________________________________________________________________
‘***************************************************************************

Function ListFiles(sCount As Integer, ByVal sFldr As String, _
bFldr As Boolean, sFileName As String) As String
Dim fCnt As Integer

    With Application.FileSearch
        .NewSearch
        .LookIn = sFldr
        .SearchSubFolders = bFldr
        .Filename = sFileName             ‘”*consolidated*.xls”
        .FileType = msoFileTypeAllFiles

        If .Execute() > 0 Then

            If .FoundFiles.Count > sCount Then

              For fCnt = 1 To .FoundFiles.Count

                  If fCnt = sCount Then

                    ListFiles = .FoundFiles(fCnt)

                  End If

              Next i

            Else

            ListFiles = “”

            End If

        Else

            MsgBox “There were no files found.”

        End If

    End With

End Function

Sub Consolidate_Data()
Dim cFile As Integer
Dim sht, rw, cl, avLastRow As Long
Dim myFileName, myFolder, sFileName As String
Dim myFile As Workbook
Dim sFldr As Boolean

myFolder = “J:\GB Project\CONSOLIDATED”
sFldr = False
sFileName = “*consolidated*.xls”

With ThisWorkbook.Sheets(“data”)
  For cFile = 13 To 100
    myFileName = ListFiles(cFile, myFolder, True, sFileName)
    If myFileName = “” Then Exit Sub
      Set myFile = Workbooks.Open(myFileName)
      ThisWorkbook.Activate
      ThisWorkbook.Sheets(“data”).Activate
        For sht = 2 To myFile.Sheets.Count
          For rw = 1 To 100
            avLastRow = .Cells(65536, 1).End(xlUp).Row + 1
            ThisWorkbook.Sheets(“data”).Cells(avLastRow, 1).Activate
            .Cells(avLastRow, 1) = myFile.Sheets(sht).Name
            For cl = 1 To 18
            Application.StatusBar = “Writing File# ” & cFile & _
                    ”  Sheet# ” & sht
            .Cells(avLastRow, cl + 1) = myFile.Sheets(sht).Cells(rw, cl)
            Next cl
          Next rw
        Next sht
  myFile.Close False
  ThisWorkbook.Save
  Next cFile
End With
End Sub

Permalink Leave a Comment

Restrict entry into Textbox Control

November 26, 2008 at 14:40 · Filed under VBA Examples

- Selva V Pasupathy

When you are working with a userform to create a userinterface for data input, sometimes you would like to restrict the users to input numbers only in some of the textbox controls. However, many a times you would find accidently or otherwise, you find text entered into the database. You can use the following code to make sure that anything typed in textbox1 other than numbers 0 – 9 will not be accepted. Thus you can successfully implement to reduce errors in your database.

Private Sub TextBox1_KeyPress( _
      ByVal KeyAscii As MSForms.ReturnInteger)
  ‘ This code restricts any text in textbox1 other than
  ‘ integer 0 – 9 ….
  Select Case KeyAscii
    Case Asc(“0″) To Asc(“9″)

    Case Else
      KeyAscii = 0
  End Select
End Sub

Permalink Leave a Comment

TED: Ideas worth spreading

February 24, 2009 at 20:33 · Filed under Ideas, Links

by Selva V Pasupathy, HSBC Technology & Services, Hyderabad.
I incidentally happened to visit a www.ted.com and I found that it was wonderful. TED (Technology, Entertainment, Design) is an invitation-only event where the world’s leading thinkers and doers gather to find inspiration. I viewed some of the video’s by Tony Robbins, which were really good. I hope someone benefits from these links.

Permalink Leave a Comment

Add Control to CommandBars in WorksheetMenuBar

December 21, 2008 at 23:41 · Filed under Code Snippets, CommandBarControl ·Tagged ado Code Vba ComboBox createtable Create Table MDB vba

Sub AddControlToWorksheetMenuBar(ByRef NewCmdControl As String, _
              ByRef cmdBar As String, ByRef Face_ID As Long, _
              ByRef On_Action_Proc As String, _
              Optional ByRef Begin_Group As Boolean = False)
  ‘   Created by Selva V Pasupathy, Hyderabad
  ‘ This custom procedure adds a button control to commandbar
  ‘ Can be used to add a control to one of the worksheet menu bar
  ‘ where NewCmdControl = Caption for new CommandBarControl
  ‘ & NewCmdControl will be the name to that control
  ‘ Face_ID = one of the long integers for face_id

  Dim newItem As CommandBarControl

  With CommandBars(cmdBar)

    With .Controls(NewCmdControl)
      On Error Resume Next
      .Delete
      On Error GoTo 0
    End With

    Set newItem = .Controls.Add(Type:=msoControlButton)

    With newItem
      .BeginGroup = Begin_Group
      .Caption = NewCmdControl
      .FaceId = Face_ID
      .OnAction = “qtrReport”
    End With
  End With

End Sub

Permalink Leave a Comment

Use BuiltIn Dialogs in Excel

December 20, 2008 at 20:55 · Filed under VBA Examples

Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Rather than designing a userform for a routine tasks to be performed by a user on excel, we can use 200+ builtin dialogs provided by excel. The code would be as follows

Application.Dialogs(xlDialogSaveAs).Show

A tabel with various xlbuiltindialogs, constant and description is given below:

Name	Constant Value	Description
xlDialogActivate	103	Activate dialog box
xlDialogActiveCellFont	476	Active Cell Font dialog box
xlDialogAddChartAutoformat	390	Add Chart Autoformat dialog box
xlDialogAddinManager	321	Addin Manager dialog box
xlDialogAlignment	43	Alignment dialog box
xlDialogApplyNames	133	Apply Names dialog box
xlDialogApplyStyle	212	Apply Style dialog box
xlDialogAppMove	170	AppMove dialog box
xlDialogAppSize	171	AppSize dialog box
xlDialogArrangeAll	12	Arrange All dialog box
xlDialogAssignToObject	213	Assign To Object dialog box
xlDialogAssignToTool	293	Assign To Tool dialog box
xlDialogAttachText	80	Attach Text dialog box
xlDialogAttachToolbars	323	Attach Toolbars dialog box
xlDialogAutoCorrect	485	Auto Correct dialog box
xlDialogAxes	78	Axes dialog box
xlDialogBorder	45	Border dialog box
xlDialogCalculation	32	Calculation dialog box
xlDialogCellProtection	46	Cell Protection dialog box
xlDialogChangeLink	166	Change Link dialog box
xlDialogChartAddData	392	Chart Add Data dialog box
xlDialogChartLocation	527	Chart Location dialog box
xlDialogChartOptionsDataLabelMultiple	724	Chart Options DataLabel Multiple dialog box
xlDialogChartOptionsDataLabels	505	Chart Options DataLabels dialog box
xlDialogChartOptionsDataTable	506	Chart Options DataTable dialog box
xlDialogChartSourceData	540	Chart SourceData dialog box
xlDialogChartTrend	350	Chart Trend dialog box
xlDialogChartType	526	Chart Type dialog box
xlDialogChartWizard	288	ChartWizard dialog box
xlDialogCheckboxProperties	435	Checkbox Properties dialog box
xlDialogClear	52	Clear dialog box
xlDialogColorPalette	161	Color Palette dialog box
xlDialogColumnWidth	47	Column Width dialog box
xlDialogCombination	73	Combination dialog box
xlDialogConditionalFormatting	583	Conditional Formatting dialog box
xlDialogConsolidate	191	Consolidate dialog box
xlDialogCopyChart	147	Copy Chart dialog box
xlDialogCopyPicture	108	Copy Picture dialog box
xlDialogCreateList	796	Create List dialog box
xlDialogCreateNames	62	Create Names dialog box
xlDialogCreatePublisher	217	Create Publisher dialog box
xlDialogCustomizeToolbar	276	Customize Toolbar dialog box
xlDialogCustomViews	493	Custom Views dialog box
xlDialogDataDelete	36	Data Delete dialog box
xlDialogDataLabel	379	Data Label dialog box
xlDialogDataLabelMultiple	723	Data Label Multiple dialog box
xlDialogDataSeries	40	Data Series dialog box
xlDialogDataValidation	525	Data Validation dialog box
xlDialogDefineName	61	Define Name dialog box
xlDialogDefineStyle	229	Define Style dialog box
xlDialogDeleteFormat	111	Delete Format dialog box
xlDialogDeleteName	110	Delete Name dialog box
xlDialogDemote	203	Demote dialog box
xlDialogDisplay	27	Display dialog box
xlDialogDocumentInspector	862	Document Inspector dialog box
xlDialogEditboxProperties	438	Editbox Properties dialog box
xlDialogEditColor	223	Edit Color dialog box
xlDialogEditDelete	54	Edit Delete dialog box
xlDialogEditionOptions	251	Edition Options dialog box
xlDialogEditSeries	228	Edit Series dialog box
xlDialogErrorbarX	463	Errorbar X dialog box
xlDialogErrorbarY	464	Errorbar Y dialog box
xlDialogErrorChecking	732	Error Checking dialog box
xlDialogEvaluateFormula	709	Evaluate Formula dialog box
xlDialogExternalDataProperties	530	External Data Properties dialog box
xlDialogExtract	35	Extract dialog box
xlDialogFileDelete	6	File Delete dialog box
xlDialogFileSharing	481	File Sharing dialog box
xlDialogFillGroup	200	Fill Group dialog box
xlDialogFillWorkgroup	301	Fill Workgroup dialog box
xlDialogFilter	447	Dialog Filter dialog box
xlDialogFilterAdvanced	370	Filter Advanced dialog box
xlDialogFindFile	475	Find File dialog box
xlDialogFont	26	Font dialog box
xlDialogFontProperties	381	Font Properties dialog box
xlDialogFormatAuto	269	Format Auto dialog box
xlDialogFormatChart	465	Format Chart dialog box
xlDialogFormatCharttype	423	Format Charttype dialog box
xlDialogFormatFont	150	Format Font dialog box
xlDialogFormatLegend	88	Format Legend dialog box
xlDialogFormatMain	225	Format Main dialog box
xlDialogFormatMove	128	Format Move dialog box
xlDialogFormatNumber	42	Format Number dialog box
xlDialogFormatOverlay	226	Format Overlay dialog box
xlDialogFormatSize	129	Format Size dialog box
xlDialogFormatText	89	Format Text dialog box
xlDialogFormulaFind	64	Formula Find dialog box
xlDialogFormulaGoto	63	Formula Goto dialog box
xlDialogFormulaReplace	130	Formula Replace dialog box
xlDialogFunctionWizard	450	Function Wizard dialog box
xlDialogGallery3dArea	193	Gallery 3D Area dialog box
xlDialogGallery3dBar	272	Gallery 3D Bar dialog box
xlDialogGallery3dColumn	194	Gallery 3D Column dialog box
xlDialogGallery3dLine	195	Gallery 3D Line dialog box
xlDialogGallery3dPie	196	Gallery 3D Pie dialog box
xlDialogGallery3dSurface	273	Gallery 3D Surface dialog box
xlDialogGalleryArea	67	Gallery Area dialog box
xlDialogGalleryBar	68	Gallery Bar dialog box
xlDialogGalleryColumn	69	Gallery Column dialog box
xlDialogGalleryCustom	388	Gallery Custom dialog box
xlDialogGalleryDoughnut	344	Gallery Doughnut dialog box
xlDialogGalleryLine	70	Gallery Line dialog box
xlDialogGalleryPie	71	Gallery Pie dialog box
xlDialogGalleryRadar	249	Gallery Radar dialog box
xlDialogGalleryScatter	72	Gallery Scatter dialog box
xlDialogGoalSeek	198	Goal Seek dialog box
xlDialogGridlines	76	Gridlines dialog box
xlDialogImportTextFile	666	Import Text File dialog box
xlDialogInsert	55	Insert dialog box
xlDialogInsertHyperlink	596	Insert Hyperlink dialog box
xlDialogInsertObject	259	Insert Object dialog box
xlDialogInsertPicture	342	Insert Picture dialog box
xlDialogInsertTitle	380	Insert Title dialog box
xlDialogLabelProperties	436	Label Properties dialog box
xlDialogListboxProperties	437	Listbox Properties dialog box
xlDialogMacroOptions	382	Macro Options dialog box
xlDialogMailEditMailer	470	Mail Edit Mailer dialog box
xlDialogMailLogon	339	Mail Logon dialog box
xlDialogMailNextLetter	378	Mail Next Letter dialog box
xlDialogMainChart	85	Main Chart dialog box
xlDialogMainChartType	185	Main Chart Type dialog box
xlDialogMenuEditor	322	Menu Editor dialog box
xlDialogMove	262	Move dialog box
xlDialogMyPermission	834	My Permission dialog box
xlDialogNameManager	977	NameManager dialog box
xlDialogNew	119	New dialog box
xlDialogNewName	978	NewName dialog box
xlDialogNewWebQuery	667	New Web Query dialog box
xlDialogNote	154	Note dialog box
xlDialogObjectProperties	207	Object Properties dialog box
xlDialogObjectProtection	214	Object Protection dialog box
xlDialogOpen	1	Open dialog box
xlDialogOpenLinks	2	Open Links dialog box
xlDialogOpenMail	188	Open Mail dialog box
xlDialogOpenText	441	Open Text dialog box
xlDialogOptionsCalculation	318	Options Calculation dialog box
xlDialogOptionsChart	325	Options Chart dialog box
xlDialogOptionsEdit	319	Options Edit dialog box
xlDialogOptionsGeneral	356	Options General dialog box
xlDialogOptionsListsAdd	458	Options Lists Add dialog box
xlDialogOptionsME	647	OptionsME dialog box
xlDialogOptionsTransition	355	Options Transition dialog box
xlDialogOptionsView	320	Options View dialog box
xlDialogOutline	142	Outline dialog box
xlDialogOverlay	86	Overlay dialog box
xlDialogOverlayChartType	186	Overlay ChartType dialog box
xlDialogPageSetup	7	Page Setup dialog box
xlDialogParse	91	Parse dialog box
xlDialogPasteNames	58	Paste Names dialog box
xlDialogPasteSpecial	53	Paste Special dialog box
xlDialogPatterns	84	Patterns dialog box
xlDialogPermission	832	Permission dialog box
xlDialogPhonetic	656	Phonetic dialog box
xlDialogPivotCalculatedField	570	Pivot Calculated Field dialog box
xlDialogPivotCalculatedItem	572	Pivot Calculated Item dialog box
xlDialogPivotClientServerSet	689	Pivot Client Server Set dialog box
xlDialogPivotFieldGroup	433	Pivot Field Group dialog box
xlDialogPivotFieldProperties	313	Pivot Field Properties dialog box
xlDialogPivotFieldUngroup	434	Pivot Field Ungroup dialog box
xlDialogPivotShowPages	421	Pivot Show Pages dialog box
xlDialogPivotSolveOrder	568	Pivot Solve Order dialog box
xlDialogPivotTableOptions	567	Pivot Table Options dialog box
xlDialogPivotTableWizard	312	Pivot Table Wizard dialog box
xlDialogPlacement	300	Placement dialog box
xlDialogPrint	8	Print dialog box
xlDialogPrinterSetup	9	Printer Setup dialog box
xlDialogPrintPreview	222	Print Preview dialog box
xlDialogPromote	202	Promote dialog box
xlDialogProperties	474	Properties dialog box
xlDialogPropertyFields	754	Property Fields dialog box
xlDialogProtectDocument	28	Protect Document dialog box
xlDialogProtectSharing	620	Protect Sharing dialog box
xlDialogPublishAsWebPage	653	Publish As WebPage dialog box
xlDialogPushbuttonProperties	445	Pushbutton Properties dialog box
xlDialogReplaceFont	134	Replace Font dialog box
xlDialogRoutingSlip	336	Routing Slip dialog box
xlDialogRowHeight	127	Row Height dialog box
xlDialogRun	17	Run dialog box
xlDialogSaveAs	5	SaveAs dialog box
xlDialogSaveCopyAs	456	SaveCopyAs dialog box
xlDialogSaveNewObject	208	Save New Object dialog box
xlDialogSaveWorkbook	145	Save Workbook dialog box
xlDialogSaveWorkspace	285	Save Workspace dialog box
xlDialogScale	87	Scale dialog box
xlDialogScenarioAdd	307	Scenario Add dialog box
xlDialogScenarioCells	305	Scenario Cells dialog box
xlDialogScenarioEdit	308	Scenario Edit dialog box
xlDialogScenarioMerge	473	Scenario Merge dialog box
xlDialogScenarioSummary	311	Scenario Summary dialog box
xlDialogScrollbarProperties	420	Scrollbar Properties dialog box
xlDialogSearch	731	Search dialog box
xlDialogSelectSpecial	132	Select Special dialog box
xlDialogSendMail	189	Send Mail dialog box
xlDialogSeriesAxes	460	Series Axes dialog box
xlDialogSeriesOptions	557	Series Options dialog box
xlDialogSeriesOrder	466	Series Order dialog box
xlDialogSeriesShape	504	Series Shape dialog box
xlDialogSeriesX	461	Series X dialog box
xlDialogSeriesY	462	Series Y dialog box
xlDialogSetBackgroundPicture	509	Set Background Picture dialog box
xlDialogSetPrintTitles	23	Set Print Titles dialog box
xlDialogSetUpdateStatus	159	Set Update Status dialog box
xlDialogShowDetail	204	Show Detail dialog box
xlDialogShowToolbar	220	Show Toolbar dialog box
xlDialogSize	261	Size dialog box
xlDialogSort	39	Sort dialog box
xlDialogSortSpecial	192	Sort Special dialog box
xlDialogSplit	137	Split dialog box
xlDialogStandardFont	190	Standard Font dialog box
xlDialogStandardWidth	472	Standard Width dialog box
xlDialogStyle	44	Style dialog box
xlDialogSubscribeTo	218	Subscribe To dialog box
xlDialogSubtotalCreate	398	Subtotal Create dialog box
xlDialogSummaryInfo	474	Summary Info dialog box
xlDialogTable	41	Table dialog box
xlDialogTabOrder	394	Tab Order dialog box
xlDialogTextToColumns	422	Text To Columns dialog box
xlDialogUnhide	94	Unhide dialog box
xlDialogUpdateLink	201	Update Link dialog box
xlDialogVbaInsertFile	328	VBA Insert File dialog box
xlDialogVbaMakeAddin	478	VBA Make Addin dialog box
xlDialogVbaProcedureDefinition	330	VBA Procedure Definition dialog box
xlDialogView3d	197	View 3D dialog box
xlDialogWebOptionsBrowsers	773	Web Options Browsers dialog box
xlDialogWebOptionsEncoding	686	Web Options Encoding dialog box
xlDialogWebOptionsFiles	684	Web Options Files dialog box
xlDialogWebOptionsFonts	687	Web Options Fonts dialog box
xlDialogWebOptionsGeneral	683	Web Options General dialog box
xlDialogWebOptionsPictures	685	Web Options Pictures dialog box
xlDialogWindowMove	14	Window Move dialog box
xlDialogWindowSize	13	Window Size dialog box
xlDialogWorkbookAdd	281	Workbook Add dialog box
xlDialogWorkbookCopy	283	Workbook Copy dialog box
xlDialogWorkbookInsert	354	Workbook Insert dialog box
xlDialogWorkbookMove	282	Workbook Move dialog box
xlDialogWorkbookName	386	Workbook Name dialog box
xlDialogWorkbookNew	302	Workbook New dialog box
xlDialogWorkbookOptions	284	Workbook Options dialog box
xlDialogWorkbookProtect	417	Workbook Protect dialog box
xlDialogWorkbookTabSplit	415	Workbook Tab Split dialog box
xlDialogWorkbookUnhide	384	Workbook Unhide dialog box
xlDialogWorkgroup	199	Workgroup dialog box
xlDialogWorkspace	95	Workspace dialog box
xlDialogZoom	256	Zoom dialog box

To download an example workbook CLICK HERE

Permalink Leave a Comment

Set Excel Document Properties

December 20, 2008 at 19:40 · Filed under VBA Examples

Somebody asked me whether it is possible to change the built document properties programmatically, that we can access for excel by going to worksheet menu bar… file >> properties… The Document Property window pops up and you can set the built in document properties by typing out on controls in summary tab. I thought it wont be possible, but then I found a way that this can be done.

I have listed the code below…

Option Explicit

Private Sub cmdAssign_Click()
Dim i As Integer
For i = 0 To Me.Controls.Count – 1
  If UCase$(TypeName(Controls(i))) = “TEXTBOX” Then
    With ActiveWorkbook
      .BuiltinDocumentProperties(Mid(Me.Controls(i).Name, 4, 255)) = Me.Controls(i).Text
      Me.Controls(i).Text = “”
    End With
  End If
Next i
MsgBox “Document Properties Successfully Done “
End Sub

Private Sub cmdCancel_Click()
Dim msg1
  msg1 = MsgBox(“Are you sure you want to quit this form?”, vbYesNo, “Exit This Form”)
  If msg1 = vbYes Then
    Unload Me
  End If
End Sub

To Download the example codes on userform CLICK HERE

To Download Example workbook Demonstrating the above code CLICK HERE

Permalink Leave a Comment

Check if a File name is more than 90 days old, & Delete

December 10, 2008 at 00:04 · Filed under VBA, VBA Examples

Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

‘ CODE TO COMPARE IF ANY FILENAME IS > 90 DAYS OLD

Const Files_Location = “J:\Falcon\Management\INFILL REPORTS\FCD\”

Sub Delete_Files(ByRef sDir As String)
  Dim i As Integer
  Dim dt2Delete, strDT As Date

  With Application.FileSearch
    .LookIn = sDir
    .SearchSubFolders = False
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
  End With

  With Application.FileSearch
    If .Execute() > 0 Then
      MsgBox “There were ” & .FoundFiles.Count & _
          ” file(s) found.”
        For i = 1 To .FoundFiles.Count
          MsgBox .FoundFiles(i)
          strDT = Mid(.FoundFiles(i), _
          InStrRev(.FoundFiles(i), ” “, _
          -1, vbTextCompare) + 1, 255)

          dt2Delete = Format(Now() – 90, “ddmmyyyy”)
          MsgBox dt2Delete
          If strDT < dt2Delete Then
              MsgBox strDT
              MsgBox “NEED TO DELETE”
          Else
              MsgBox “NEED NOT DELETE”
          End If
        Next i
      Else
          MsgBox “There were no files found.”
      End If
  End With