Home Forums Power Pivot Update .txt connection path

This topic contains 2 replies, has 2 voices, and was last updated by  Johnath 6 months, 4 weeks ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #12034

    Johnath
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hello guys,

    I am relatively new to PP and have a (in my mind) simple question I can’t find the answer to.

    Currently I am running a vba macro that converts an .sld file into an .txt file (To update the tables with the newest data in case a customer changed  something in the .sld file).
    This .txt file is the source of my powerpivot data. Ideally this would be a standardized macro, so it can be run to convert data and update the source connection path if it is run on another computer.

    Is there any way to write a macro in vba that lets me modify/rearrange the source path?

    Kind regards

    #12035

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    Johnath,

    Simple questions from a new PP user seem (more often than not) to be a ticket into subtleties and advanced topics.

    One of the techniques used currently to simplifying changes to data source paths is to use parameters in Power Query (aka Get & Transform in Excel 2016).

    What you are talking about comes across to me as a topic something along the lines of “data driving parameters in Power Query using VBA”. Sounds like something Chris Webb would have looked into. Clicking on this link should launch you into the thick of the things.

    Best regards,

    Tom

    #12049

    Johnath
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hello Tom and thanks for your reply.

    After looking some hours through the blog you suggested, I am still left without answers.

    The file I create is the source in my PowerPivot connection. From my macro so far, I have the name and the folder of the .txt file I want to use.
    Is there no way to “easily” update the connection something along the lines of:

    “Update Source Connection to (ActiveWorkbook.Path & “Filename.txt”) ?
    Because right now it leads to C:\Users\MyName\etc. which obviously doesn’t exist on other peoples computers, therefore the file created on their computer from the macro is not active in the PowerPivot Connection.

    I have no trouble updating the source manually via existing connections in PowerPivot, but it would extremely ease things out if I was able to do it with a simple mouseclick running a macro.

    Thanks for your help in advance

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.