[RESOLVED] Accessing Excel range from Powerpoint

[Rebam98]

Hi-

I'm working on something and cannot for the life of me figure out why it won't work.

I need to pull in a range of cells from Excel into powerpoint, to complete a presentation. This is the code I have. It's not complete because I have been stepping through the code until I get to the end, and it give me the generic '1004' application or object defined error.

The problem comes in selecting a range in Excel. I can get cell "A1," i.e. a single cell, just fine but when I try to define a larger range, it crashes. I've racked my brain for every possible thing I could do. I tried setting up a named range and referencing it (in the commented out code below) to no avail.

The code below is a little simpler than what I am doing, to isolate out the main problem. Basiclaly I just need to be able to pick out a range of cells in Excel from PowerPoint.

Please help.

sub get_excel_range()


Dim amberwrkbook As Excel.Workbook



Set amberwrkbook = GetObject("H:\Notes\Learning VBA\Making Summary to play with.xls")

amberwrkbook.Application.Sheets(24).Range("A1").Select '<-- works just fine



amberwrkbook.Application.Sheets(24).Range(Cells(1, 1), Cells(8, 8)).Copy '<-- CRASH, FAIL BURN!

'amberwrkbook.Application.Names.Add "Summary_Sheet", Range(Cells(1, 1), Cells(8, 8))


'amberwrkbook.Application.Sheets(24).Range("Summary_Sheet").Copy

......

End Sub

[cssriraman]

Welcome to VB Forums!

Always use [vbcode] your code goes in here [/vbcode] tags when posting code.

amberwrkbook.Application.Sheets(24).Range(Cells(1, 1), Cells(8, 8)).Copy '<-- CRASH, FAIL BURN!

You can use the following technique:

To copy A1 to B10,

amberwrkbook.Application.Sheets(24).Range("A1:B10").Copy

[Rebam98]

Thanks! I just had someone help me figure out this problem. I can use A1:H10 except that I want to specify a range with a variable in it, e.g. range(cells(1,1),cells(variable,8)) Someone else said I needed a dot (.) in front of each of the cells to make it work. I think there is more to the story though as I had to directly copy his code from start to finish to get mine to work ... I don't completely understand why my way didn't work ...but I do have something that works now.

[cssriraman]

When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.

[zaza]

amberwrkbook.Application.Sheets(24).Range(Cells(1, 1), Cells(8, 8)).Copy

Incidentally, look how you're referencing the Range. You use "amberwrkbook.application.Sheets(24)."

Why would you not then use that for the Cells? That is why it doesn't work: you are not telling it that you need to look for the cells elsewhere as well. Of course, this is irrelevant when you hardcode the range as "A1" etc.

zaza