Field from Access into already open Excel file cell.

[desperateUser]

I have a button on an Excel template that pushes the worksheet data into a record in Access. Access then creates an "order number" for the record and I need that order number pushed BACK into the Excel file that's open so the staff can print, save and close the excel spreadsheet and be done with everything.

I'm successfully opening Access and putting the Excel data in a record, I just don't know how to get that order number back into the cell it needs to go to...

Any points in the right direction? Thanks!

 

[PHV]

Have a look at the Access.Application.DLookUp function.
BTW, how are you putting the Excel data in a record ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[easyit]

Would it be suitable for you to pull this data out instead of pushing?


Then you could use the folowing:

Sub ADOImportFromAccessTable(ByVal DBFullName As String, TableName As String, TargetRange As Range)
Dim intColIndex  As Integer
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = DBFullName
cnn.Properties("Jet OLEDB:System database") = "C:\Program Files\Common Files\System\SYSTEM.MDW"
cnn.Open UserId:="Admin", Password:=""
Set TargetRange = TargetRange.Cells(1, 1)
Set rst = New ADODB.Recordset
With rst
    .Open TableName, cnn, adOpenStatic, adLockOptimistic
    If Not .EOF Then
        .MoveFirst
        For intColIndex = 0 To rst.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
        Next
        TargetRange.Offset(1, 0).CopyFromRecordset rst ' the recordset data
   End If
   .Close
End With
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub

As a tablename you could pass either the name of the query/table or a sqlstring. The latter is handy, because it alows you to build the string with references to excel cells.


EasyIT

 

[desperateUser]

This is how I'm getting the Excel data into Access...

Sub mcrOpenAccess()

    Dim RetVal
    Dim db As Database
    Dim Rs As Recordset

    
    RetVal = Shell("""C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"" ""J:\PROJECTS\PTDb\SRT.mdb""", 1)

    Set db = OpenDatabase("J:\PROJECTS\PTDb\SRT.mdb")

' open the database
    
    Set Rs = db.OpenRecordset("tblLPOs")
    
' Add a new record into the database

    With Rs
        .AddNew ' create a new record
        
        ' add values to each field in the record
        Rs("Account") = Range("Sheet2!F2").Value
        Rs("Center") = Range("Sheet2!G2").Value
        Rs("Requestor") = Range("Sheet2!H2").Value
        Rs("Comments") = Range("Sheet2!M2").Value
        .Update
    End With
End Sub

I will go look up your suggested DLookup Function.

 

[desperateUser]

EasyIT
Thank you for the code! I'm trying to glean what I can out of it to get that one field. You can see from my code that the bulk comes from Excel in the first place.

Thanks

 

[PHV]

Something like this ?
With Rs
.AddNew ' create a new record
' add values to each field in the record
!Account = Range("Sheet2!F2").Value
!Center = Range("Sheet2!G2").Value
!Requestor = Range("Sheet2!H2").Value
!Comments = Range("Sheet2!M2").Value
.Update
.Bookmark = .LastModified
Range("some cell").Value = ![order number]
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[desperateUser]

PHV,
Please forgive my ignorance, would this be an additional way to get the data from Excel to Access?

This morning I was musing about putting the function that creates the order id into an Excel macro to start with THEN put the data in Access. How different would the function have to be?