problem with VBA and power query

[mickeystanford_alumni]

Hi, I have a file which imports a query through power query, pretty simple query with a file directly. Then, in this data I use a vba code to automate the named range columns, by telling the code to name the range of the column with the concatenate of the first, second, and third row. The code works perfectly with one query, however, when I implement this code to a bigger file with more than 1 queries, it just bugs and takes ages to get executed, so I have to cancel.

Could anyone help me on how to solve it?

Sub UpdateWorkbookAndRefreshQueries()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastCol As Integer
    Dim i As Integer
    Dim colName As String
    Dim namedRange As Range
    Dim validName As String
    Dim name As name
    Dim conn As WorkbookConnection

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("DATAPACK")  'NAME OF SHEET

    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
   
    For i = 1 To lastCol
        colName = ws.Cells(1, i).Value & "." & ws.Cells(2, i).Value & "." & ws.Cells(3, i).Value
        validName = Replace(colName, " ", ".")
        Set namedRange = ws.Range(ws.Cells(1, i), ws.Cells(ws.UsedRange.Rows.Count, i))
       
        wb.Names.Add name:=validName, RefersTo:=namedRange
        On Error GoTo 0
    Next i

    For Each conn In wb.Connections
        conn.Refresh
    Next conn

    MsgBox "Columns named and queries refreshed successfully.", vbInformation
End Sub

 

[jkpieterse]

What do you mean "it just bugs"? If you step through the code, can you pin-point which part takes a long time?

 

[mickeystanford_alumni]

What do you mean "it just bugs"? If you step through the code, can you pin-point which part takes a long time?

thank your for the promt response. yes here:

wb.Names.Add name:=validName, RefersTo:=namedRange

the problem I had before is that every time I was running the code to automatically name the ranges of the columns, the query linked to the sheet DATAPACK was getting disconnected, so then I was getting the error on the download.

would be great if you could give me a hand there.

 

[jkpieterse]

Does it work if you change that line to:

namedRange.Name = validName

 

[mickeystanford_alumni]

Does it work if you change that line to:

namedRange.Name = validName

Hi jkpieterse, I just did a modification actually; however, although the named ranges are created indeed perfectly, my query gets kind of disconnected. So if i try to refresh it it doesn't load...

Sub UpdateWorkbookAndRefreshQueries()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastCol As Integer
    Dim i As Integer
    Dim colName As String
    Dim namedRange As Range
    Dim validName As String
    Dim name As name
    Dim conn As WorkbookConnection

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("DATAPACK")  'NAME OF SHEET

    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    For i = 1 To lastCol
        colName = ws.Cells(1, i).Value & "." & ws.Cells(2, i).Value & "." & ws.Cells(3, i).Value
        validName = Replace(colName, " ", ".")
        Set namedRange = ws.Range(ws.Cells(1, i), ws.Cells(ws.UsedRange.Rows.Count, i))
       
        wb.Names.Add name:=validName, RefersTo:=namedRange
        On Error GoTo 0
    Next i

    MsgBox "Columns named and queries refreshed successfully.", vbInformation
End Sub

 

[jkpieterse]

Hard to say without having access to the workbook in question.

 

[mickeystanford_alumni]

Hard to say without having access to the workbook in question.

If not too much hassle, you could quickly do it by quickly querying a file into your sheet with the following data (below).
Your sheet queried in your destination file should be called DATAPACK.
The code remains the same, your columns will be named ABC, DEF and GHI respectively.
After running the code, the problem will arise and you won't be able to refresh your query again...

A D G
B E H
C F I

 

[jkpieterse]

If you could just share the files I need using box or OneDrive or any other filesharing service that'd be great.

 

[mickeystanford_alumni]

If you could just share the files I need using box or OneDrive or any other filesharing service that'd be great.

well i think that it is easier for code purposes if you just take a file like the one I told you to create, I won't share anything different than that.