VBA Check to see if file is open before reopening a second file

0 votes

This code needs to be executed more than once, but each time it does so, I want it to first check to see if the Powerpoint file is already open before opening a new copy.

I anticipate it to check to see if the PowerPoint file is open in order to prevent the file from being opened twice.

used to convert Excel to PowerPoint before

    \`
    'app
    '  pre
    '   slide
    '    shapes
    '     text frame
    '      text

    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 cofigRng As Range
    Dim xlfile$
    Dim pptfile$

    Application.DisplayAlerts = False

    Set adminSh = ThisWorkbook.Sheets("Admin")
    Set cofigRng = adminSh.Range("Rng_sheets2")

    xlfile = adminSh.\[excelpath2\]
    pptfile = adminSh.\[pptPth\]

    Application.DisplayAlerts = False
    Set wb = Workbooks.Open(xlfile, False, True)

    Application.DisplayAlerts = False

    Set pre = ppt_app.Presentations.Open(pptfile)

    For Each rng In cofigRng

    '----------------- 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

    '            Stop

                pre.Application.Activate
                Set slde = pre.Slides(vSlide_No)

                'Application.ActiveWindow.Panes(vSlide_No).Activate
                slde.Select
                slde.Shapes.PasteSpecial ppPasteOLEObject 'ppPasteSecial' ppPasteOLEobject
                Set shp = slde.Shapes(slde.Shapes.Count)
               

                With shp

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

                End With
               

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

    Application.CutCopyMode = False
    Set expRng = Nothing
    Next rng

    'pre.Save
    'pre.Close

    Set pre = Nothing
    Set ppt_app = Nothing

    wb.Close False
    Set wb = Nothing

    Application.DisplayAlerts = True

    End Sub\`
Apr 4, 2023 in Others by narikkadan
• 63,720 points
806 views

1 answer to this question.

0 votes

You could replace the line:

Set pre = ppt_app.Presentations.Open(pptfile) 

With

if not pre is nothing then
      Set pre = ppt_app.Presentations.Open(pptfile)
end if 

The code checks if the pre object is set and if not, sets it by opening your pptfile

answered Apr 4, 2023 by Kithuzzz
• 38,020 points

Related Questions In Others

0 votes
1 answer

How to check if a cell is empty in a range variable?

Use WorksheetFunction.CountA() (https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.counta) function If WorksheetFunction.CountA(rng) = 0 Then ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,720 points
770 views
0 votes
1 answer

How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

answered Nov 5, 2018 in Others by DataKing99
• 8,250 points
5,773 views
0 votes
1 answer
0 votes
0 answers

SEO - How to programmatically check if a website has been Banned by Google

i wanted to link a website to ...READ MORE

Feb 14, 2022 in Others by Kichu
• 19,050 points
357 views
0 votes
1 answer

How to modify Powerpoint Chart ChartData by Excel VBA

Example: Code: Set pptApp = GetObject(, "PowerPoint.Application") Set pptPres = ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,720 points
2,932 views
0 votes
1 answer

Using excel I need to open PPT and create ".gif" image of a ."pdf" and save it

It appears happier if you get a ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,720 points
437 views
0 votes
1 answer

Changing code to adjust number of charts on each powerpoint slide VBA

Try this: Option Explicit Sub CopyChartsToPowerPoint() ...READ MORE

answered Jan 23, 2023 in Others by narikkadan
• 63,720 points
471 views
0 votes
1 answer

TextEffect to TextFrame Adjustment (Mismatch)

Delete the original shape and then replace, ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,720 points
549 views
0 votes
1 answer

Trying to import a CSV file and convert it into a table using VBA

You cannot convert a range that contains ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,020 points
1,913 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP