UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> From Access To Powerpoint Excel File, Office 2013    
 
   
wornout
post Apr 24 2017, 12:07 AM
Post#1



Posts: 884
Joined: 17-November 13
From: Orewa New Zealand


I am trying to copy a demo powerpoint file to my powerpoint
I have a slide that has a button on it that when clicked runs the below code but it debugs and says
unrecognized database format
CODE
Private Sub OpenDBAndRecordset(Slide As PowerPoint.Slide)

    Dim strTable   As String
     'Open DAO database connection
    Set db = OpenDatabase(Slide.Tags("DBPath"))        it debugs here
    
    'Get Table Name
    strTable = Slide.Tags("Table")
    
    'Open Table recordset
    Set rs = db.OpenRecordset("Select * from " & strTable)
    
End Sub



The file path is
CODE
Public Sub Assign_Tag(Slide As PowerPoint.Slide)
    Dim i As Long
    
    'first delete any existing tags on Slide
    For i = 1 To Slide.Tags.Count
        Slide.Tags.Delete (i)
    Next i
    Slide.Tags.Add "DBPath", "C:\Users\Michelle\Desktop\Graemes Data base\help_database.accdb"  'database path
    Slide.Tags.Add "Table", "ServeyQuestions"  'table name
End Sub


I am using office 365
Go to the top of the page
 
LPurvis
post Apr 24 2017, 03:37 AM
Post#2


UtterAccess Editor
Posts: 15,976
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

What value is held by:
Slide.Tags("DBPath"))        
It's definitely pointing to: help_database.accdb?

And what reference have you added to the project for DAO?

Cheers

--------------------
Go to the top of the page
 
JonSmith
post Apr 24 2017, 05:40 AM
Post#3



Posts: 3,075
Joined: 19-October 10



You also have no variables declared here. db and rs should be Dim'd somewhere, are they declared outside of the procedure?

JS
This post has been edited by JonSmith: Apr 24 2017, 05:40 AM
Go to the top of the page
 
wornout
post Apr 24 2017, 07:46 PM
Post#4



Posts: 884
Joined: 17-November 13
From: Orewa New Zealand


Ok here is all the code. it works on the trial but file ext is mdb.
CODE
Option Explicit

Private db As DAO.Database
Private rs As DAO.Recordset

'-------------------
'Adds two tags to Slide so we can refresh data for the excel object and chart object
'First Tag is the path to database
'Second Tag is the Access table with data
'-------------------
Public Sub Assign_Tag(Slide As PowerPoint.Slide)
    Dim i As Long
    
    'first delete any existing tags on Slide
    For i = 1 To Slide.Tags.Count
        Slide.Tags.Delete (i)
    Next i
    Slide.Tags.Add "DBPath", "C:\Users\Michelle\Desktop\Graemes Data base\help_database.accdb"  'database path
    Slide.Tags.Add "Table", "ServeyQuestions"  'table name
End Sub

'-------------------------------------------------------
'Refreshes OLE Objects on Slide - looks for Excel worsheet or Excel Chart or MSGraph Object
'-------------------------------------------------------
Public Sub RefreshOLEObject(Slide As PowerPoint.Slide)

Dim shap                As Shape
    
     'Open database and recordset
     Call OpenDBAndRecordset(Slide)
    
     'Loop through each shapes in the slide
     For Each shap In Slide.Shapes
    
         'Check for embedded OLE object
         If shap.Type = msoEmbeddedOLEObject Then
            If VBA.InStr(1, shap.OLEFormat.ProgID, "MSGraph.Chart", vbTextCompare) > 0 Then
                'Refresh MsGraph object
                Call RefreshMSGraphObject(shap)
            ElseIf VBA.InStr(1, shap.OLEFormat.ProgID, "Excel.Chart", vbTextCompare) Then
                'Refresh Excel Chart object
                Call RefreshChartObject(shap)
            ElseIf VBA.InStr(1, shap.OLEFormat.ProgID, "Excel.Sheet", vbTextCompare) Then
                'Refresh Excel worksheet object
                Call RefreshExcelSheetObject(shap)
            End If
        End If
    Next
    
    'Close Database and Recordset
    Call CloseDBAndRecordset
    
End Sub

'--------------------------------------------
'Refresh Excel worksheet Object from database
'--------------------------------------------
Private Sub RefreshExcelSheetObject(sh As PowerPoint.Shape)
    
    Dim wksh            As Excel.Worksheet
    
   'Get Excel worksheet object
    Set wksh = sh.OLEFormat.Object.Worksheets(1)

    'Clear contents of used range
    wksh.UsedRange.ClearContents
    Call AssignNewData(wksh)

    'Releasing the objects
    Set wksh = Nothing
    
  
End Sub

'----------------------------------------
'Refresh Excel Chart Object from database
'----------------------------------------
Private Sub RefreshChartObject(sh As PowerPoint.Shape)
    Dim wksh            As Excel.Worksheet
    Dim wkCht           As Excel.Chart

    
    'Get Datasheet of Excel chart object
    Set wksh = sh.OLEFormat.Object.Worksheets(1)
    'Get Chartsheet of Excel chart object
    Set wkCht = sh.OLEFormat.Object.Charts(1)
    
    'Clear contents of used range
    wksh.UsedRange.ClearContents
    
    Call AssignNewData(wksh)
    
    'Assign Chart source data
    wkCht.SetSourceData wksh.UsedRange, xlColumns
        
    'Releasing the objects
    Set wksh = Nothing
    Set wkCht = Nothing
  
    
End Sub
'------------------------------------
'Refresh MSGraph Object from database
'------------------------------------
Private Sub RefreshMSGraphObject(sh As PowerPoint.Shape)

    Dim r               As Long
    Dim c               As Long
    
    Dim oGraph          As Graph.Chart
    Dim oData           As Graph.DataSheet
    
    
    'Get graph object
    Set oGraph = sh.OLEFormat.Object
    'Get Datasheet of graph object
    Set oData = oGraph.Application.DataSheet
    
    r = 0
    Do
        r = r + 1
    Loop Until oData.Rows(r).Include = False
    c = 0
    Do
        c = c + 1
    Loop Until oData.Columns(c).Include = False
    'clear contents of graph datasheet range
    oData.Range(oData.Cells(1, 1), oData.Cells(r + 1, c + 1)).ClearContents
    
    Call AssignNewData(oData)
    
    
End Sub

'---------------------------------
'Open Database and Recordset object
'---------------------------------
Private Sub OpenDBAndRecordset(Slide As PowerPoint.Slide)

    Dim strTable   As String
     'Open DAO database connection
    Set db = OpenDatabase(Slide.Tags("DBPath"))
    
    'Get Table Name
    strTable = Slide.Tags("Table")
    
    'Open Table recordset
    Set rs = db.OpenRecordset("Select * from " & strTable)
    
End Sub

'---------------------------------
'Close Database and Recordset object
'---------------------------------
Private Sub CloseDBAndRecordset()

    'Close Recordset
    rs.Close
    'Close database
    db.Close
    
    'Releasing the objects
    Set db = Nothing
    Set rs = Nothing
    
End Sub

'---------------------------------------
'Assign new data from DB into Data sheet
'---------------------------------------
Private Sub AssignNewData(DataSheet As Object)
    Dim i               As Long
    Dim j               As Long
    Dim k               As Long
        
     'Put new data into the excel worksheet or excel chart datasheet, or MSGraph Datasheet
    With DataSheet
        'put all field names in first row
        For i = 0 To rs.Fields.Count - 1
          
            .Cells(1, i + 1) = rs.Fields(i).Name
        Next i
        
        'put new data into the Data sheet
        '--------------------------------------'
        i = 1
        rs.MoveFirst
        Do While Not rs.EOF
            j = 0
            For k = 0 To rs.Fields.Count - 1
                .Cells(i + 1, j + 1).Value = rs.Fields(k).Value
                j = j + 1
            Next k
            i = i + 1
            rs.MoveNext
        Loop
        '----------------------------------------'
    End With
        
End Sub

These are the references I have ticked
Visual Basic For Applications
Microsoft PowerPoint 16.0 Object Library OLE Automation
Microsoft Office 16.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Access 16.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Excel 16.0 Object Library
Microsoft Graph 16.0 Object Library

Go to the top of the page
 
HiTechCoach
post Apr 24 2017, 09:42 PM
Post#5


UtterAccess VIP
Posts: 18,987
Joined: 29-September 03
From: Oklahoma City, Oklahoma


To read the accdb you will need to update the DAO library to the new ACE engine

Steps:

1) remove (uncheck) : Microsoft DAO 3.6 Object Library

2) Add (check): Microsoft Office 16.0 Access database engine object

--------------------
Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
 
wornout
post Apr 26 2017, 01:35 PM
Post#6



Posts: 884
Joined: 17-November 13
From: Orewa New Zealand


That worked a treat I now have it working Thank you

This post has been edited by wornout: Apr 26 2017, 01:46 PM
Go to the top of the page
 
HiTechCoach
post Apr 28 2017, 02:28 PM
Post#7


UtterAccess VIP
Posts: 18,987
Joined: 29-September 03
From: Oklahoma City, Oklahoma


You're welcome. Glad we could assist.



--------------------
Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2017 - 05:58 PM