Please pardon the "whine"... Access-to-Excel Automation, Office 2003
Oct 20 2010, 02:09 PM
From: Northern Virginia
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]!
Oct 20 2010, 02:37 PM
From: Milwaukee, WI
I feel your pain. It seems most books just repeat the same old example with new clothes. How about this from my examples database:
Dim XL As Object
'Dim XL As Excel.Application, xlw As Excel.Worksheet
Set XL = CreateObject("Excel.Sheet")
XL.Application.Visible = True
.ActiveSheet.name = "pivot"
' comment out to leave application open
'Set XL = Nothing
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!
Oct 20 2010, 03:23 PM
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.
Hope this helps,
Oct 20 2010, 03:47 PM
From: Northern Virginia
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:
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
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
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
|Go to Top · Lo-Fi Version||Time is now: 19th May 2013 - 01:51 PM|