Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Highlighted
Established Member.. RStimy
Established Member..
767 views

DataTable Import clarification

Jump to solution

Hi, please excuse me if I am overlooking something simple but I have been up and down this site and Google trying to determine whether or not I can import an .xls file into my datatable "as is."  To clarify, I know how to use DataTable.Import filename to get the file loaded and I can use AddSheet and ImportSheet to put a specific sheet into a same-named sheet in my DT.  My question: is there not a way to programmatically do the same as File>Settings>Resources>DataTable>Other Location which COMPLETELY replaces the existing DT with the excel file with all of it's sheets and sheet names in tact?  The problem is that after I import the excel file and update the specific sheet during the test, I rewrite the file back to it's original location.  If I don't have whatever other sheets may have been in the original excel file, they will be lost in the rewrite.

 

Thanks

0 Likes
1 Solution

Accepted Solutions
swaroopjoshi Absent Member.
Absent Member.

Re: DataTable Import clarification

Jump to solution

You would need to use object model to import the sheets programmatically.

excelPath = "<path of excel file>"
Set ExcelApp = CreateObject("Excel.Application")
Set Workbook1 = ExcelApp.Workbooks.Open (excelPath,,True) ' True for read-only

For each Sheet in Workbook1.Worksheets

sheetname = Sheet.Name
DataTable.AddSheet sheetname
DataTable.ImportSheet excelPath, sheetname, sheetname

Next

Workbook1.close
ExcelApp.Application.Quit
Set ExcelApp = Nothing
Set Workbook1 = Nothing

 


Accept as solution/Give Kudos to the author if the piece of information answered/helped you to solve your problem.
All the thoughts here are mine not related to my employer nor anyone.
1 Reply
swaroopjoshi Absent Member.
Absent Member.

Re: DataTable Import clarification

Jump to solution

You would need to use object model to import the sheets programmatically.

excelPath = "<path of excel file>"
Set ExcelApp = CreateObject("Excel.Application")
Set Workbook1 = ExcelApp.Workbooks.Open (excelPath,,True) ' True for read-only

For each Sheet in Workbook1.Worksheets

sheetname = Sheet.Name
DataTable.AddSheet sheetname
DataTable.ImportSheet excelPath, sheetname, sheetname

Next

Workbook1.close
ExcelApp.Application.Quit
Set ExcelApp = Nothing
Set Workbook1 = Nothing

 


Accept as solution/Give Kudos to the author if the piece of information answered/helped you to solve your problem.
All the thoughts here are mine not related to my employer nor anyone.
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.