Populate cells in an excel spreadsheet with information from an access database

[jedireg]

I have an access database which tracks receiving inspection data on outside
vend product.
In the event that something is found to be out of specification, I would
like to issue a corrective
action request ( aka 8D ) to the vendor. I have the excel spreadsheet developed
already and
want to click a button on the access form and have it open up the excel template,
place information
into certain cells ( can be names ranges ) and then save the excel file with
the current record number
and vendor name ( all of which is available on the access form).

I can find lots of information on pulling the data from the excel side or
for pushing data from excel into
access and I have done this in the past. This time however I want to push
the data from access into
the predesigned excel sheet.

I would appreciate any help in the direction that I need to be going.

Thanks

Glen

 

[jedireg]

I finally came across the following for which worked for my in case anyone
else is looking for something similar.
I had to set the focus to each of the controls on the form in order to access
the text property of them, so i cycled through each one assigning it to a
variable and passing it to the sub routine if there is a better way please
let me know.







Option Compare Database
Option Explicit


Sub OpenSpecific_xlFile(ByVal Description As String, ByVal QCContact As String,
ByVal RecordNum As String, ByVal PartNumber As String)
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "\\server\folder1\folder2\8-D Problem Solving Worksheet -
Blank.xlsx"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With

With oXL
.range("A5").Value = RecordNum
.range("B20").Value = "Company Quality"
.range("E20").Value = QCContact
.range("K20").Value = "Company Receiving Inspector"
.range("H29").Value = PartNumber
.range("N29").Value = "Company Name"
.range("B31").Value = Description
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Su