none
updating powerpivot datasource with vba RRS feed

  • Question

  • I have searched extensively and have found no solution to this seemingly basic functional design point of the otherwise brilliant powerpivot.

    I have an access database that I use as a datasource for my powerpivot.

    I have many copies of the database (not looking for a change in solution here )

    example

    database1.mdb

    excel (template1.xlsm) pivot <==> powerypivot modelxx <==> database1.mdb

    vba

    filecopy template1.xlsm , database2_report.xlsm

    modelxx.datassource.connection="database2.mdb"

    (I know the syntax for the connection strings)

    HELP !!

    also

    if the users have rename dtabase2_report.xlsm to my_database2_report.xlsm and I update database2_report.xlsm

    can they "connect my_database2_report.xlsm.model=database2_report.xlsm .model

    (Seems logical to me that we can connect a pivot to a different model, like we can do with pivotcache)

    Adam


    Adam

    Sunday, January 21, 2018 2:07 PM

All replies

  • Hi AuroraMan1,

    You had asked,"if the users have rename dtabase2_report.xlsm to my_database2_report.xlsm and I update database2_report.xlsm. can they "connect my_database2_report.xlsm.model=database2_report.xlsm .model"

    If user rename any file and still you are using the old file name in your code then you will get an error like File not found.

    So, Whenever User renames the file then you also need to modify the file name in your code.

    Otherwise, It will not able to find / access the file anymore.

    If the modified file name and the file name you had used in your code are same then it should work in general conditions. But we are also not familiar with PowerPivot.

    You had posted very limited code, With that we are not able to get the proper idea how your code interact with PowerPivot.

    I suggest you to post a full Sample code , Excel files, Access DB and detailed steps to reproduce the issue.

    We will try to make a test on our side and let you know about the results.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 2:43 AM
    Moderator
  • I am fully aware of how to copy / rename files (there is no issues with that), it was only a example of the logic flow. This is a PowerPivot Question.

    I have posted an example here

    https://www.dropbox.com/s/rdvxyo7hete79hq/PowerQueryExample.zip?dl=0

    Thanks

    Adam


    Adam

    Monday, January 22, 2018 12:39 PM
  • Hi AuroraMan1,

    You had mentioned that,

    I try to test your code but I am getting error because I am not using PowerPivot Addin and maybe your workbook has connection with it.

    For PowerPivot specific issue , You can post your issue in Forum below.

    MSDN Power Pivot Forum

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 23, 2018 8:18 AM
    Moderator
  • Thanks for the pointer !

    Adam


    Adam

    Tuesday, January 23, 2018 1:34 PM
  • Hi AuroraMan1,

    I suggest you to take appropriate actions to close this thread before creating new thread on Power Pivot Forum.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 24, 2018 6:28 AM
    Moderator
  • Deepak, I was hoping to get an answer to my question. I followed your suggestion and opened a thread in power pivot, and (I think one of your colleagues )  suggest I open a thread here.

    I will leave this open unit a suitable answer has occurred.

    Adam


    Adam

    Wednesday, January 24, 2018 10:18 AM
  • Hi AuroraMan1,

    Your goal is ,"updating powerpivot datasource with vba".

    For that purpose, I find link below. But it is a manual approach.

    Make changes to an existing data source in Power Pivot

    I did not find any other MSDN documentation to perform the same using VBA.

    Other old thread , I find on Power Pivot and StackOverFlow forum which had same kind of issue.

    Using VBA To Add or change Power Pivot data connection

    Using VBA To Add or change Power Pivot data connection

    You can find some third party links which propose the solution but they are not working.

    The code you had shown in your xlsm file is creating a OLEDB connection with Access database.

    But there is no any link for Powerpivot here.

    You can verify the same with VBA PowerPivot model.

    About the PowerPivot Model Object in Excel

    You will find that, all the properties of the methods are Read Only.

    Means you can access it but not modify it.

    Currently, It looks like it is not possible to updating powerpivot datasource with vba.

    You have to follow the manual approach.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 25, 2018 8:27 AM
    Moderator
  • Deepak, Thank you for summarizing my issue. maybe second line support can get involved and provide a solution.

                  As you have found out, this is an issue for many people. Manually updating is not a solution as I expect to have 100s of copies of the model. (each with very specific data (for privacy reasons)).

    Adam


    Adam

    Thursday, January 25, 2018 11:22 AM
  • Hi AuroraMan1,

    You had mentioned that,"maybe second line support can get involved and provide a solution."

    Which second line support you are talking about?

    If you try to clear it then I will try to provide all possible help to solve this issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 26, 2018 1:28 AM
    Moderator
  • Deepak

    second line support for powerpivot

    as you stated "But we are also not familiar with PowerPivot."

    I am looking for someone from Microsoft that knows powerpivot and VBA.

    Thanks

    Adam


    Adam

    Friday, January 26, 2018 1:44 PM
  • Hi AuroraMan1,

    You had mentioned that,"I am looking for someone from Microsoft that knows powerpivot and VBA."

    For that, I escalate this thread to some senior engineer.

    They will try to look in your issue and try to provide you further suggestions.

    Thanks for your understanding.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 29, 2018 8:59 AM
    Moderator
  • Deepak, Any update on second line support?

    Thanks

    Adam


    Adam

    Friday, February 16, 2018 2:56 AM
  • Deepak,

    "For that, I escalate this thread to some senior engineer."

    Any response from your escalation?

    Thanks

    Adam


    Adam

    Wednesday, April 25, 2018 11:30 AM
  • Hi AuroraMan1,

    You had asked,"Any response from your escalation?"

    I did not got any response. Senior Engineer will directly post the suggestion on the thread.

    They will not inform me about that.

    Looks like the thread is getting 4 months old.

    I suggest you to create a new thread. We will again try to escalate it and I hope you will get response from them.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 2:00 AM
    Moderator