Excel OLE: How do I reference a sheet by name?

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

Comment

Tracy Lamb
Thursday, 19 October 2023 22:12 PM UTC

Thank you John... I'll try your suggestion tonight. I agree I need to learn how to use the Offic object models combined with PB.

Helpful 0

Tracy Lamb
Friday, 20 October 2023 00:04 AM UTC

Works perfectly! Here's my code:

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

There are no comments made yet.

Tracy Lamb
Wednesday, 18 October 2023 19:48 PM UTC

Thank you, but I've been pouring over the document link for 2 days. The problem is that I'm not familiar with OLE objects, and even less familiar with Excel Ole commands. I just need to know if there's a sheet named "Import" and what the sheet number is so I can copy some data.

Resolved Excel OLE: How do I reference a sheet by name?