Okay, after rethinking this many times, this is what I need some major help on. Basically I need to update an Excel sheet with the current record on an open form. The issue is the excel sheet has specific cells that need updated by the record in view. How the H E double hockey sticks to I even begin to do this. My previous attempt was to do a createobject string in VBA but couldn't figure out how to update the cells from the record on the form. I'm pretty much out of ideas and any help would be appreciated.


Thanks

This is not easy to do.

First develop a query that fetches the data being displayed by the form.

Then in the click event of a command button, you will need to:

Open the query into a recordset.

Open Excel programmatically.

Then using that copy of Excel, open the sheet of interest.

Then set the cell values to the corresponding values in the recordset.

Close the sheet, saving the changes.

Close the recordset.

End the code.

I had discovered an easier way to do this:

Sub CommandButton1_Click()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Set xlApp = CreateObject("excel.Application")
Set xlBook = xlApp.Workbooks.Open("\\Mcuspndlfs13.mcdsus.mcds.usmc.mil\c163\1_MEF_2\I_MHG\SUPPLY\Databases\Files\GCPCFRM.XLS")
xlBook.Application.Visible = True

xlBook.Application.Cells(2, 5).Value = [doc/callNo]
xlBook.Application.Cells(3, 5).Value = [Price]
xlBook.Application.Cells(2, 7).Value = [POCName]
xlBook.Application.Cells(3, 7).Value = [PurchaseDt]
xlBook.Application.Cells(4, 7).Value = [PurchClerk]
xlBook.Application.Cells(14, 2).Value = [Qty]
xlBook.Application.Cells(14, 3).Value = [UI]
xlBook.Application.Cells(14, 4).Value = [ItemDescription]
xlBook.Application.Cells(14, 7).Value = [PartNumb]
xlBook.Application.Cells(14, 8).Value = [UnitPrice]
xlBook.Application.Cells(21, 2).Value = [Justification]

xlBook.Application.Quit
Set xlApp = Nothing
End Sub



Yay! Thank you for the fresh look at things fkegley!