I have created a macro which can split files data into various workbook based on count of rows each split files having 60K records and it's working fine as far as files are concerned however these files need to be uploaded into an application which is based on 2003 POI i.e. you can't upload 2010 files.
Code given below
With ThisWorkbook.ActiveSheet Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious) For lLoop = 1 To rLastCell.Row Step 60000 lCopy = lCopy + 1 Set wbNew = Workbooks.Add .Range(.Cells(lLoop, 1), .Cells(lLoop + 60000, .Columns.Count)).EntireRow.Copy Destination:=wbNew.Sheets(1).Range("A1") If lCopy > 1 Then wbNew.Sheets(1).Activate Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "Application Name" Range("B1").Select ActiveCell.FormulaR1C1 = "Url" Range("C1").Select ActiveCell.FormulaR1C1 = "Category" Range("D1").Select ActiveCell.FormulaR1C1 = "Sub Category" Range("E1").Select ActiveCell.FormulaR1C1 = "Productive Flag" Range("F1").Select ActiveCell.FormulaR1C1 = "Update (Y/N)" End If wbNew.Close SaveChanges:=True, Filename:=ThisWorkbook.path & "\App_Invetory" & "-" & lCopy & ".xls" Next lLoop End With
When I try uploading the resultant files we are getting an exception given below.
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
Hence I would some help in Splitting Excel 2010 files into multiple Excel 2003 files...Is it even possible.