Forum Discussion

Berger1012's avatar
Berger1012
Copper Contributor
Apr 20, 2020

VBA Error when creating power query with variables

I have a problem with VBA. I want to create a power query and I get this error: run-time error 1004, application or object-defined error. I really don't know how to fix it and would really hope that ...
excel
Macros and VBA
Berger1012's avatar
Berger1012
Copper Contributor
Apr 20, 2020

Thank you so much for your answer!

I just changed the variables to this:

 

Dim varName As Variant
varName = "appf us"

Dim varSource As Variant
varSource = "C:\Users\Thoma\Downloads\Neuer Ordner\appf.us.txt"

 

 

No I get the same error again. The debugger tells me, that the error occours in this line of the complete code:

.ListObject.DisplayName = varName



So I still think that the error has to be in the variable "varName" but I can't find a solution to fix it.

 

JKPieterse's avatar
JKPieterse
Silver Contributor
Apr 20, 2020
A listobject's name cannot contain spaces, that is the source of your error.
  • Berger1012's avatar
    Berger1012
    Copper Contributor
    Apr 20, 2020

    Thank you for your answer!

    I just changed the varibale varName to this:

    varName = "appf"

     

    The space is now deleted but it still does not work. The error is also still the same but now the debugger tells me that the error occours in this line of the complete code:

    .Refresh BackgroundQuery:=False

     

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Apr 20, 2020
      Now you've gotten to this stage you can go into the query and see which step causes an error. Stop the VBA macro first of course.
      • Berger1012's avatar
        Berger1012
        Copper Contributor
        Apr 20, 2020

        I really appreciate your help!

        The data is loaded in the power query editor like it should be. It looks exactly the same as it looked when I created the power query with the editor. Take a look:

         

         

        But in the Excel sheet there is no power query table. It just says "ExternData_1: receiving Data...". Here is a Screenshot:




        When I press the data update button in Excel I get this error:

        "The import varName does not match an export. This is maybe caused by an missing module reference"

        So the error has to be at the variable varName but I am still not able to find a reason for this. 

        I am sorry that this problem is so complicated but I really want to thank you for your time!

Resources