OLE Automation help required

G

Graham Naylor

Hi,

I need to knock a small piece of code together attached to a button on an
Access Form that will open Excel, get some information from a few known
cells, then close Excel down again (OLE?).

Can anyone point me to some code on the net somewhere that would help me get
started as I have never tried to do anything like this before.

Thanks

Graham
 
T

Tracy

Graham,

First you need to create your table in Access with the desired fields. Then
create a macro with the action TransferSpreadsheet. Enter all your
information that apply's.
It's pretty self explanatory.

You could create a macro in the Excel file such as on control a the excel
sheet closes perhaps... I think that will get you going though.

Thanks.

Tracy

To
 
G

Graham Naylor

Hi,

Thanks, but it doesn't really do what I need. I need to be able to get one
cells information so I can modify it before writing it to file as an import
won't allow me to chang the format of the data while importing it (mainly as
Access won't hold thousandths of a second!)

Thanks

Graham
 
J

John Nurick

Hi Graham,

Sub GetValuesFromExcel()
'Demonstrates taking values from particular cells in
'Excel workbook and putting them into Access table
'using automation and recordset operations

Dim xlApp As Excel.Application
Dim wbkW As Excel.Workbook
Dim rsR As DAO.Recordset
Dim F As DAO.Field

Set xlApp = CreateObject("Excel.Application")
Set wbkW = xlApp.Workbooks.Open("D:\Folder\Filename.wks", , True)
Set rsR = CurrentDb.OpenRecordset("TableName")

rsR.AddNew
With wbkW.Worksheets("SheetName")
'get values from cells and put them in fields in the recordset
rsR.Fields(1).Value = .Cells(5, 1).Value
rsR.Fields(2).Value = .Cells(5, 3).Value
rsR.Fields(3).Value = .Cells(7, 6).Value
End With
rsR.Update

rsR.Close
Set rsR = Nothing
Set wbkW = Nothing

'close this and any other workbooks that may have opened
Do While xlApp.Workbooks.Count > 0
xlApp.Workbooks(1).Close False
Loop
xlApp.Quit
Set xlApp = Nothing
End Sub

See also http://support.microsoft.com/default.aspx?scid=kb;en-us;247412
http://www.mvps.org/access/modules/mdl0006.htm
 
Top