Importing Excel that contains blank Rows
mponter
Forums
Hi Brian,
We have a spreadsheet that is generated monthly by HR and is dumped into a shared folder in .xlsx format. Unfortunately the top 2 rows of the .xlsx file is used as a header which is throwing IDEA when I try to import the file.
My first guess is to have Excel grab the file open it, remove the rows and re-save the file then import with IDEA. Second is to use Excel to convert the .xlsx file to a .txt and import (ignoring the first few lines).
Either way, I'm having difficulty manipulating Excel from IDEA. Also, if we move towards running tests in IDEA Server I am not sure whether using intermediary applications is an option. I feel like I'm overlooking something basic and wondered if you had any thoughts.
Regards, Mark
Hi Mark,
Hi Mark,
Here is some code for the first option. This will open your spreadsheet, remove the first two rows and then resave it at which time you can do the import.
A third option is to bring it in and remove the first two lines through an extraction, the downside with this is I assume you would then have to rename all the fields.
Function ModifySpreadsheet(sFilename as string)
Const xlUp = -4162
Dim excel As Object
Dim oBook As Object
Dim oSheet As Object
Set excel = CreateObject("Excel.Application")
excel.Visible = False
Set obook = excel.Workbooks.Open(sFilename)
Set oSheet = oBook.Worksheets.Item(1)
sSheetName = oSheet.Name
oSheet.Rows("1:2").Delete Shift:=xlUp
Set oSheet = Nothing
oBook.Save
oBook.Close (True)
Set obook = Nothing
excel.quit
Set excel = Nothing
End Function