Extracting cell values from excel via ASP

[dsk525]

Hi All,

I have a problem in extracting the cell values from Excel file. My code is able to extract all the values except the currency values. Some how it is returning Null for these particular values. The cells that are in excel file that is returning a Null value store curreny values. "£"#,##0 this is the format of the cell. Is there any way I can go ahead and retrieve the value form these cells. I am able to retrieve all other values form the cells.

I would really appreciate if I could get any help in extracting these values for the execl cells.

=====================================================
Code used to extract values from excel file to be written into access database:

Function Compaq(strFileName As String) As Boolean
Dim strExcelString As String
Dim strExcelStatement As String
Dim srtAccessSQLStatement As String
Dim strCode As String
Dim strPartNum As String
Dim conExcelFile
Dim rstExcel
Dim count As Integer

On Error GoTo ErrorHandlerAll

'Create a connection object to the excel file.
strExcelString = "Data Source=" + strFileName + "; Extended Properties=Excel 8.0;"
Set conExcelFile = CreateObject("ADODB.Connection&quot
With conExcelFile
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = strExcelString
.Open
End With

'Create the recordset and populate the recordset with the
'data in the excel file.
Set rstExcel = CreateObject("ADODB.Recordset&quot
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'Need to make the spreadsheet selection automatic.
'strExcelStatement = "select * from [" + strSpreadSheetName + "$]"
strExcelStatement = "select * from [UK All Pricing$]"
With rstExcel
Set .ActiveConnection = conExcelFile
.Open strExcelStatement
End With


'Create a connection object to the database.
Set conUpload = CreateObject("ADODB.Connection&quot
conUpload.Open "HeminiDSN"

'Create a command object for the database table ProductInfo
Set cmdUpload = CreateObject("ADODB.Command&quot
cmdUpload.ActiveConnection = conUpload

'Create the recordset for productInfo table.
Set rsUpload = CreateObject("ADODB.Recordset&quot

'If it is not the end of the excel file move to the first record.
If Not rstExcel.EOF Then
rstExcel.MoveFirst
End If

Do While Not rstExcel.EOF
count = 0

'If the second field is empty increment the count.
If Len(Trim(rstExcel.Fields(1).Value)) > 0 Then
Else
count = count + 1
End If

'If the second field is "Supported Options" the increment the count.
If Trim(rstExcel.Fields(1).Value) = "Supported Options" Then
count = count + 1
End If

'If the third field is empty the increment the count.
If Len(Trim(rstExcel.Fields(2).Value)) > 0 Or rstExcel.Fields(2).Value = Null Then
Else
count = count + 1
End If

'If the fourth field is empty the increment the count.
If Len(Trim(rstExcel.Fields(3).Value)) > 0 Then
Else
count = count + 1
End If

'If the fourth field is "Part Number" then increment the count.
If Trim(rstExcel.Fields(3).Value) = "Part Number" Then
count = count + 1
End If

'If the fifth field is empty the increment the count.
If Len(Trim(rstExcel.Fields(4).Value)) > 0 Then
Else
count = count + 1
End If

'If the fifth field is "RBP" then increment the count.
If Trim(rstExcel.Fields(4).Value) = "RBP" Then
count = count + 1
End If

'If the sixth field is empty the increment the count.
If Len(Trim(rstExcel.Fields(5).Value)) > 0 Then
Else
count = count + 1
End If

'If the sixth field is "TBP" then increment the count.
If Trim(rstExcel.Fields(5).Value) = "TBP" Then
count = count + 1
End If

'If the seventh field is empty the increment the count.
If Len(Trim(rstExcel.Fields(6).Value)) > 0 Then
Else
count = count + 1
End If

'If the seventh field is "Band" then increment the count.
If Trim(rstExcel.Fields(6).Value) = "Band" Then
count = count + 1
End If

If count < 3 Then
strPartNum = rstExcel.Fields(3).Value
srtAccessSQLStatement = &quot;select ProductDesctiption,Category,&quot; + _
&quot;ProductCode,CompanyName from ProductInfo where &quot; + _
&quot;ProductCode = '&quot; + strPartNum + &quot;'&quot;
Debug.Print srtAccessSQLStatement
cmdUpload.CommandText = srtAccessSQLStatement
cmdUpload.CommandType = 1
rsUpload.Open cmdUpload, , 0, adLockPessimistic

'Code for adding and updating the records.
If rsUpload.EOF Then
'Add a new record.
With rsUpload
.AddNew
.Fields(&quot;ProductDesctiption&quot.Value = rstExcel.Fields(1).Value
.Fields(&quot;Category&quot.Value = rstExcel.Fields(2).Value
.Fields(&quot;ProductCode&quot.Value = rstExcel.Fields(3).Value
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
'Missing updating for currency values.
'.Fields(&quot;ProductEstimatedCost&quot.Value = MId(rstExcel.Fields(4).Value)
.Fields(&quot;CompanyName&quot.Value = &quot;Compaq&quot;
'.Fields(&quot;Band&quot.Value = rstExcel.Fields(6).Value
.Update
End With
Else
'update a new record.
With rsUpload
.Fields(&quot;ProductDesctiption&quot.Value = rstExcel.Fields(1).Value
.Fields(&quot;Category&quot.Value = rstExcel.Fields(2).Value
.Fields(&quot;ProductCode&quot.Value = rstExcel.Fields(3).Value
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
'Missing updating for currency values.
.Fields(&quot;CompanyName&quot.Value = &quot;Compaq&quot;
'.Fields(&quot;Band&quot.Value = rstExcel.Fields(6).Value
.Update
End With
End If

rsUpload.Close
End If

rstExcel.MoveNext
Debug.Print count
Loop

Compaq = True
Exit Function

ErrorHandlerAll:
Compaq = False

End Function

 

[forecasting]

'.Fields(&quot;ProductEstimatedCost&quot.Value = MId(rstExcel.Fields(4).Value)

Is this the line that doesn't work? Your example has it commented out. Also MId() isn't a complete function.

 

[dsk525]

It is not evening picking up the £ or $ values. The MId() function does not work so I have commented it out.

 

[forecasting]

can you get the formula for field 4?