trouble with VBA between Access and Excel (1 Viewer)

Q

qwertyjjj

Registered User.

Local time

Yesterday, 23:06

Joined

Aug 8, 2006

Messages

262

• Sep 14, 2006

• #1

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 ?

 

Uncle Gizmo

Nifty Access Guy

Staff member

Local time

Today, 07:06

Joined

Jul 9, 2003

Messages

16,292

• Sep 14, 2006

• #2

qwertyjjj said:

- it's all standard VBA isn't it ?

Click to expand...

Well basically VBA is the same it is true, however you do not have "cells" or "ranges" in Access, and that is where your problem lies.

Just imagine if you opened a word Doc, you could have a macro for doing something to a paragraph or a page, but it would not work in excel would it!

 

Q

qwertyjjj

Registered User.

Local time

Yesterday, 23:06

Joined

Aug 8, 2006

Messages

262

• Sep 14, 2006

• #3

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.

 

Uncle Gizmo

Nifty Access Guy

Staff member

Local time

Today, 07:06

Joined

Jul 9, 2003

Messages

16,292

• Sep 14, 2006

• #4

Sorry I don't do much in excel, but doesn't activecell refer to the highlighted Cell, and is the cell highlighted?

 

B

Brian1960

Brian1960

Local time

Today, 07:06

Joined

Aug 13, 2004

Messages

141

• Sep 14, 2006

• #5

Setting Formula

Code:Copy to clipboard

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

Hope this helps

 

Q

qwertyjjj

Registered User.

Local time

Yesterday, 23:06

Joined

Aug 8, 2006

Messages

262

• Sep 14, 2006

• #6

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.

 

Last edited: Sep 14, 2006

D

dgkindy

Registered User.

Local time

Today, 02:06

Joined

Feb 22, 2007

Messages

34

• Apr 28, 2007

• #7

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?

 

Q

qwertyjjj

Registered User.

Local time

Yesterday, 23:06

Joined

Aug 8, 2006

Messages

262

• Apr 28, 2007

• #8

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

 

Uncle Gizmo

Nifty Access Guy

Staff member

Local time

Today, 07:06

Joined

Jul 9, 2003

Messages

16,292

• Apr 28, 2007

• #9

I found these two knowledge base articles by Microsoft very helpful when I was first manipulating spreadsheets from MS Access.

http://support.microsoft.com/default.aspx?scid=kb;en-us;246335

http://support.microsoft.com/default.aspx?scid=kb;EN-US;247412

 

Last edited: Apr 28, 2007

D

dgkindy

Registered User.

Local time

Today, 02:06

Joined

Feb 22, 2007

Messages

34

• Apr 28, 2007

• #10

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

Smeghead

Local time

Yesterday, 23:06

Joined

Jan 12, 2001

Messages

32,059

• Apr 28, 2007

• #11

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.