Excel Cells to PPT Cells- Two Variable Ranges

[mlexcelhelpforum]

Hi all,

I've attempted to write a macro that copies individual cells from Excel sheets to individual cells in tables that I have set up in PowerPoint:

1. For each second row of data in Excel, the macro creates a slide with an empty PowerPoint table in PowerPoint. (The originally empty PowerPoint tables on all slides are identical.)
2. Then it is supposed to put the data from each cell in each of those second Excel rows into the proper cell of the table on each PowerPoint slide. So, the 1st row cells are populated into the designated cells of the 1st slide, the 3rd row cells are populated into the designated cells of the 2nd slide, and so on...

I'm not sure of all the VBA options, but I've been attempting to do this primarily with For Next Loops:

- I successfully created a For Next Loop for Step 1 above. A slide has been created for each second row of data in my Excel sheet.
- However step two has two loop ranges: it must loop through each slide in the PowerPoint presentation, and at the same time it must loop through every second row of data in the Excel sheet-- because the macro populates the slides with the data of each second row of the Excel sheet. You see?

I see an option of successive or nested loops, but no option of two value ranges looping at the same time. Perhaps I shouldn't be using For Next Loops?

Please Login or Register  to view this content.

As you can see I'm having trouble finding a way to loop through b and y simultaneously, so that the 1st row of data goes on the first slide, the 3rd row of data goes on the second slide, the 5th row of data goes on the 3rd slide, etc.

If the thread title does not correctly describe the contents, please suggest how to rephrase it.

Thank you,

ML

[snb]

Please Login or Register  to view this content.

[mlexcelhelpforum]

Hello there, snb!

I just tried it out.

At this line in the

Please Login or Register  to view this content.

I got this error message:

Run-time error '438':
Object doesn't support this property or method.

[snb]

It's a nice task for you to find out how to write in ot a certain cell in a table in PPT directly.

try separately:

Please Login or Register  to view this content.

if you make a reference in Excel's VBEditor to the ppt-object library you can enter this code and get the help of intellisense (automatic completion).

[snb]

I improved/adapted my first suggestion using Intellisense.

[mlexcelhelpforum]

Hello again,

What is OT?

I tried the second code you posted, but got the same error. I have already selected the Microsoft PowerPoint 14.0 object library.

Your code looks great! Short and simple. If only I could get it to work.

ML

[snb]

in ot is a typo for 'into'

[mlexcelhelpforum]

Oh, now I think I get what you mean.

Yes, I have written text into a PowerPoint table cell using VBA before. That part isn't so difficult.

[mlexcelhelpforum]

Do you have any idea what this error message represents?

[mlexcelhelpforum]

IntelliSense just helps you figure out what to write after the period, right? I use it a little, but the problem is I often don't know what to write before the period

[mlexcelhelpforum]

I just used IntelliSense and the object browser and found that SlideRange cannot follow the period after Slides. I'll try to continue using IntelliSense and the object browser and play around a little until I find the code that works.

[snb]

I fear you didn't use my improved code in post #2, because that doesn't contain sliderange.
Please have a look again.
I hope you realise it's rather complicated to advise without seeing a sample powerpoint. For instance: I can't know what the table looks.like.

[mlexcelhelpforum]

Dear snb,

I have tried the following codes:

1.

Please Login or Register  to view this content.

2.

Please Login or Register  to view this content.

I added code to open PowerPoint for the second snippet of code you gave.

The first code gets the error message:
Run-time error '438':
Object doesn't support this property or method.

The second code won't run at all.

Unfortunately I'm unable to upload the PowerPoint files as the uploading tool states: Invalid file.

So I uploaded them here instead: https://sites.google.com/site/herearethefilessnb/home

Thanks a bunch for your help,
ML

[snb]

If you test the fist code step by step (F8 ), you can report the value of variable j and the value of variable jj. and the value of sn(j,jj)

If you wnat to see what happens

Please Login or Register  to view this content.

[mlexcelhelpforum]

I just tried to use F8 and go down through this

Please Login or Register  to view this content.

Now I get:
Run-time error '438':
Object doesn't support this property or method.

At line:
Application.Visible = True

[snb]

Add a dot before application.

You can zip any file you want and post as an attachment here. Please use ppt instead of pptx.

[mlexcelhelpforum]

With or without the dot I get the same error.

[snb]

You can test the code without this line.

[mlexcelhelpforum]

Actually, now it looks like I'm getting the error at this line now:

With GetObject("C:\Users\ML\Desktop\Presentation1.pptx").Presentations(1)

[mlexcelhelpforum]

I've zipped Office 2003 versions of the files.

MLs problem.zip

[snb]

I adapted this code.

[mlexcelhelpforum]

Hi snb,

I tried that.

At this line:
.Slides(j).Shapes(1).Table.Cell(jj \ 2 + 1, jj Mod 2 + 1).Shape.TextFrame.TextRange.Text = sn(j, jj)

I get:
Run-time error '9'
Subscript out of range

ML

[mlexcelhelpforum]

I think this code is starting to get quite advanced for me, and I still wanted to ask how then to do it in Steps of greater than two, such as Steps of 3, Steps of 5, etc. So that I would be copying every 3rd row, or every 5th row, etc.

Is it not possible to make some kind of minor adjustment to my original code?

[snb]

As I told you before: be specific:

what value has variable j and what value has variable jj, and what value has sn(j,jj) ?

Debugging is just what it says: enter into the details.

[mlexcelhelpforum]

I'm afraid I don't understand what I'm supposed to do. I think I'm a relative beginner at this. Where do I enter values for j and jj?

Is debugging just walking through each line with F8? I am doing that and telling you where the errors pop up, but I don't know what to do about the errors myself.

Also, because of the problem we talked about yesterday, when I use F8 to walk through the macro it gets hung up and freezes Excel and PowerPoint on this line:
With GetObject("C:\Users\ML\Desktop\Presentation1.pptx") if I haven't already opened another PowerPoint 2010 file.

So I think I have to somehow insert the code you gave me yesterday:

Please Login or Register  to view this content.

into this new code. It was the only code that was successful in opening PowerPoint 2010 instead of 2003.

[snb]

If you use F8 you can hang with the mouse above the variables in the code and see which values they have.

You introduced the complication yourself by using a 2*2 table.
If you had used a 1*4 table the code would have looked simpler

Please Login or Register  to view this content.

As soon as you grasp the code, it's not difficult to see what it does. But that takes time (for everyone).

[mlexcelhelpforum]

Uh-oh!

I'm really sorry! I'm afraid with me being a newbie and all of the suspense in our correspondence I got confused and forgot what I was originally after. I also tried to start off simple so as not to bombard you with the whole scope of what I'm trying to do.

The files I uploaded before are in the case that every row is sent to PowerPoint. This I already have a macro for. The question is how to write a macro to send every second row, or a macro to send every 3rd row, etc.

So the key factors are:
- I want to be able to easily change the interval of selected rows: single rows, every 2nd row, every 3rd row, etc. depending on what rows I want to populate the slides with.
- I will often change the layout of the PowerPoint tables and change which Excel columns are assigned to which cell on the table of each slide.
- I want to deal with different data ranges, sometimes 1 column, sometimes 6 columns, it depends.

Attached are files showing what I'd like to happen if I decide to send every 2nd Excel row and choose to use a 2x2 table in PowerPoint. However, as I just said above, I'd also like to be able to use other table dimensions, and I want to be able to change which Excel cell is assigned to which PowerPoint cell.

The code I originally wrote is pretty easy for me to understand and adapt, if only two simultaneous loops were possible. I'm not sure if this new code is suitable?

[mlexcelhelpforum]

I just tested this code.

Please Login or Register  to view this content.

It stopped on the first try and when I moused over, j = 1, and jj = 0.

[mlexcelhelpforum]

Ok, I think I'm starting to get it.

For a macro to import cell data from every 2nd row I've gotten this far:

Please Login or Register  to view this content.

I should tell you the reason why this works is because whatever factor I choose to divide the rows by, in this case 2, will be chosen to divide evenly.

[snb]

You'd better build up form simple to complex. Only take the folowing step after successfully ending the previous one.
In your original 2*2 table Use sn(j,jj+1)

Please Login or Register  to view this content.

[mlexcelhelpforum]

Hello again,

At the moment nothing is working.

I think it's best to ignore all of the details I've asked for and imagine a situation that deals with the core of the problem.

So let's imagine I just have one column of data, and I just want to go down through that one column of data in steps of two and insert the data from each of those second rows into a new slide in PowerPoint. How would I do that?

And if that's possible, how would I then do the same thing for every 3rd row or every 4th row of that one column?

I think if I could figure out how to do this then I would be able to solve the more complex task I originally aimed to do. This seems to be the missing piece.

Thanks so much for all of your help,
ML

[snb]

This works flawlessly on my system.

[mlexcelhelpforum]

Wahoo!

It worked here too. I just had to change the file address and add the PowerPoint object library!
Good job :-)

And thank you, thank you, thank you...

Nice new languages too.

[mlexcelhelpforum]

I'm impressed!

How did you figure out the code for that?

And how do I figure out the code for how to do the same thing but for every 2nd, 3rd, etc. row of data?

My code is always much longer than yours haha

[snb]

Most of the VBA-solutions can be found between your ears...

I used the same code I posted here in this thread.

The advantage of simple code is, that is can be adapted easily.

If you want to take every third,fourth,fifth, etc. line you can simply add 'step' to the code: for instande for each third line:

Please Login or Register  to view this content.

[mlexcelhelpforum]

Dear snb,

In testing the code I've found that changing the interval is not so simple. The code for intervals of three did not work on my end.

The problem lies here

Please Login or Register  to view this content.

For example, if the worksheet has cells A1 through D9 filled, then the slide number values of j are: 1, 4, and 7.

However in this part of the

Please Login or Register  to view this content.

we only created 3 new slides, one for every 3rd row. And PowerPoint goes through trying to populate every 3rd slide with every 3rd row of Excel. But what was originally aimed for was to populate every individual slide with every 3rd row of Excel.

Therefore when running the second part of the code above, we get:

Run-time error '-2147188160 (80048240)'
Slides (unknown member): Integer out of range. 7 is not in the valid range of 1 to 4.

Because there is no 7th slide within the four slides we are working with.

So we're kind of back to the original question, which is how to populate individual slides with different intervals of data from Excel. I think it must simply not be possible to run two for next loops of varying intervals-- for slides = 1 to slides.count step 1, and for For j = 1 To UBound(sn) Step 3 -- simultaneously.

[snb]

So you need a little bit of secondary school arithmetic to adapt the series 1,4,7,etc into 1,2,3, etc

what about j\3+1 ?

[mlexcelhelpforum]

Great!!!!

That worked. Thank you very much :-D You just saved me and my whole original idea for the project I'm working on.

Unfortunately, I must have forgotten my secondary school arithmetic because I have no idea how you figured out that:

1, 4, 7, 10, 13, etc.

/3 + 1 =

1, 2, 3, 4, 5, etc.

(There must be some rounding involved somewhere in there too?)

I had been trying to think of how to find a relationship between these ranges yesterday but couldn't figure it out. What will I do for the other increments? Sorry to be dumb, but would you mind telling me how you figured out that math so I can try to figure out the other increments?

I think it's your math skills that make your codes so short and crisp

[mlexcelhelpforum]

Hello there,

So I just realized that it's the same idea for other intervals:

1, 5, 9, 13, 17

/4 +1 =

1, 2, 3, 4, 5

and

1, 10, 20, 30, 40

/9 +1=

1, 2, 3, 4, 5

Etc.

Cool! But how did you figure that out?

Excuse me again for my ignorance on this.

ML

[snb]

Dive into VBEditor's help files and explore the difference between operator \ and operator /.
In this case you should only use \ .

[mlexcelhelpforum]

Done!

/ The division operator

\ The integer division operator - this operator divides two numbers and returns the integer result (eg. 7\4 gives a result of 1)