VB6 and excel

Waldermort

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 Quick

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 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. :) So, did you mean to do this? xlApp.Workbooks(FileName).Close 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?

Waldermort

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 Quick

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 Rathi

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

Waldermort

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

Waldermort

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.