Copying a variable size table from Excel via VBA

-=bb=- · #1 05-29-2013, 08:29 AM

Hi everyone,

Not sure if this falls more into the Excel Programming but since I am doing it from PowerPoint, I hope it is OK here.

I want to copy an Excel table, of variable size, into a slide in my presentation. Easy enough to do manually, but I would like to automate the process if at all possible.

I am trying lots of variations but getting no joy at all. Its no problem if I have a table of a known size, I can just

Code:

 
objWbk.worksheets("Exec Summary").Range("G18:H21").Copy

but I am failing quite badly to get it to work with any kind of variable sized table.

Could anybody please point me in the right direction on how to do this?

Many thanks in advance.

bb

JohnWilson · #2 05-29-2013, 09:47 AM

Depends exactly what you mean but maybe:

objWbk.worksheets("Exec Summary").UsedRange.Copy

-=bb=- · #3 06-03-2013, 03:34 AM

Quote:

Originally Posted by JohnWilson

Depends exactly what you mean but maybe:

objWbk.worksheets("Exec Summary").UsedRange.Copy

Hi John,

Thank you very much for taking the time to reply - sorry for the lateness of my reply I have been out of the office for a few days on personal leave.

Unfortunately using the method that you mention above, I get an Object Required Error.

My code looks like this currently

Code:

 
With objWbk.worksheets("Surveyed Area by Area")
        Range("D14").Activate
        UsedRange.Copy
EndWith
Application.ActiveWindow.View.PasteSpecial (ppPasteBitmap)

I am sure it is something particularly numpty-ish that I'm doing!

Many thanks

-=bb=- · #4 06-03-2013, 03:45 AM

Sorry - I should have mentioned that the table is not the only item on the worksheet - hence the D14 activate to select the cell in the top right of the table.

bb

JohnWilson · #5 06-03-2013, 08:28 AM

Maybe this then:

Dim c As Integer
Dim r As Integer
c = Range("D14").End(xlToRight).Column
r = Range("D14").End(xlDown).Row
Range(Cells(14, 4), Cells(r, c)).Copy

-=bb=- · #6 06-03-2013, 08:35 AM

Quote:

Originally Posted by JohnWilson

Maybe this then:

Dim c As Integer
Dim r As Integer
c = Range("D14").End(xlToRight).Column
r = Range("D14").End(xlDown).Row
Range(Cells(14, 4), Cells(r, c)).Copy

Hi John,

Once again thank you for taking the time to help out.

Unfortunately using this method fails with a run-time error '6' : Overflow on the r = Range("D14").End(xlDown).Row line.

If it helps debug the problem the table is only ever four columns wide. Hoever before it flakes out, the value of c is 16384

Sorry this is proving to be a little tricky ...

bb

JohnWilson · #7 06-03-2013, 09:10 AM

Can you post a sample sheet?

-=bb=- · #8 06-04-2013, 02:20 AM

https://www.dropbox.com/s/wmrxudgamy3lquh/Book1.xlsx

That is one example of one of the sheets in the workbook that I am trying to extract the table, via Powerpoint VBA, into a slide.

It might be easier to just copy the table from within Excel and paste it as a PNG on the same worksheet and then I can just grab it by name rather than trying to do it from within Powerpoint?

I'm sure you must be able to paste with a specific shapename?

Once again, thank you for your time

bb

JohnWilson · #9 06-05-2013, 05:06 AM

The code I posted works perfectly with your file for me

Sub chex()
Dim c As Integer
Dim r As Integer
With ActiveWorkbook.Worksheets("Surveyed Area by Area")
c = Range("D14").End(xlToRight).Column
r = Range("D14").End(xlDown).Row
Range(Cells(14, 4), Cells(r, c)).Copy
End With
End Sub

Are you running in Excel or PowerPoint?
If the latter have you set an Excel Reference

-=bb=- · #10 06-05-2013, 05:13 AM

Hi John,

I am working in powerpoint. The Excel reference is set as follows :

Set objXL = CreateObject("Excel.application")
Set objWbk = objXL.workbooks.Open(Application.ActivePresentatio n.Path & "\Report Creator v2.xlsm")

Is that where my error is?

bb

JohnWilson · #11 06-05-2013, 07:33 AM

That creates an Excel object but doesn't necessarily set a reference to Excel Code. (You would do this in the VBE Tools > References)

If you didn't do this xlToRight and xlDown mean nothing to PowerPoint (they are Excel constants) try this instead.
c = Range("D14").End(-4161).Column
r = Range("D14").End(-4121).Row

-=bb=- · #12 06-05-2013, 08:45 AM

Hi John,

I already have Excel 14.0 Object Library, Office 14.0 Object Library, OLE Automation, PowerPoint 14.0 Object Library and Visual Basic for Applications checked in References. I cannot locate one for Excel Code.

Unfortunately code still causes and overflow at the line

r = Range("D14").End(-4121).Row

and C is still being set to 16384.

Kindest regards
bb

JohnWilson · #13 06-05-2013, 08:59 AM

That's the correct reference. sorry I have no idea - it works for me!

-=bb=- · #14 06-05-2013, 09:01 AM

Thank you anyway for taking the time to try and help out. Much appreciated.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating a table for a variable number of rows	OllieOnline	Mail Merge	1	03-27-2013 02:48 PM
Using macro to add variable number of rows to a protected word table	Julia	Word Tables	1	01-09-2013 06:04 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
Copying table styles	Ulodesk	Word	1	09-13-2011 03:31 PM
copying a table	Gunfighter	Word Tables	1	05-31-2011 03:34 PM