Hi,
I get a bizarre bug when I try to automate a new Excel workbook with VBA code in Access. When I try to create an name for a cell( MyWorkBook.Names.add),the function return no error but the name seems not to be properly
defined, so when I use the name I receive 2 kind of error:
Case 1:
Excel formula: “=val1+val2” give the #NAME? as a answer in the cell with the formula (invalid reference to cells)
Case 2:
VBA code: oApp.range(“val1”).value generates runtime error 1004 method X of of object Y failed.
When I check the result in the worbook created, I can see that the name is defined in the “Define Name” dialog box but when I click the cell which I gave a name, it still show an A1 address
(A1 instead of val1, for instance)
‘sample code I use
Dim oApp As New Excel.Application
oApp.Visible = True
‘Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
oApp.Workbooks.Add
oApp.Range(“A1”).Select
oApp.ActiveCell.FormulaR1C1 = “37”
oApp.Range(“A2”).Select
oApp.ActiveCell.FormulaR1C1 = “11”
oApp.Range(“A1″).Select
oApp.ActiveWorkbook.Names.Add Name:=”Val1″, RefersToR1C1:=”=Sheet1!R1C1”
oApp.Range(“A2″).Select
oApp.ActiveWorkbook.Names.Add Name:=”Val2″, RefersToR1C1:=”=Sheet1!R2C1”
oApp.Range(“A3”).Select
oApp.ActiveCell.FormulaR1C1 = “=Val1-Val2”
oApp.Range(“A4”).Select
oApp.ActiveCell.FormulaR1C1 = “=Val1-Val2”
MsgBox oApp.Range(“Val1”).Value
MsgBox oApp.Range(“Val2”).Value
‘
Strangely, I get the bug with Access2000 running on WinXP, Access XP running on WinXP and Access XP running on Win 2000 BUT not on Access 2000 running on Win 2000. Also, any version of Access on Win9x seems to work(tried my code on many diferent computer)… Frankly, I am not sure what causes the bug.
Thanks for any help!!
for additionnal information on my bug, here’s my email: franklejuste@mac.com