A Python script that uses the xlrd and openpyxl frameworks to read user defined cells from a selected Excel spreadsheet and append them to a seperate master workbook.

XLWriter

import shutil
import os
import xlrd
from xlrd import open_workbook
from openpyxl import load_workbook
import openpyxl
import glob
import datetime


files = glob.glob('*.xls')
filename = []
for i in files:
	filename += [i]
filestring = filename[0]


### Open source workbook ###

workbook = open_workbook(filestring)
sheet = workbook.sheet_by_index(0)

### Read data from source column ###

# Initialise empty lists which will store values
worktick = []
ordernum = []
siteadd = []
locid = []
leadlen = []


def readsource(lst, colnum):
	source = []
	for i in range(sheet.nrows):
		lstval = sheet.cell_value(i, colnum)
		source += [lstval]
	source = source[1:]
	return source
# Function for reading data from a selected column (colnum) to a predefined list (lst)
worktick = readsource(worktick, 0)
ordernum = readsource(ordernum, 1)
siteadd = readsource(siteadd, 2)
locid = readsource(locid, 3)
leadlen = readsource(leadlen, 7)

### Open destination workbook ###

# masterdoc = 'C:\where\is\the\file'
masterdoc = 'Google docs Master Workbook.xlsx'
destwb = load_workbook(masterdoc)
ws = destwb.active


# Find length of input row
rowx = 0
cell = ws['A1']
column_floor = []
while cell.value != None:
	cell = ws.cell(0, 1 + rowx, 0)
	rowx += 1
	column_floor += [cell.value]
column_floor = len(column_floor) + 1




# column_floor is our start row value for writing source values



theday = datetime.datetime.now()
today = "%s-%s-%s" % (theday.day, theday.month, theday.year)
added = 'Added on: ' + today


### Write source values ###
def writeval(lst, colnum):
	titlecell = ws.cell(0, column_floor-1, 0)
	titlecell.value = added
	for i, value in enumerate(lst):
		newcell = ws.cell(0, column_floor + i, colnum)
	 	newcell.value = value

writeval(worktick, 0)
writeval(ordernum, 1)
writeval(siteadd, 2)
writeval(locid, 3)
writeval(leadlen, 4)

### Save file ###
# Initialise variables
current = os.getcwd()
processed_path = current + os.sep + 'Processed' + os.sep



# Save our edited Master Workbook
destwb.save(masterdoc)

# Save a copy of our edited Master Workbook, and move to a Backup dir
backupPath = current + os.sep + 'Backup' + os.sep
destwb.save(backupPath + 'Backup' + today + 'Master Doc.xlsx') 
# shutil.move('Backup Master Doc.xlsx', backupPath) 

# Move our source workbook to a Processed dir
processed_path = current + os.sep + 'Processed' + os.sep
processedtag = 'Processed on ' + today + ' '
shutil.move(filestring, processed_path)
os.rename(processed_path + os.sep + filestring, processed_path + os.sep + filestring[:-4] + ' [' + processedtag + ']' + '.xls')