Archive for Automation

Add Label to Userform Programmatically

Read the rest of this entry »

Advertisements

Leave a Comment

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

– 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:        https://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

Leave a Comment

Archives

Leave a Comment

Clear Controls on Userform

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
The following code can be used to clear all textboxes and comboboxes on userform.

HOW SHOULD I USE THE FOLLOWING CODE

Private Sub Clear_all()
    For I = 0 To UserForm1.Controls.Count - 1

        If UCase(TypeName(UserForm1.Controls(I))) = "TEXTBOX" Then
            UserForm1.Controls(I).Value = ""
        End If
        If UCase(TypeName(UserForm1.Controls(I))) = "COMBOBOX" Then
            UserForm1.Controls(I).Value = ""
        End If
    Next I
End Sub

Leave a Comment

Automate File Download from Internet

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

The following code illustrates how one can use visual basic for automated file download when the code actually initializes the internetexplorer and navigates the path, and it saves at the location suggested by the user the file that is to be downloaded… Please be advised this code is not well tested and might not be perfect in all scenario.

HOW SHOULD I USE THE FOLLOWING CODE

Option Explicit
Dim OBJIE As InternetExplorer
Dim myurl As String
Dim myExcelFile  As String
'____________________________________________________________
Sub DownLoad_File()
'   --------- Initialize strings like name of the file & -
'   url to navigate --------------------------------------
    
    myExcelFile = FileNameWithPath
    myurl = "http://www.sockofiles.350.com/" & _
            "Form=_With=_no=_title=_bar.xls"
'   ---------- Open internetexplorer and navigate --------
    Set OBJIE = New InternetExplorer
    With OBJIE
        .Navigate myurl
        .AddressBar = 0
        .FullScreen = False
        .Height = 300
        .Width = 400
        .Top = 0
        .Left = 0
        .Toolbar = 0
        .MenuBar = 0
        .Resizable = 0
        .StatusBar = 0
        .Visible = True
    End With
'   --------Wait till IE is free -------------------------
    Do Until OBJIE.ReadyState
        DoEvents
    Loop
'   --------Pass on keys to download and save ------------
    Application.Wait Now() + TimeValue("00:0:03")
    SendKeys ("%s")
    Application.Wait Now() + TimeValue("00:0:03")
    
    SendKeys (myExcelFile)
    Application.Wait Now() + TimeValue("00:0:03")
    SendKeys ("%s")
    Application.Wait Now() + TimeValue("00:0:03")
    OBJIE.Quit
    MsgBox "DONE"
End Sub
'____________________________________________________________
Function FileNameWithPath() As String
    FileNameWithPath = _
        InputBox("Give the complete name of the " & _
        "file that you want to save including path")
End Function

Leave a Comment

Automate WebLogin…

Copy the following script and paste it in a notepad, make changes to uname and upass variable as your userid and your password… Now, save the notepad as Login_Wordpress.vbs and then when you double click on the document saved in a folder, you will see your login is done by vbscript automatically.
-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Dim navURL, uName, uPass 
Dim ie 

'Change following couple of lines with you ID & Password
    uName = "MyUserName"
    uPass = "myPassWord"

'This sets the url of the page, in this case
'rediff login page
    navURL = "https://socko.wordpress.com/wp-login.php"

'Create internetexplorer and provide url
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate navURL

'set visibility as true
    ie.Visible = True

'wait until IE is done loading page.
    While ie.Busy
      'DoEvents:
    Wend

'Enter userid and password in respective textboxes
    ie.document.all("log").Value = uName
    ie.document.all("pwd").Value = uPass
   
'click go button
    ie.document.all("wp-submit").Click    'Clicks the GO button on the page

'wait until IE is done loading page.
    While ie.Busy
      'DoEvents  'wait until IE is done loading page.
    Wend

    msgbox ("Done")

Leave a Comment