Excel to PPT Export VBA Error

[Boniouk]

I have the following code which takes data from a excelpth Cell reference and exports to a pptpath cell reference, but im getting a 1004 Application defined or object defined error but can't see for the life of me what i haven't defined. Also, that error only happens if ran within VBA, if from a macro it returns a 400 error only. Any help gurus?

Option Explicit

Sub ExporttoPPT()

Dim ppt_app As New PowerPoint.Application
Dim pre As PowerPoint.Presentation
Dim slde As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim wb As Workbook
Dim rng As Range

Dim vSheet$
Dim vRange$
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vslide_No As Long
Dim expRng As Range

Dim adminSh As Worksheet
Dim configRng As Range
Dim xlfile$
Dim pptfile$

Application.DisplayAlerts = False

Set adminSh = ThisWorkbook.Sheets("Admin")
Set configRng = adminSh.Range("Rng_sheets")

xlfile = adminSh.[excelpth]
pptfile = adminSh.[pptPth]

Set wb = Workbooks.Open(xlfile)
Set pre = ppt_app.Presentations.Open(pptfile)

For Each rng In configRng

'---- Set Variables

With adminSh
vSheet$ = .Cells(rng.Row, 4).Value
vRange$ = .Cells(rng.Row, 5).Value
vWidth = .Cells(rng.Row, 6).Value
vHeight = .Cells(rng.Row, 7).Value
vTop = .Cells(rng.Row, 8).Value
vLeft = .Cells(rng.Row, 9).Value
vslide_No = .Cells(rng.Row, 10).Value
End With

'---- Export to PPT

wb.Activate
Sheets(vSheet$).Activate
Set expRng = Sheets(vSheet$).Range(vRange$)
expRng.Copy

Set slde = pre.slides(vslide_No)
slde.Shapes.PasteSpecial ppPasteBitmap
Set shp = slde.Shapes(1)

With shp
.Top = vTop
.Left = vLeft
.Width = vWidth
.Height = vHeight

End With

Set shp = Nothing
Set slde = Nothing
Set expRng = Nothing


Application.CutCopyMode = False


Next rng

'pre.Save
'pre/Close

Set pre = Nothing
Set ppt_app = Nothing
wb.Close False
Set wb = Nothing

Application.DisplayAlerts = True

End Sub

 

[rlv01]

a. Which line of code generates the error?
b. You should try to use code tags when posting code, as it makes it easier to read.

 

[Boniouk]

Think it's a named range i got wrong, trying it again now. How do i remove posts if i find a solution before any responses? I can't see where i can take my own posts down, and don't want clog stuff up.

 

[rlv01]

I think you can just mark it 'solved'

 

[Mehe]

I have the following code which takes data from a excelpth Cell reference and exports to a pptpath cell reference, but im getting a 1004 Application defined or object defined error but can't see for the life of me what i haven't defined. Also, that error only happens if ran within VBA, if from a macro it returns a 400 error only. Any help gurus?

Option Explicit

Sub ExporttoPPT()

Dim ppt_app As New PowerPoint.Application
Dim pre As PowerPoint.Presentation
Dim slde As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim wb As Workbook
Dim rng As Range

Dim vSheet$
Dim vRange$
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vslide_No As Long
Dim expRng As Range

Dim adminSh As Worksheet
Dim configRng As Range
Dim xlfile$
Dim pptfile$

Application.DisplayAlerts = False

Set adminSh = ThisWorkbook.Sheets("Admin")
Set configRng = adminSh.Range("Rng_sheets")

xlfile = adminSh.[excelpth]
pptfile = adminSh.[pptPth]

Set wb = Workbooks.Open(xlfile)
Set pre = ppt_app.Presentations.Open(pptfile)

For Each rng In configRng

'---- Set Variables

With adminSh
vSheet$ = .Cells(rng.Row, 4).Value
vRange$ = .Cells(rng.Row, 5).Value
vWidth = .Cells(rng.Row, 6).Value
vHeight = .Cells(rng.Row, 7).Value
vTop = .Cells(rng.Row, 8).Value
vLeft = .Cells(rng.Row, 9).Value
vslide_No = .Cells(rng.Row, 10).Value
End With

'---- Export to PPT

wb.Activate
Sheets(vSheet$).Activate
Set expRng = Sheets(vSheet$).Range(vRange$)
expRng.Copy

Set slde = pre.slides(vslide_No)
slde.Shapes.PasteSpecial ppPasteBitmap
Set shp = slde.Shapes(1)

With shp
.Top = vTop
.Left = vLeft
.Width = vWidth
.Height = vHeight

End With

Set shp = Nothing
Set slde = Nothing
Set expRng = Nothing


Application.CutCopyMode = False


Next rng

'pre.Save
'pre/Close

Set pre = Nothing
Set ppt_app = Nothing
wb.Close False
Set wb = Nothing

Application.DisplayAlerts = True

End Sub

Hi, is the issue resolved? I was doing the same code( except to opt automation) and unable to resolve the issue. Can you please guide me if you have solved it.

 

[Mehe]

a. Which line of code generates the error?
b. You should try to use code tags when posting code, as it makes it easier to read.

Option Explicit

Sub ExporttoPPT()

Dim ppt_app As New PowerPoint.Application
Dim pre As PowerPoint.Presentation
Dim slde As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim wb As Workbook
Dim rng As Range

Dim vSheet$
Dim vRange$
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vslide_No As Long
Dim expRng As Range

Dim adminSh As Worksheet
Dim configRng As Range
Dim xlfile$
Dim pptfile$

Application.DisplayAlerts = False

Set adminSh = ThisWorkbook.Sheets("Admin")
Set configRng = adminSh.Range("Rng_sheets")

xlfile = adminSh.[excelpth]
pptfile = adminSh.[pptPth]

Set wb = Workbooks.Open(xlfile)
Set pre = ppt_app.Presentations.Open(pptfile)

For Each rng In configRng

'---- Set Variables

With adminSh
vSheet$ = .Cells(rng.Row, 4).Value
vRange$ = .Cells(rng.Row, 5).Value
vWidth = .Cells(rng.Row, 6).Value
vHeight = .Cells(rng.Row, 7).Value
vTop = .Cells(rng.Row, 8).Value
vLeft = .Cells(rng.Row, 9).Value
vslide_No = .Cells(rng.Row, 10).Value
End With

'---- Export to PPT

wb.Activate
Sheets(vSheet$).Activate
Set expRng = Sheets(vSheet$).Range(vRange$) '____ error in above line, 1004, object defined

expRng.Copy

Set slde = pre.slides(vslide_No)
slde.Shapes.PasteSpecial ppPasteBitmap
Set shp = slde.Shapes(1)

With shp
.Top = vTop
.Left = vLeft
.Width = vWidth
.Height = vHeight

End With

Set shp = Nothing
Set slde = Nothing
Set expRng = Nothing


Application.CutCopyMode = False


Next rng

'pre.Save
'pre/Close

Set pre = Nothing
Set ppt_app = Nothing
wb.Close False
Set wb = Nothing

Application.DisplayAlerts = True

End Sub

 

[Mehe]

a. Which line of code generates the error?
b. You should try to use code tags when posting code, as it makes it easier to read.

Can you please help me, I have the same error. I'm trying to automate excel to ppt using vba.

 

[rlv01]

This thread has already been marked solved. You should open a new thread for your problem.