Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Office > PowerPoint

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-29-2013, 08:29 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default Copying a variable size table from Excel via VBA

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
Reply With Quote
  #2  
Old 05-29-2013, 09:47 AM
JohnWilson JohnWilson is online now Windows 7 64bit Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,706
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

Depends exactly what you mean but maybe:

objWbk.worksheets("Exec Summary").UsedRange.Copy
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #3  
Old 06-03-2013, 03:34 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

Quote:
Originally Posted by JohnWilson View Post
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
Reply With Quote
  #4  
Old 06-03-2013, 03:45 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 06-03-2013, 08:28 AM
JohnWilson JohnWilson is online now Windows 7 64bit Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,706
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

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
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #6  
Old 06-03-2013, 08:35 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

Quote:
Originally Posted by JohnWilson View Post
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

Last edited by -=bb=-; 06-03-2013 at 08:36 AM. Reason: Extra info
Reply With Quote
  #7  
Old 06-03-2013, 09:10 AM
JohnWilson JohnWilson is online now Windows 7 64bit Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,706
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

Can you post a sample sheet?
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #8  
Old 06-04-2013, 02:20 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 06-05-2013, 05:06 AM
JohnWilson JohnWilson is online now Windows 7 64bit Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,706
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

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
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #10  
Old 06-05-2013, 05:13 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 06-05-2013, 07:33 AM
JohnWilson JohnWilson is online now Windows 7 64bit Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,706
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

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
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #12  
Old 06-05-2013, 08:45 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 06-05-2013, 08:59 AM
JohnWilson JohnWilson is online now Windows 7 64bit Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,706
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

That's the correct reference. sorry I have no idea - it works for me!
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #14  
Old 06-05-2013, 09:01 AM
-=bb=- -=bb=- is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: May 2013
Posts: 8
-=bb=- is on a distinguished road
Default

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

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 07:03 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft