^^^Help Wanted: Excel to Access^^^

[dsk525]

Hi all,

I have an excel sheet with a currency field, and I am taking the data from the currency field and writing it into the access database (via ASP). However, when it writes to the Access database it writes it as "Null" values.

Any help would be appreciated on this!

Thank you.

Here is the code:

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