I am going to gripe for 1 second. If I see another text that shows how to create a new spreadsheet in excel from Access using automation
I am going to go nuts! They (all the "fat books" that I have) talk about how to create a spreadsheet and send some data to it, but I need to open an existing spreadsheet.

I will use early binding at first, then replace with late binding later, but any code snippets would be GREATLY appreciated.

Pseudocode From Access:

Open existing file "C:\.....\myExcelFile.xls"
set MyExcelFile.worksheet1(J:8) = intMyAccessVariable
let excel crunch...
dblMyAccessAnswerVariable = myExcelFile.worksheet4(B:10)


Something like that. While you guys noodle that, I'll try the archives.

Someone PLEASE SAVE ME from automation [censored]!

Thanks,
Rob

I feel your pain. It seems most books just repeat the same old example with new clothes. How about this from my examples database:


Here is another great resource - Ken Snell's Excel Import/Export page. Go to http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm and then you can re-direct to the appropriate page you need. Good luck!

Rob,

Without knowing more about what you're doing, it's hard to tell, but it looks like you're just using Excel to perform some calculation(s), and then take the result out of Excel back into Access. I'm wondering if this is because you are using worksheet functions that are only available in Excel.

If this is the case, you might want to consider just using Excel functions in Access instead, and avoid using the workbook entirely.

For Example.

Hope this helps,

Dennis

No, Dennis, the spreadsheet is a complex engineering spreadsheet that we need to be able to manipulate from Access.
We insert values, let it work, then pull off the results ( back into Access).

The following code answers my original question:

Public Sub MyTest()

'This sub does the following:
'1. Opens up an existing Excel file
'2. Manipulates data in a cell on Sheet1
'3. Excel then does some computations
'4. We pick up the answer from another worksheet in the same file

'I'm not sure if I'll have to put in some DoEvents in the code between the assignment of the cell in Excel
'and the retrieval of the answer, but for a simple spreadsheet, this works perfectly.
'Obviously this code will be extended, but this answers my original question.


'Thanks to John Mishefske, UtterAccess, and to Ken Snell: http://www.accessmvp.com/KDSnell/default.htm
'This specific routine was based on (mostly copied) from:
'http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim blnEXCEL As Boolean

blnEXCEL = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
' TRUE would open in read-only mode (which lets you edit the file but not overwrite the file)
' FALSE allows me to edit it and save it.
Set xlw = xlx.Workbooks.Open("C:\_WorkFiles\UTD\RVS\2010-10-17\ExcelTestSheet.xls", , False) ' TRUE would open in read-only mode

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("Sheet1")

' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("B5") ' this is the first cell that contains data

'manipulate the data in the cell
xlc = xlc + 4

'The spreadsheet then does some massive contortions and calculations
'Then pick up the answer from Sheet2, Cell D6

Dim varMyAnswer As Variant
varMyAnswer = xlw.Worksheets("Sheet2").Range("D6")

MsgBox "Answer is: " & varMyAnswer



' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
' I think FALSE would not save, TRUE saves
xlw.Close True

Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing


End Sub