Excel OLE Check for Empty Cell

[jaylaforte]

I'm writing a script that parses an Excel file to import data into my database and am having difficulty figuring out how to check for an empty cell.

I tried doing a loop such as the following:

While currentCell <> ""
   'Do stuff
End While

However, every time I loop to an empty cell I get an error stating "Argument is incorrect data type (NIL)"

Thanks for any help!

[jaylaforte]

Re: Excel OLE Check for Empty Cell

Well, once I posted the thread, I found a solution in one of the related posts below the thread.

From this post: http://msgboard.alphasoftware.com/al...ad.php?t=66096

Selwyn suggested using eval_Valid to check for the error. So essentially, whenever I was checking for a value in a cell, I would make sure the statement was valid. If I was going to receive a "NIL" error, the eval_valid function would catch it.

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

Try

While currentCell.value <> ""
'Do stuff
End While

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

Wrong thread, sorry.

[jaylaforte]

Re: Excel OLE Check for Empty Cell

Stan - That works perfectly! When I was referencing a cell in Excel, I would still receive a value back without using ".Value" and assumed it was the same functionality.

Thanks for the help!!

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

You wouldn't believe how long it took me to figure that out. Glad I could pass it on.

[jaylaforte]

Re: Excel OLE Check for Empty Cell

I can believe it... I've been at this for a few days now and would've been at it even longer! I really appreciate it!

One final question for you: Do you know of any good resources for Excel OLE methods and functions? I've been scouring the internet with Google trying to find information and am successful most of the time but I haven't been able to find a one-stop-shop for all things Excel OLE.

Thanks again Stan!

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

My best source of help, after I got a good push from Marcel, is to create a simple macro in excel doing what I want to be able to tell Alpha to do. I then look at the vb behind the macro and work out how to translate that into xbasic.

You can determine the numeric values of excel constants in the vb immediate window.

? xlAscending
 1
? xldescending
 2

There are still some areas that I haven't been able to make work even though the implementation seems simple enough.

This

Range("A1:B6").Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Should translate to (given the xlapp pointer to the ole object

xlApp.Range("A1:B6").Select()
xlApp.Selection.Sort(Range("B1"),1,0,1,.F.,1,0)

but it doesn't work for me.

(Selects the range A1:B6 and sorts ascending on column B)

[Marcel Kollenaar]

Re: Excel OLE Check for Empty Cell

Hi Stan,

Found a solution for the sort problem. The .Sort() command is a fully optional construction, see the Excel help. In VBA you can use two ways of declaring your parameters, 'named' and 'unnamed'. The named version is that every parameter has it name such as:

Key1:=Range("B1")

and even the order in which you name them is free.

The other way is the "in order" parameter list. If you study the .sort() method you will see that is can be used for different types of data: ranges, cell, pivot-table, named ranges. What I did is starting with a full parameter list and deleted each last parameter until there was no error message and the sort worked. I think that Xbasic can't handle the optional part of the declaration list and when the parameters doesn't align anymore it produces an error.

'xlApp.Selection.Sort(xlApp.Range("A1"),1,xlApp.Range("A1"),2,xlApp.Range("A1"),1,0,1,.F.,1,0,0,0)

I ended up with the next line of code. Only the first three parameters will align without an error.

xlApp.Selection.Sort(xlApp.Range("A1"),1,xlApp.Range("B1"))

and are accepted when you run the code. But the last parameter range doesn't add any positive result to the sort so ommit this. Therefore you can only sort on one column in the selected range and the selection is includes in the sort.

Solution:

dim xlApp as p
xlApp = ole.GetObject("","Excel.Application.11")
xlApp.Range("A1:C8").select()
xlApp.Selection.Sort(xlApp.Range("A1"),1)

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

Marcel,

Thanks for that. I'll go try an implementation now.

Haven't heard much from you lately. Hope all is well.

Stan

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

Most excellent, Marcel.

FUNCTION excel_sort AS L (excelname AS C, newname AS C, sortcol AS C, direct as N)
	IF (.not. between(sortcol,"A","Z"))
		ui_msg_box("Error","Can only sort on col A-Z")
		exit FUNCTION
	END IF
	IF (.not. between(direct,1,2))
		ui_msg_box("Error","Can only sort ascend (1) or descend (2)")
		exit FUNCTION
	END IF
	Dim xlApp as p
	Dim myCell as c
	xlApp = ole.Create("Excel.Application")
	xlApp.Workbooks.Open(excelname)
	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
	topleft = "A1"
	lastrow = xlApp.Activecell.SpecialCells("11").Row
	lastcol = xlApp.Activecell.SpecialCells("11").Column
	lastcell = chr(lastcol+64)+lastrow
	myrange = topleft+":"+lastcell
	xlApp.Range(myrange).Select()
	mysort = sortcol+"1"
	xlApp.Selection.Sort(xlapp.Range(mysort),direct)
	xlapp.DisplayAlerts = .F.
	xlApp.ActiveWorkbook.SaveAs(newname,-4143) '-4143 is a constant for Excel Normal, dbf4 is 11
	xlApp.Workbooks.Close()
	xlApp.DisplayAlerts = .T.
	xlApp.Quit()
	delete xlApp
END FUNCTION

'to call the function, use the example on the next line
'excel_sort("c:\test1.xls","c:\test2.xls","C",2) 
'this sorts the active area of spreadsheet test1.xls on column C, descending
'then saves that as test2.xls

[Marcel Kollenaar]

Re: Excel OLE Check for Empty Cell

You did an excellent job too Stan with your sort function. I can learn a lot from your Xbasic code how you handle things in Excel.

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

The A-Z column restriction was a shortcut. I have a function somewhere that will translate the lastcol numeric value into the character equivalent for combination with the lastrow numeric value to yield the correct values for lastcell like AA1 and above. Just don't use it much and didn't feel like looking it up.

Thanks for the kind words.

[Stan Mathews]

Re: Excel OLE Check for Empty Cell

Found the conversion of excel column number to letter(s). Sort restriction altered.

FUNCTION excel_sort AS L (excelname AS C, newname AS C, sortcol AS C, direct as N)
	IF (.not. between(sortcol,"A","IV"))'max xl 2003
		ui_msg_box("Error","Can only sort on col A-IV")
		exit FUNCTION
	END IF
	IF (.not. between(direct,1,2))
		ui_msg_box("Error","Can only sort ascend (1) or descend (2)")
		exit FUNCTION
	END IF
	Dim xlApp as p
	Dim myCell as c
	xlApp = ole.Create("Excel.Application")
	xlApp.Workbooks.Open(excelname)
	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
	topleft = "A1"
	lastrow = xlApp.Activecell.SpecialCells("11").Row
	lastcol = xlApp.Activecell.SpecialCells("11").Column
	lastcell = xlindx_to_txt(lastcol)+lastrow
	myrange = topleft+":"+lastcell
	xlApp.Range(myrange).Select()
	mysort = sortcol+"1"
	xlApp.Selection.Sort(xlapp.Range(mysort),direct)
	xlapp.DisplayAlerts = .F.
	xlApp.ActiveWorkbook.SaveAs(newname,-4143) '-4143 is a constant for Excel Normal, dbf4 is 11
	xlApp.Workbooks.Close()
	xlApp.DisplayAlerts = .T.
	xlApp.Quit()
	delete xlApp
	excel_normal = .t.
END FUNCTION

'to call the function, use the example on the next line
'excel_sort("c:\test1.xls","c:\test2.xls","C",2) 
'this sorts the active area of spreadsheet test1.xls on column C, descending
'then saves that as test2.xls

FUNCTION xlindx_to_txt AS C (colnum AS N )
  If Colnum > 26 Then
    xlindx_to_txt = Chr(Int((Colnum - 1) / 26) + 64) + Chr((mod((Colnum - 1),26) ) + 65)
  Else
    xlindx_to_txt = Chr(Colnum + 64)
  End If
END FUNCTION

[MikeC]

Re: Excel OLE Check for Empty Cell

Stan, Marcel,

Thanks for this.
It makes a Very nice addition to my help file for methods to use for Excel.