#1
|
|||
|
|||
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 Could anybody please point me in the right direction on how to do this? Many thanks in advance. bb |
#2
|
|||
|
|||
Depends exactly what you mean but maybe:
objWbk.worksheets("Exec Summary").UsedRange.Copy |
#3
|
|||
|
|||
Quote:
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) Many thanks |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
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 |
#7
|
|||
|
|||
Can you post a sample sheet?
|
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
That's the correct reference. sorry I have no idea - it works for me!
|
#14
|
|||
|
|||
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 |