trouble with VBA between Access and Excel

[qwertyjjj]

Hi
I am using some VBA from ACcess 2003 to export data to Excel 2003.

I then run some tidying up code, which I have created by recording a macro in Excel 2003 and then pasting the code back to Access.

Unfortunately stuff like this doesn't work:
ActiveCell.FormulaR1C1 = "1"
Range("H2").Select

It errors in Access. I have to change the code to:
Range("H2").Value = "1"
Range("H2").Select

etc.

I have added the Excel 11.0 library and Office 11.0 library. AM I missing another reference or does the macro code from Excel not translate well with Access - it's all standard VBA isn't it ?

 

[qwertyjjj]

Sorry, I should have explained that I am setting references to the worksheet:

' Open an existing spreadsheet
Set appExcel = GetObject("C:\A.xls")
Set workSheet = appExcel.Worksheets("A")

' Show spreadsheet on screen
appExcel.Application.Visible = True
appExcel.Parent.Windows(1).Visible = True

With workSheet

...code as above

All the code works fine when I use code such as .Range("H2").Value = "1"

It's just that the macro code created from Excel:
.ActiveCell.FormulaR1C1 = "1"

is necer recognised.

Now, there must be something missing for it to not recognise the Excel code.

 

[Brian1960]

Setting Formula

objExcel.ActiveCell.Formula = "=SUM(A5:A25)"

Hope this helps

 

[qwertyjjj]

Thanks.
WHat I'm trying to get at is why the Excel macro code is not recognised. The Excel macro code should be correct no matter what and should be recognised by Access immediately shouldn't it?

Therefore worksheet.ActiveCell.FormulaR1C1 = "1" should work directly without me having to edit it ?

It also doesn't recognise things like:
Selection.PasteSpecial etc. etc.
created by Excel VBA macro recording

I have to edit this to
Range("A1:B10").PasteSPecial etc. etc.

 

[dgkindy]

Solution?

Did you ever figure out the solution to this problem. I just come across the same problem and now trying to figure out how to fix?

 

[qwertyjjj]

I think I just referenced the cells directly.
What code are you using?

 

[dgkindy]

In the end I ended up using code as follows:

Row=2
Do loop
Cells(Row,1)= variable
Row= Row +1
Until EOF

I tried using the activecell but it does not appear to work.

 

[boblarson]

When using Excel objects within Access you really need to use the fully qualified reference:

If you have xlWB as a workbook object, then to reference the activesheet you should use:

xlWB.Activesheet.Range("A1:B2").Select

Etc.

If you don't, not only do you run the risk that it won't work (it may work) but that when you close your objects that an instance of Excel will hang around until you totally close Access and that can cause you grief if you need to run your code again before closing the database.