Macro to Change PowerPivot Data Connection

Macro to Change PowerPivot Data Connection

Source: http://dmoffat.wordpress.com/2013/08/21/how-you-can-change-to-another-connection-type-in-powerpivot/

I have noticed that it has been noted in a couple of places that once you choose to connect to data in a particular type of data source (i.e. an Access ACCDB file or a SQL Server or MySQL database) you are then subsequently unable to change the type of data source for PowerPivot data (say if you migrate your data from Access to SQL Server after building a big PowerPivot solution).  This could have major implications on a complex PowerPivot solution with lots of DAX functionality and Pivot Tables based on the existing tables. This would be a painful process no doubt :-(

I have found, however, that  there IS a way to set up a Connection so the source CAN be changed.

Once again, to do that, you have to create the Connection from inside the Excel UI and then add it to the Data Model rather than from inside PowerPivot’s UI.

The secret is to use the generic OLEDB Connection from the “Other Sources” choice on the Data Tab (rather than the specific Access or SQL Server ones):

Connection Wizard

When you choose this option you can then choose the type of data you wish to connect to from a list of installed drivers on your machine (if for example you want to connect to something like MySQL you can just go to the MySQL site and download and install the necessary driver).

ConnectionType

If you choose, for example, SQL Server you can then step through a unique series of dialogs to find the server, database and table or view you are looking for.  Once selected remember that you have to choose to connect as a SQL statement rather than as a table (critical)

The secret is at this point is to:

1. Select the Command String text

2. Copy it to the clipboard (or you come back afterward and copy it once it is created)

3. Once you have clicked ok through and created your Connection you just copy what’s in the Clipboard to a cell in your spreadsheet .. like this:

Conenction Strings

Notice I did the same thing for a connection to an Access database using the ACE 12.0 provider.  I can have as many of these as I want or need.   I could also convert these strings to formulas (formulae ?) so I can type my variables (server, database, table) into other cells and concatenate them back together to create a functioning Command String (Cool eh?).  This way you can change your variables as easily as typing the path to the new Access database or the name of the new Server they moved your app to and the next time you run the code you’ll see below you’re connected up. Sweet !

Thankfully Excel generates these Connection Strings  for you for free so you don’t need to figure out all the parameters in there.  There are probably some that show up that you really don’t need because they’re just reflecting the default anyway.

But there is one I REALLY like to change – I always replace “Share Deny Write” with “Read” (not that it matters actually but it’s good form and MAY prevent collisions with other users of the source solution or PowerPivot – better safe than sorry).

So now I have a cell named ConnectionSQL and another ConnectionAccess (if you don’t know about or use Names now you’d better do it right now or you’ll be sorry some day – trust me :-) ).

I can then create 2 very simple pieces of code (please forgive the formatting but I want you to be able to copy it):

Sub ConnectSQL()

With ThisWorkbook.Connections(“<<Your Connection Name>>”).OLEDBConnection

.Connection = Range(“ConnectionSQL”).Value

.Refresh

End With

End Sub

Sub ConnectAccess()

With ThisWorkbook.Connections(“<<Your Connection Name>>”).OLEDBConnection

.Connection = Range(“ConnectionAccess”).Value

.Refresh

End With

End Sub

So you can switch connections just by running one or the other Procedure (of course you could make it sexier but this is just to show you the functionality :-) ).

After running one or the other sub check to see the change in the Connection String and also look at the change in the data in PowerPivot (if the tables are in fact different in some way – like more or less records – in the two sources).

Macro to have Word Outlines import levels into the Speaker Notes Section of PowerPoint.

Below is Bill Dillworths Macro to have Word Outlines import levels into the Speaker Notes Section of PowerPoint.

 

Sub OutlineLevel2Notes()

‘Set up the integer variables we’ll be using for the macro
Dim varSlideNum As Integer
Dim varLineNum As Integer
Dim varOutlineLevel As Integer

‘This variable determines the outline level that will be _
moved from the textbox section to the notes section of PowerPoint

varOutlineLevel = 6

‘Begin the assumed prefix
With ActivePresentation

     ‘We will need to cycle thru each slide
     For varSlideNum = 1 To .Slides.Count

     ‘Add to the assumed prefix
     With .Slides(varSlideNum).Shapes.Placeholders(2)

          ‘Check if there is a text frame, if not, then there really _
isn’t any point in looking at this slide any longer

          If .HasTextFrame Then

               ‘Since there is a text frame, add to the assumed prefix
               With .TextFrame.TextRange

                    Now we will need to loop thru the lines of text _
within the placeholder textbox, but we will go backwards

                    For varLineNum = .Lines.Count To 1 Step -1

                              ‘If this line is one that should be in the notes section …
                              If .Lines(varLineNum).IndentLevel > (varOutlineLevel – 2) Then

                                   ‘… then put it there …
                                   ActivePresentation.Slides(varSlideNum) _
                                   .NotesPage.Shapes(2) _
                                   .TextFrame.TextRange.Text = _
                                   .Lines(varLineNum).Text & vbCr & _
                                   ActivePresentation.Slides(varSlideNum) _
                                   .NotesPage.Shapes(2) _
                                   .TextFrame.TextRange.Text

                                   ‘… and get rid of the text in the texbox
                                   .Lines(varLineNum).Text = “”

                                   ‘End of check on outline level
                                   End If

                              ‘Proceed to the next slide, if there is one.
                             Next varLineNum

                         ‘Stop this level of the assumed prefix
                         End With

                    ‘Done checking if there is a textframe
                    End If

               ‘Stop this level of the assumed prefix
               End With

          ‘Proceed to the next slide
          Next varSlideNum

     ‘Terminate the last level of the assumed prefix
     End With

‘Terminate the Macro

End Sub

PowerPoint 2010 VBA to add a video

Click here to copy code
https://training-nyc.com/training_class_blog/index.php/2011/12/powerpoint-2010-vba-add-video-code/

PowerPoint 2010 VBA to add a video (CODE)

' PowerPoint 2010 

' Place this code in a module within a PowerPoint 2010 presentation, 
' and run the following test procedure. Modify the output file name 
' as necessary: 
Sub TestCreateSampleVideo() 
    CreateSampleVideo ActivePresentation, "C:\Temp\Video.wmv" 
End Sub 

Sub CreateSampleVideo(pres As Presentation, fileName As String) 
    ' Presentation.CreateVideo does its work asynchronously. 
    ' You can use the Presentation.CreateVideoStatus property 
    ' to periodically check the status, and react accordingly. 

    ' Besides the file name, the CreateVideo method accepts the following 
    ' parameters: 

    ' UseTimingsAndNarration indicates whether to use the presentation's 
    '   timings and narrations that you have supplied. If false, the 
    '   conversion disregards this information. The default is True. 
    ' DefaultSlideDuration indicates the default timing for each slide, 
    '   if you have haven't specified a timing or if you set 
    '   UseTimingsAndNarration to false. The default value is 5 seconds. 
    ' VertResolution indicates the vertical resolution for your movie. The 
    '   default is 720. Regular options include 720, 480, and 240, although you 
    '   can specify any reasonable value you like (200 would work, for example, 
    '   although it's not a standard vertical resolution.) 
    ' FramesPerSecond indicates the number of frames per second in the 
    '   output video. The default value is 30, and unless you have a reason 
    '   to change this, leave it alone. 
    ' Quality indicates a relative quality of the video, and the default 
    '   value is 85. The larger the number, the larger the output and the longer 
    '   it takes to create the video. Unless you have a reason, leave this 
    '   at the default value. Try setting the value to a low number: You'll see 
    '   a definite degradation in output quality. 

    pres.CreateVideo fileName, DefaultSlideDuration:=1, VertResolution:=480 

    ' Now wait for the conversion to be complete: 
    Do 
        ' Don't tie up the user interface, add DoEvents 
        ' to give the mouse and keyboard time to keep up. 
        DoEvents 
        Select Case pres.CreateVideoStatus 
            Case PpMediaTaskStatus.ppMediaTaskStatusDone 
                MsgBox "Conversion complete!" 
                Exit Do 
            Case PpMediaTaskStatus.ppMediaTaskStatusFailed 
                MsgBox "Conversion failed!" 
                Exit Do 
            Case PpMediaTaskStatus.ppMediaTaskStatusInProgress 
                Debug.Print "Conversion in progress" 
            Case PpMediaTaskStatus.ppMediaTaskStatusNone 
                ' This shouldn't happen--you'll get this value 
                ' when you ask for the status and no conversion 
                ' is happening or has completed. 
            Case PpMediaTaskStatus.ppMediaTaskStatusQueued 
                Debug.Print "Conversion queued" 
        End Select 
    Loop 
End Sub

VBA Macro to Process Google Keywords CSV file into a usable, clickable Excel file

VBA CODE IS FOUND HERE

https://training-nyc.com/training_class_blog/index.php/2011/12/vba-macro-process-google-keywords-csv-file-usable-clickable-excel-file-code/

Google found many keywords in our site from blogs and homework files that were hurting our SEO and making our site less relevant to our specific Keywords such as Excel Training in NYC or VBA Training. By checking where each keyword was getting picked up, we could block that page from being index or delete old files.

This macro separates all the links (up to 7) per line and allows you to click them to check if you should block that page by a robots.txt file.

 

VBA Macro to Process Google Keywords CSV file into a usable, clickable Excel file (CODE)

Sub process_google_keywords_csv_download()

Dim lastrow As Integer
Dim c As Range
‘find last row
Range(“a65000”).End(xlUp).Select
lastrow = ActiveCell.Row

‘insert last row
ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1:$D$” & lastrow), , xlYes).Name = _
“Table1”
Range(“Table1[#All]”).Select
ActiveSheet.ListObjects(“Table1”).TableStyle = “TableStyleLight9”

Range(“Table1[Top URLS]”).Select
Selection.Replace What:=”[“, Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=”]”, Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “WithSpace”
Range(“E2”).Select
ActiveCell.FormulaR1C1 = “=SUBSTITUTE([@[Top URLs]],””:http://””,”” http://””)”

‘select table column
Range(“Table1[WithSpace]”).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns(“E:E”).Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range(“Table1[[#Headers],[WithSpace]]”), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)) _
, TrailingMinusNumbers:=True

Range(“Table1[Top URLS]”).Delete
Range(“Table1[#All]”).ColumnWidth = 25
Range(“Table1[WithSpace]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column1]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column2]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column3]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column4]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column5]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column6]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column7]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column8]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub

PowerPoint 2010 VBA- Add an audio File or Video File

Const videoFileName As String = "C:\Users\Public\Videos\Sample Videos\Bee.wmv"
Const audioFileName As String = "C:\Users\Public\Music\Sample Music\Happy Birthday.mp3"
Sub AddMedia()
    With ActivePresentation.Slides(1).Shapes
        Dim shp As Shape
        ' Set only one of height and width--PowerPoint will maintain the correct
        ' aspect ratio for the video. This video is linked (as opposed to embedded).
        Set shp = .AddMediaObject2(videoFileName, msoTrue, msoFalse, 10, 10, 320)
        DisplayMediaInfo shp

        ' This audio is embedded, not linked.
        Set shp = .AddMediaObject2(audioFileName, msoFalse, msoTrue, 350, 10)
        DisplayMediaInfo shp
    End With
End Sub

Private Sub DisplayMediaInfo(shp As Shape)
    If shp.Type = msoMedia Then
        Debug.Print "Embedded: " & shp.MediaFormat.IsEmbedded
        Debug.Print "Linked: " & shp.MediaFormat.IsLinked
    End If
End Sub

PowerPoint 2010 VBA Macros

A great PowerPoint VBA Macros Resource. Lots of Useful Macros

http://skp.mvps.org/vba.htm

PowerPoint VBA Macro to Create Summary Slides with or without hyperlinks

Created by Joel Jeffrey. This is extremely useful.

Create Summary Slide Macro

VBA Macro to Take all Overflow PowerPoint Text and move to a new slide

Copy PowerPoint Macro Here.