Hi all,
I'm trying to import data from an Excel workbook using an OLE object. The algorithm works great, but I have hard-coded the WorkSheet number. How can I refernce the sheet by name instead of number? I specifically want to find the sheet named "Import". If it doesn't exist, give the user a message, else copy the data. Here's my code:
TRY
// Open Active sheet
ll_sheet = 1
xlsub = xlapp.Application.ActiveWorkbook.Worksheets[ll_sheet]
ll_excel_rows = Xlsub.UsedRange.Rows.Count
For ll_row = ll_start to ll_end
//First Row is the Column names
idw_2.SetItem(ll_row - 1, 'asfound', Mid(Trim(String(xlsub.cells[ll_row, 1].value)),1,20))
idw_2.SetItem(ll_row - 1, 'foundin', Mid(Trim(String(xlsub.cells[ll_row, 2].value)),1,1))
idw_2.SetItem(ll_row - 1, 'asleft', Mid(Trim(String(xlsub.cells[ll_row, 3].value)),1,20))
idw_2.SetItem(ll_row - 1, 'leftin', Mid(Trim(String(xlsub.cells[ll_row, 4].value)),1,1))
if (ll_excel_columns = 5) then
idw_2.SetItem(ll_row - 1, 'uncertainty', Mid(Trim(String(xlsub.cells[ll_row, 5].value)),1,20))
end if
Yield()
Next
Another interesting interesing thought is to show the user a list of the worksheet names and let him/her choose which worksheet to copy from. Using PB2021.
TIA,
~~~Tracy
TRY
li_ThisSheet = 1
xl_sheets = xl_app.Sheets
li_sheet_count = xl_sheets.Count
for li_x = 1 to li_sheet_count
if xl_sheets.Item(li_x).Name <> "Import" Then
Continue
else
li_ThisSheet = li_x
Exit
end if
Next
xl_sub = xl_app.Application.ActiveWorkbook.Worksheets[li_ThisSheet]
For ll_row = ll_start to ll_end
... blah, blah, blah
Next