Visual Basic

24-Feb-06 6:08

I have never in my life coded any Visual basic before the last few days, I am more of a C kinda guy, so I wonder if somebody could help me a little with some of my functions.

I am creating an ActiveX dll which is to be used with a C project of mine. The purpose of this dll is to perform all my Excel needs. I am having trouble getting the following to work:

Public Function CloseFile(FileName As String, Save As Boolean)
    xlApp.Workbooks(FileName).Close savechanges:=Save
End Function

Whenever I call it I am confronted with a message box displaying "Subscript out of range".
Also with the following:

Public Function GetCellString(RowId As Integer, ColId As Integer) As String
    GetCellString = xlSheet.Cells(RowId, ColId).Value
End Function

Most of the cells containing floating point numbers, but for simplicity I am returning a string. Problem is, I am only getting the whole number (before the decimal point) instead of a float.

Any help would be greatly appreciated.

Joshua Quick24-Feb-06 7:47

Joshua Quick

24-Feb-06 7:47

waldermort wrote:
xlApp.Workbooks(FileName).Close savechanges:=Save

This line doesn't make any sense.
I'm guessing "savechanges" is a member variable. It should be on the next line.

Also, the colon ':' allows you to write 2 lines of code on the same line. It turns 2 lines of code like this...

Dim index As Integer<br />
index = 1

And allows you to do this...
Dim index As Integer : index = 1

I don't think that was your intent. Although the := reminded me of Pascal. Ahhh... memories. Smile | :)

So, did you mean to do this?

xlApp.Workbooks(FileName).Close<br />
savechanges = Save

waldermort wrote:
GetCellString = xlSheet.Cells(RowId, ColId).Value

I'm guessing that property "Value" is not returning a float. Otherwise, VB would have converted it to a floating point string for you.
Are there any other properties/functions that you can call?

Waldermort24-Feb-06 8:30

24-Feb-06 8:30

Thanks for the reply. Regarding the save function, I can't remember where exactly but I'm sure I found some sample on the net somewhere showing that. But after what you said I went trawling through MSDN and found this http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D3/S5A43B.asp[^]

At the bottom of that page is exactly the same code???

As for the float value I'm still at a loss

-- modified at 14:32 Friday 24th February, 2006

Joshua Quick24-Feb-06 8:50

Joshua Quick

24-Feb-06 8:50

I stand corrected. I've never seen := in VB before. Must be some kind of special syntax because it's not documented in any of my books.

I'm also thinking that your xlApp.Workbooks(FileName) is not returning a WorkBook object, which is why you're getting an exception.

Divya Rathi24-Feb-06 9:01

Divya Rathi

24-Feb-06 9:01

Excel Spreadsheets determine the datatype of a column, by looking at
the content of the first few rows. If it determines that the column is text,
then all numeric cells/fields are returned as null and if the column is
determined as numeric, then all text fields are returned as null.

Divya Rathi

Waldermort24-Feb-06 12:51

24-Feb-06 12:51

I determined that the function itself is causing the problem. You say that the datatype is determined by the first few rows. The first 2 rows and first column of this file contain text, the rest is composed entirly of floating point numbers. From this function:

Public Function GetCellString(RowId As Integer, ColId As Integer) As String
    Dim val As Double
    val = xlSheet.Cells(RowId, ColId).Value
    MsgBox val
    GetCellString = xlSheet.Cells(RowId, ColId).Value
End Function

Waldermort24-Feb-06 13:36

DataGrid Current Row and Field

24-Feb-06 13:36

My code now looks like this

Public Function GetCellString(RowId As Integer, ColId As Integer) As String
    If VarType(xlSheet.Cells(RowId, ColId).Value) = vbString Then
        GetCellString = xlSheet.Cells(RowId, ColId).Value
    ElseIf VarType(xlSheet.Cells(RowId, ColId).Value) = vbDouble Then
        GetCellString = CStr(xlSheet.Cells(RowId, ColId).Value)
    End If
    MsgBox GetCellString
End Function

It is detecting a double, but it is only converting the numbers before the decimal point.

New_Coder24-Feb-06 5:32

New_Coder

24-Feb-06 5:32

How do I get the contents of the current field and row from a datagrid?

I have set up the code to run when I click on a record in the datagrid and this works until you sort the datagrid by clicking on the column headers.

dim test as string
test = TestGrid.CurrentRowIndex
COMPANY.Text = CODE_DataSet.Tables(CODE_TABLE).Rows(test).Item(0)

Any Idesas? Confused | :confused:

Dean_SF24-Feb-06 8:32

Dean_SF

24-Feb-06 8:32

What's happening is the currency manager for the datagrid is no longer pointing to the correct row after you perform a sort.

Here's a good description of the problem and a potential solution:

http://weblogs.asp.net/dmarsh/archive/2003/01/22/646.aspx[^]

Felipe Dalorzo24-Feb-06 8:35

Felipe Dalorzo

24-Feb-06 8:35

There is an ItemCommand event that fires when you click something in the datagrid
one of the arguments for that event is DataGridCommandEventArgs let's call it "e"

myItemCommand (object sender, DataGridCommandEventArgs e )
{
e.Item.DataItem[0]; /*This a DataRowViewRow*/
}

That is how you access a row in a datagrid.

Please let me know if I missunderstood your question or there's anything else I can help you with.

Greetings,

Felipe

New_Coder24-Feb-06 10:51

New_Coder

24-Feb-06 10:51

OK I think I have got what I am looking for with:

Dim MyCell As DataGridCell
Dim MyRow As Integer
Dim MyCol As Integer
Dim MyData As String

MyCell = TestGrid.CurrentCell
MyRow = (MyCell.RowNumber)
MyCol = (MyCell.ColumnNumber)

MyData = CODE_DataSet.Tables(CODE_TABLE).Rows(MyRow).Item(MyCol)

COMPANY.Text =MyData

This will work as long as there is data in the cell I clicked on.
How do I test for NULL?
What variable type do I need to set MyData so I dont get a compiler error
"Cast from type DBNull to type String in not valid"