Share |

Saturday, August 27, 2016

How to consolidate Excel Worksheets in Python and VBA?

Below are the steps to consolidate Microsoft Excel worksheets in VBA :-

  • Press Windows Logo Key ( Win Logo Key) + R to open Run Dialog box.

    Run-dialog-box
  • Type Excel and Hit on OK button to open Microsoft Excel Application

    Microsot excel workbook photo
  • Press ALT + F11 (function key) to Open Visual Basic for Application Editor

    Excel VBA Editor
  • Select Insert from Menu bar then select Module from Pop up Window

    Excel-VBA-insert-popup-Module
  • then Copy & Paste Below written code in the module

    Excel-VBA-Editor-COPY-PASTE
  • Place Cursor any where in the code and click on run button (you can also press F5 to run this code).

    Excel-VBA-RUN.png


VBA Code

Option Explicit

Public Sub ConsolidateSheets()
Dim wrkBkDump, wrkBkNewDump As Workbook
Dim LastRow As Long, i As Long, FileDump

Application.DisplayAlerts = False
Application.ScreenUpdating = False

FileDump = Application.GetOpenFilename("Microsoft Excel workbook(*.xl*),(*.xl*)", MultiSelect:=False)

If FileDump <> False Then
    Set wrkBkDump = Application.Workbooks.Open(FileDump, ReadOnly:=True)

    Set wrkBkNewDump = Excel.Workbooks.Add
    With wrkBkNewDump
        '.Sheets(Array(1, 2)).Delete
        .Sheets(1).Name = "Dump"
    End With
    
    For i = 1 To wrkBkDump.Sheets.Count
        wrkBkDump.Worksheets(i).AutoFilterMode = False
        wrkBkDump.Worksheets(i).Range("A3").CurrentRegion.Copy
        
        With wrkBkNewDump.Worksheets("dump")
            LastRow = .Range("B1048576").End(xlUp).Row
            
            If LastRow <> 1 Then
                .Range("A" & LastRow + 1).PasteSpecial (xlPasteAll)
            Else
                .Range("A1").PasteSpecial (xlPasteAll)
            End If
            Application.CutCopyMode = False
        End With

    Next

    wrkBkNewDump.SaveAs (Replace(FileDump, ".", "_Copy."))
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

consolidate Excel worksheets in python as well

Python Code

import win32com.client as win32


class xlconsolidator():
    xlapp = None
    xlenum = None

    def __init__(self):
        self.xlapp = win32.gencache.EnsureDispatch("Excel.Application")
        self.xlenum = win32.constants
        self.xlapp.Visible = True

    def getfile(self):
        return self.xlapp.GetOpenFilename("Microsoft Excel workbook(*.xl*),(*.xl*)", MultiSelect=False)

    def openworkbook(self, filename):
        wb = None
        try:
            wb = self.xlapp.Workbooks.Open(filename, ReadOnly=True)
        except Exception as ex:
            print ex.message

        return wb

    def createworkbook(self):
        return  self.xlapp.Workbooks.Add()

    def consolidatesheets(self, xlwbsource, xlwbtarget):

        xlwktarget = xlwbtarget.Worksheets(1)

        for xlwksheet in xlwbsource.Worksheets:
            lastrow = xlwktarget.Range("A" + str(xlwktarget.Rows.Count)).End(self.xlenum.xlUp).Row
            #Remove filter from sheet
            xlwksheet.AutoFilterMode = False
            #by assuming data is in structred format
            xlwksheet.Range("A1").CurrentRegion.Copy
            if lastrow != 1:
                xlwksheet.Range("A1").CurrentRegion.Copy(xlwktarget.Range("A" + lastrow + 1))
            else:
                xlwksheet.Range("A1").CurrentRegion.Copy(xlwktarget.Range("A1"))

            self.xlapp.CutCopyMode = False

        xlwbsource.Close(False)

    def saveworkbook(self, wb, newfilename):
        wb.SaveAs(newfilename)

    def __del__(self):
        self.xlapp.Quit()
        #self.xlapp.DisplayAlerts = True
        #self.xlapp.ScreenUpdating = True

if __name__ == '__main__':
    xl = xlconsolidator()
    filename = xl.getfile()
    wbsrc = xl.openworkbook(filename)
    if wbsrc != None:
        wbtarget =  xl.createworkbook()
        xl.consolidatesheets(wbsrc, wbtarget)
        xl.saveworkbook(wbtarget, 'c:\\consolidatted Workbook.xlsx')
        #filenamewithoutext =  os.path.pardir()

Saturday, July 09, 2016

How to send email in python using gmail, yahoo, hotmail?

In python, I have created a class by which we can send email through gmail, yahoo, hotmail or any other email service provider.


screen shot of python IDLE editor



Click here to view project online


Here is the complete code:


import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.mime.application import MIMEApplication
from email.MIMEText import MIMEText
from email.MIMEBase import MIMEBase
from email import Encoders

#-------------------------------Author-----------------------------------#
#                       chandan kumar ojha                               #
#                   Please don't change in code                          #
#                   It will reads to raise error                         #
#------------------------------------------------------------------------#

class Email(object):
    _message=None

    def __init__(self,to,eFrom,subject,body,Files=[]):
        msg = MIMEMultipart()
        msg['From'] = eFrom
        msg['To'] = to
        msg['Subject'] = subject
        msg.attach(MIMEText(body, 'plain'))

        for f in Files:
            #part = ('application',"octet-stream")
            #part.set_payload(open(f,"rb").read())
            part = MIMEApplication(open(f,'rb').read())
            #Encoders.encode_base64(part)
            part.add_header('Content-Disposition', 'attachment', filename=str(os.path.basename(f)))
            msg.attach(part)
        self._message = msg

    def sendEmail(self,host,port,username,password):
        server = smtplib.SMTP(host, port)
        server.starttls()
        server.login(username,password)
        server.sendmail(self._message['From'],self._message['To'],self._message.as_string())
        server.quit()

# example To send email- Uncomment below line to send email
if __name__ == '__main__':
    Attachmets = ['c:\\Directory Name\\sub Directory\\File Name.Ext', 'c:\\Directory Name\\sub Directory\\File2 Name.Ext']
    mail = Email("ToEmailAddress@gmail.com","youemailAddress@gmail.com","test-SubjectLine","this is a body Text",Attachmets)
    mail.sendEmail('smtp.gmail.com',587,'youemailAddress@gmail.com','password')

Friday, June 24, 2016

How to scrape data from website using selenium in python?

Scootsy Crawler / Scrapper

WARNING - Contents of this page are for educational purposes only. It is strongly suggested that you do not use this knowledge for illegal purposes!

Below are the code of statement to scrape or extract information from website.
  • Library used in the codes are sys, Selenium, pandas and datetime
  • web driver Firefox. you can used chrome web driver instead.

python code view

Python-IDLE-Scootsy-crawler-code

Scrapped Data view

Scootsy scrapped data

Scootsy Scrapper Code

click here to view project online

import sys
import selenium
import selenium.webdriver.common.keys
import pandas
import datetime

URL = 'https://scootsy.com/get_vendor_listing.php'
#*************************************************************************************************
maxcharLen = 60
print (maxcharLen + 1) * '-'
strToPrint = 'Scootsy Crawler 1.0'
print (maxcharLen  - len(strToPrint))/2 * '*', strToPrint , (maxcharLen  - len(strToPrint))/2 * '*'
strToPrint = "Please don't change the code "
print (maxcharLen  - len(strToPrint))/2 * '*' , strToPrint , (maxcharLen  - len(strToPrint))/2 * '*'
strToPrint = "If you change it will leads to raise an error"
print (maxcharLen  - len(strToPrint))/2 * '*' , strToPrint , (maxcharLen  - len(strToPrint))/2 * '*'
print (maxcharLen + 1) * '-'
#*************************************************************************************************

sys.stdout.write("\r" + "Driver Initializing ...")
#driver = selenium.webdriver.Chrome("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe")
driver = selenium.webdriver.Firefox()
sys.stdout.write("\r" + "Navigating to url : " + URL )
driver.get(URL)
#assert "tabs-container2" in driver.find_element_by_id('tabs-container2')

ul = driver.find_element_by_tag_name("ul")
sys.stdout.write("\r" + "start crawling for Restaurant : " + URL )
columns = ['Id','City','Restaurant Name','Cuisines','Delivery Time','URL']

restaurants = [[]]
i = 0

print "\r" + "found available restaurant : ", len(ul.find_elements_by_tag_name("li"))
for li in ul.find_elements_by_tag_name("li"):

    div = li.find_element_by_class_name('dish_name')
    a = div.find_element_by_tag_name('a')
    span = div.find_element_by_tag_name('span')

    resid = li.get_attribute('id')
    resName = a.text
    cousin = span.text
    deliverytime = li.find_element_by_class_name('icn').find_element_by_tag_name('span').text
    link = a.get_attribute('href')
    city = link.split('/',3)[3].split('-')[3]

    restaurants[i].append(resid)
    restaurants[i].append(city)
    restaurants[i].append(resName)
    restaurants[i].append(cousin)
    restaurants[i].append(deliverytime)
    restaurants[i].append(link)

    sys.stdout.write("\r" + "Data Extracted For Restaurant : " + resName)

    restaurants.append([])

    i += 1

sys.stdout.write("\r" + "Data Extraction Finished...")
driver.close()
sys.stdout.write("\r" + "Driver Close...")
data_Table = pandas.DataFrame.from_records(restaurants,columns=columns)

filename = "scootsy crawl " + str(datetime.datetime.today().strftime("%d%m%y %H%M%S")) + ".xlsx"
sys.stdout.write("\r" + "File Saving TO : " + filename)
data_Table.to_excel(filename,'Sccotsy',index=False,encoding="UTF-8")
print "\r" + "File Saved @ : ", filename
sys.stdout.read()

Friday, June 03, 2016

How to Install psycopg2 library in Python (windows)

Install psycopg2 Module in Python 2.7 in windows

Prerequisite activity:

Path of the parent folder of pip is appended/added in system Environment variables.

Please follow link here to Add/Append pip command in System Environment variables

Please follow below steps to Install psycopg2 library in python:
  1. Press Windows Logo Key () + R to open Run Dialog box.


  2. Type cmd and press OK button to open Command Prompt.


  3. Type pip Install psycopg2 and Press Enter


Friday, May 27, 2016

Refresh Data Queries in Excel workbook - Python and VBA

Below code snippets written in Python and VBA as well, to refresh Excel Workbook Data Queries (Connections) whether For Table or Pivots Table or Charts:



in Python

import win32com.client as win32
import time

Files = ['C:\\Folder\\File1.xlsx', 'C:\\Folder\\File2.xlsx', 'C:\\Folder\\File3.xlsx']


def RefreshFiles(Files):
    UpdatedFiles = []

    xl = win32.DispatchEx("Excel.Application")
    xl.visible = True
    for filename in Files:
        try:
            WB = xl.workbooks.open(filename)
            for con in WB.connections:
                con.refresh
            WB.Close(True)

            UpdatedFiles.append(filename)
            time.sleep(1)
        except:
            print "File Not Updated : " + filename

    xl.Quit()
    return UpdatedFiles;

in VBA

Option Explicit

Public Sub GetFiles()
    Dim Files
    
    Files = Application.GetOpenFilename("Micrsoft excel workbooks (*.xls*),(*.xls*)", MultiSelect:=True)
    
    If IsArray(Files) Then
        RefreshAll Files
    End If
End Sub

Private Sub RefreshAll(Files As Variant)
Dim XL As Excel.Application, WB As Excel.Workbook
Dim con As Variant, File As Variant

Set XL = New Excel.Application

For Each File In Files
    Set WB = XL.Workbooks.Open(File)
    For Each con In WB.Connections
        con.Refresh
    Next

    WB.Close True
Next

XL.Quit

End Sub
© 2016 All Rights Reserved | DMCA.com Protection Status