2

I have a folder with many excel files of the same format, with multiple sheets. The goal is to loop through the folder and move one of the sheets in each notebook to an new notebook i.e. ending up one sheet from each .xlsx file in the new workbook. I normally use DataNitro for excel work, but sadly when copying data you don't seem to be able to specify a workbook argument to the CellRange function, so I don't think it is possible. I have code to do it in VBA, but my organisation is moving away from VBA. I am looking for workarounds using xlrd and xlutils. So far I have this as a test case for one of the files:

import os, xlrd, xlwt, xlutils
FilePath = r"Z:\Production_Data\WaveII\2-007\20130403\Central report\March'13\March"     #ENTER FILE PATH HERE
os.chdir(FilePath)
wb = xlrd.open_workbook("2nd Mar'13 CENTRAL REPORT..xls")
copy_sheet1 = wb.sheet_by_index(1)
new_book = xlwt.Workbook()
test = new_book.add_sheet('TEST')

But I am unsure how to write the data in copy_sheet1 to the 'TEST' sheet in the new workbook. Incidentally it would be best if it was an exact copy i.e. including formatting ect, rather than a cell by cell iteration of the data. Not sure whether that is possible or not.

Any help much appreciated.

Thanks

Woody Pride
  • 13,539
  • 9
  • 48
  • 62

1 Answers1

0

You can switch between multiple workbooks in DataNitro with the active_wkbk, open_wkbk, and close_wkbk commands. There's more information in the docs: https://datanitro.com/docs/workbook.html#multiple-workbooks

Ben Lerner
  • 1,318
  • 9
  • 12