Exporting field to excel cell

J

James Simpson

Hello everyone,
I am currently working with VBA in Access 97 to accomplish the following:
Export one field value to an excel cell (say A1), however when I attempt to
accomplish this using the following code :
oWS.Cells(iEndCol, 1).Value = rstPurchases![Lot #]
Where oWS is a object that points to a fully qualified excel worksheet,
and rstPurchases is a DAO.Recordset containing a query I issue, I receive
the following error message :
Runtime Error '1004':
Application-defined or object defined error
Any suggestions as to how to retrieve a field from a recordset and put it
into a excel cell?

The following is a complete listing of the code that I used (in case you
need it for reference) :
CreateExcelObj
Dim iProductCounter As Integer
Dim asProductsToExport() As String
Dim asUnitsToUse() As String
Dim oWS As Object
Dim oWB As Object
Dim CurDbs As Database
Set oWB = gobjExcel.Workbooks.Add

' gobjExcel.Workbooks.Add
' Set oWS = gobjExcel.ActiveSheet

Call GetAllProductsToExport(asProductsToExport)
Call GetAllUnits(asUnitsToUse)

' This loop will loop through all possible products to export
For iProductCounter = 0 To UBound(asProductsToExport)
Dim iCurUnit As Integer
Set oWS = oWB.Worksheets.Add
Set CurDbs = Application.CurrentDb
' oWS.Name = asProductsToExport(iProductCounter)
' Here we set the headings for the current workbook
oWS.Cells(1, 1).Value = "Dealer"
oWS.Cells(1, 2).Value = "Variety"
oWS.Cells(1, 3).Value = "Unit"
oWS.Cells(1, 4).Value = "Ped/Stat"
oWS.Cells(1, 5).Value = "Tr"
oWS.Cells(1, 6).Value = "Lot #"
oWS.Cells(1, 7).Value = "Transferred"
oWS.Cells(1, 8).Value = "Delivered"
oWS.Cells(1, 9).Value = "DO"
oWS.Cells(1, 10).Value = "Order #"
For iCurUnit = 0 To UBound(asUnitsToUse)
Dim sSQLCommand As String ' SQL Select command string
sSQLCommand = "SELECT * FROM [Customer Order Details] WHERE
([unit] = '" + asUnitsToUse(iCurUnit) + "')"
Dim rstPurchases As DAO.Recordset
Dim sCurLotNum As String
Dim iStartCol As Integer
Dim iEndCol As Integer

Set rstPurchases = CurDbs.OpenRecordset(sSQLCommand)
iStartCol = 0
iEndCol = 0

While (Not (rstPurchases.EOF))
If sCurLotNum <> rstPurchases![Lot #] Then
sCurLotNum = rstPurchases![Lot #]

End If
Dim sOrderCommand As String
Dim rstCustomer As DAO.Recordset
' SORT BY [Lot #]
sOrderCommand = "SELECT [Cust] FROM [order number] WHERE
orderid = '" + rstPurchases![order#] + "'"
Set rstCustomer = CurDbs.OpenRecordset(sOrderCommand)






oWS.Cells(iEndCol, 1).Value = rstCustomer.Fields(0).Value
oWS.Cells(iEndCol, 2).Value =
asProductsToExport(iProductCounter).Value
oWS.Cells(iEndCol, 3).Value = asUnitsToUse(iCurUnit)
oWS.Cells(iEndCol, 4).Value = rstPurchases![Pedigree/Status]
oWS.Cells(iEndCol, 5).Value = rstPurchases![Treatment]
oWS.Cells(iEndCol, 6).Value = rstPurchases![Lot #]
oWS.Cells(iEndCol, 7).Value = rstPurchases![QTY Transferred]
oWS.Cells(iEndCol, 8).Value = rstPurchases![QuantityDel]
oWS.Cells(iEndCol, 9).Value = rstPurchases![DO]
oWS.Cells(iEndCol, 10).Value = rstPurchases![order #]
rstPurchases.MoveNext
iEndCol = iEndCol + 1
Wend
Next iCurUnit
Next iProductCounter
gobjExcel.Visible = True

Thanks in advance.

Sincerely,

James Simpson
Straightway Technologies Inc.
 
Top