Archive for Automation
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, _ With Application.FileSearch End Function Sub Consolidate_Data() myFolder = “J:\GB Project\CONSOLIDATED” With ThisWorkbook.Sheets(“data”) |
Archives
- ActiveX Programming
- Add an Image to Cell Comments
- Add Sheet Navigation CommandBar to an Excel file or Application
- Auto-Generate a File Name
- Automate File Download from Internet
- Automate WebLogin…
- Convert Decimal to RGB Color Value
- Copy Ranges from Different Sheets into one Sheet
- Create HyperlinkObject on Sheet
- Create LogFile for Workbook Open
- Create Sheet “Table of Contents”
- Create ShortCut Link at the desired location
- Create UserForm at Runtime using Code
- Display Userform with no Title Bar (Splash Screen)
- Downloads
- Downloads from www.jkp-ads.com
- Export a Text File with Comma and Quote Delimiters
- Get elements from HTML form
- Get FileName with Path from User
- Get FileNames with Path from User
- Hyperlink Elements in HTML
- Ken’s Knowledge Base submissions
- Login to Rediffmail
- Move a File
- Multiple Cell Concatenation
- Other Site Links
- Read CSV Using VBA techniques
- Remove Unprintable Character & Spaces from cells
- Rename a File
- Rename Files using Codes (VBA)
- Resources
- Resources
- Search Folders and Subfolders for files
- Show Status on Userform While executing Code
- Site Contents
- socko.wordpress.com — Technorati Search
- Sort WorkSheets by Name
- Sorting Text with Worksheet Formula
- test
- Use of vbScript in Database Applications
- Userform Resizer – VBA
- Userforms
- VBA Code Library
- vbaX KB Links
- Verify that a File Exists
- Web Forms manipulated by Visual Basic
- Write to & Read from Sequential Text Files
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
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. |
|
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")