Combining VBA code

[MarkNie]

Hi All

I was wondering if anybody can shed some light or point me in the right direction.

I am busy writing some code to pull data from excel into powerpoint textboxes. I have managed to do this without any issues.

The problem I am having now is that I have to write a bit of code for each text box which is going to take forever as there will be over 150 of them.

I there anyway to combine the code maybe in a loop (which I don't understand to well) or just a simpler way of doing it.

Code:
Dim strReplaceText As String

strReplaceText1 = Range("C2")
strReplaceText2 = Range("D2")
strReplaceText3 = Range("G2")

ActivePresentation.Slides(2).Shapes("Textbox1").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText1

ActivePresentation.Slides(2).Shapes("Textbox2").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText2

ActivePresentation.Slides(2).Shapes("Textbox3").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText3

ActivePresentation.Slides(3).Select


strReplaceText4 = Range("C3")
strReplaceText5 = Range("D3")
strReplaceText6 = Range("G3")

ActivePresentation.Slides(3).Shapes("Textbox4").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText4

ActivePresentation.Slides(3).Shapes("Textbox5").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText5

ActivePresentation.Slides(3).Shapes("Textbox6").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText6


strReplaceText7 = Range("C4")
strReplaceText8 = Range("D4")
strReplaceText9 = Range("G4")

ActivePresentation.Slides(3).Shapes("Textbox7").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText7

ActivePresentation.Slides(3).Shapes("Textbox8").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText8

ActivePresentation.Slides(3).Shapes("Textbox9").Select
ActiveWindow.Selection.TextRange.Text = strReplaceText9

Is there a way that you could just run through all the rows in excel an populate the textboxes in ppt without having to write each line for a row and textbox.

I hope this makes sense, please let me know if you need a better explenation.

Thanks

 

[Gruuuu]

Is there a way that you could just run through all the rows in excel an populate the textboxes in ppt without having to write each line for a row and textbox.

Yes.

Here's a basic breakdown of a for loop, to help you get started.

For [red]i[/red] = [blue]2[/blue] To [purple]150[/purple]
    [green]'Operation[/green]
Next [red]i[/red]

[red]i[/red] This is your COUNTER. It is increased by Next [red]i[/red]
[blue]2[/blue] This is your INITIAL VALUE. [red]i[/red] will start as value [blue]2[/blue] (it will also be an integer, but that is because of a non-explicit declaration. We'll get into that in a moment.)
[purple]150[/purple] This is your EXIT CRITERIA. The loop will run [purple]150[/purple] - [blue]2[/blue] +1 times.

Another thing to consider is that when you are referencing objects by name, such as ActivePresentation.Slides(3).Shapes("Textbox7")
The name "Textbox7" is a string, which you can build up by stringing variables together, such as "Textbox" & i (Integers are converted to strings when concatenated).

And a side note, on the explicit thing.
It's a REALLY good idea to have Option Explicit in your modules.
Looks like this:

[u][blue]Option Explicit[/blue]                          [/u]
[blue]Sub[/blue] MySub()
...

This will force you to declare variables before using them. It saves you a lot of headache in the end, and makes you a better coder.

I mention this because, interestingly enough, you declare a variable, strReplaceText, but then never use it, you use strReplaceText1, strReplaceText2, etc etc. You're basically make a new variable for every cell you reference. Option Explicit would have stopped you.

Yet another thing to consider is that Range("C2").Value can be assigned as a string, so you didn't need the variable at all.

Try that out and let us know how it comes along.

 

[MarkNie]

Hi Gruuuu

Thank you very much, I will give this a go and let you know how I get on.

Regards
Mark

 

[SkipVought]

Are you running this code in Excel or in PPT?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

hi,

something like this...

    Dim lRow As Long, i As Integer, iSlide As Integer
    
    With ActiveSheet
        For lRow = 2 To .[C2].End(xlDown).Row
            For i = 1 To 3
                Select Case i
                Case 1
                    ActivePresentation.Slides(iSlide).Shapes("Textbox" & i).textrange.Text = _
                        .Cells(lRow, "C").Value
                Case 2
                    ActivePresentation.Slides(iSlide).Shapes("Textbox" & i).textrange.Text = _
                        .Cells(lRow, "D").Value
                Case 3
                    ActivePresentation.Slides(iSlide).Shapes("Textbox" & i).textrange.Text = _
                        .Cells(lRow, "G").Value
                End Select
            Next
            iSlide = iSlide + 1
        Next
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, I had my iSlide incrnment in the wrong place...

    Dim lRow As Long, i As Integer, iSlide As Integer, sValue As String
    
    With ActiveSheet
        For lRow = 2 To .[C2].End(xlDown).Row
            iSlide = iSlide + 1
            For i = 1 To 3
                Select Case i
                Case 1
                    sValue = .Cells(lRow, "C").Value
                Case 2
                    sValue = .Cells(lRow, "D").Value
                Case 3
                    sValue = .Cells(lRow, "G").Value
                End Select
                ActivePresentation.Slides(iSlide).Shapes("Textbox" & i).textrange.Text = sValue
            Next
       Next
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkNie]

Hi SkipVought

Thank you for the code. I have added to my VBA but keep getting a compile error - Method or data member not found for the TextRange.Text = sValue.

I have tried changing the code a bit which works but it only uses the first 3 testboxes in the presentation so the last persons details are in the first 3 and it doesn't seem to run through all the textboxes in the presentation.

my code which I changed a bit as above:

Dim lRow As Long, i As Integer, iSlide As Integer, sValue As String

With ActiveSheet
For lRow = 2 To .[C2].End(xlDown).Row
iSlide = 1
iSlide = iSlide + 1
For i = 1 To 3
Select Case i
Case 1
sValue = .Cells(lRow, "C").Value
Case 2
sValue = .Cells(lRow, "D").Value
Case 3
sValue = .Cells(lRow, "G").Value
End Select
ActivePresentation.Slides(iSlide).Shapes("Textbox" & i).Select
ActiveWindow.Selection.TextRange.Text = sValue
Next
Next
End With


Thanks again for all your help, almost there

Mark

 

[MarkNie]

Oh I am running this code from PowerPoint.

 

[Gruuuu]

It sounds like perhaps your textbox names are skipping some numbers, perhaps? Example: TextBox1, TextBox2, TextBox3, TextBox154, ...

Question: For each slide, is there more than one shape on the slide, or is it just the one? And if it's one, would there ever be more than one?

 

[MarkNie]

Hi Gruuu

There will be between 6 and 9 on each slide. But never more than 9.

This is basically for people's biographies. So each one will have a Name, Title and Biog textbox and there will only ever be 3 people on each slide.

 

[MarkNie]

Hi Gruuu

I have also checked all the textboxes and they are all named correctly. For testing purposes I am only useing 21 boxes which are all named correctly eg Textbox1, Textbox2 etc upto Textbox21.

Just seems to be only targeting the first 3 textboxes. Would it have something to do with the "For i = 1 To 3" bit of the code?

 

[SkipVought]

When you post your questions and examples, you must be as complete as possible. For instance you do NOT have 3 tb's per slide. That makes a big difference!

    Dim lRow As Long, i As Integer, iSlide As Integer, sValue As String
    
    With ActiveSheet
        For lRow = 2 To .[C2].End(xlDown).Row
            iSlide = 1
            iSlide = iSlide + 1 '[b]
            i = 1
            Do While i <= ActivePresentation.Slides(iSlide).Shapes.Count
                If Left(ActivePresentation.Slides(iSlide).Shapes(i).Name, 7) = "Textbox" Then '[/b]
                    Select Case i
                    Case 1
                        sValue = .Cells(lRow, "C").Value
                    Case 2
                        sValue = .Cells(lRow, "D").Value
                    Case 3
                        sValue = .Cells(lRow, "G").Value
                    End Select
                   ActivePresentation.Slides(iSlide).Shapes([b]i[/b]).Select
                    ActiveWindow.Selection.TextRange.Text = sValue '[b]
                End If
                
                i = i + 1
            Loop    '[/b]
       Next
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkNie]

Hi SkipVought

Apologies for not being more complete with my questions.

I have tried the above code, what it seems to be doing now is just taking the last row from xls and filling in the 6 textboxes on the first slide but does not move to the next slides.

I am not sure how better to explain what I am trying, seems to be getting there and I bet I am being stupid about this too.

Let me try again (Apologies in advance if I am duplicating):

I am trying to update textboxes in the presentation from an excel spreadsheet. I want the VBA to run through the presentation and fill in the textboxes from the cells in excel.

Slide 1

Textbox1 = C2
Textbox2 = D2
Textbox3 = G2

Textbox4 = C3
Textbox5 = D3
Textbox6 = G3

Slide 2

Textbox7 = C2
Textbox8 = D2
Textbox9 = G2

Textbox10 = C4
Textbox11 = D4
Textbox12 = G4

Textbox13 = C5
Textbox14 = D5
Textbox15 = G5

Slide 3... this will carry on for as many rows as we have.


The first bit of code that I posted works fine, but I don't want to write 20 pages of code.

I hope this helps a bit more.

Thanks again for all your help, really appreciated!!

Mark

 

[SkipVought]

well YOU took the iSlide stuff out of the code that I sent you, when you posted your question. Put it back in.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkNie]

Hi Skip

Did you mean add this back in below Endif?

ActivePresentation.Slides(iSlide).Shapes("Textbox" & i).textrange.Text = sValue

Thanks

 

[SkipVought]

Sorry, you DID have the iSlide logic. MY ERROR

    Dim lRow As Long, i As Integer, iSlide As Integer, sValue As String
    
    With ActiveSheet
        For lRow = 2 To .[C2].End(xlDown).Row
            iSlide = iSlide + 1 '
            i = 1
            Do While i <= ActivePresentation.Slides(iSlide).Shapes.Count
                If Left(ActivePresentation.Slides(iSlide).Shapes(i).Name, 7) = "Textbox" Then '
                    Select Case i
                    Case 1
                        sValue = .Cells(lRow, "C").Value
                    Case 2
                        sValue = .Cells(lRow, "D").Value
                    Case 3
                        sValue = .Cells(lRow, "G").Value
                    End Select
                   ActivePresentation.Slides(iSlide).Shapes(i).Select
                    ActiveWindow.Selection.TextRange.Text = sValue '
                End If
                
                i = i + 1
            Loop    '
       Next
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkNie]

Hi Skip

I am getting the following error:

Run-time error'-2147188160(80048240)':
Shape (unknown member): Invalid request. To select a shape, its view must be active.

Thanks for your help though, I will hunt around to see if I can find anything else to help.

It is also not putting the text in the right textboxes eg:

C2 = Textbox1
D2 = Textbox2
G2 = Textbox3

I am so lost now

Thanks again
Mark

 

[SkipVought]

Please COPY the EXACT statement that your code errors on, and PASTE in your post each time you report an error.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkNie]

Hi Skip

That error is for the following line of code:

ActivePresentation.Slides(iSlide).Shapes(i).Select

Just under the end select.

Thanks

 

[MarkNie]

and this is the complete code I am using:

Sub OpenXlsxloop3()
' This requires that you set a reference to Excel in Tools, References
' You could later change these to As Object to avoid that necessity
Dim XlsxApp As Excel.Application
Dim XlsxWork As Excel.Workbook
Dim XlsxFile As String
Dim sld As Slide
Dim shp As Shape


XlsxFile = "C:\Biographies.xlsx"

' Get a reference to Excel app
Set XlsxApp = New Excel.Application
XlsxApp.Visible = True

' Open our source Excel file, get a reference to it
Set XlsxWork = XlsxApp.Workbooks.Open(XlsxFile)

Dim lRow As Long, i As Integer, iSlide As Integer, sValue As String

With ActiveSheet
For lRow = 2 To .[C2].End(xlDown).Row
iSlide = iSlide + 1
i = 1
Do While i <= ActivePresentation.Slides(iSlide).Shapes.Count
If Left(ActivePresentation.Slides(iSlide).Shapes(i).Name, 7) = "Textbox" Then
Select Case i
Case 1
sValue = .Cells(lRow, "C").Value
Case 2
sValue = .Cells(lRow, "D").Value
Case 3
sValue = .Cells(lRow, "G").Value
End Select
ActivePresentation.Slides(iSlide).Shapes(i).Select
ActiveWindow.Selection.TextRange.Text = sValue '
End If

i = i + 1
Loop
Next
End With



' Cleanup
' Close the presentation
XlsxWork.Close
' Quit PPT
XlsxApp.Quit
' Release variables
'Set XlsxWork = Nothing
'Set XlsxApp = Nothing

End Sub


If that helps any.

Thanks