Hi all,
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 WithWhen 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.