0

I need to create a macro that can be used by other people that will import data monthly and create two pivot tables from two Excel workbooks.

The best method I devised is to have the person download the two files into the same folder as this macro-enabled workbook and name them the same for the purpose of querying and pulling the data (i.e. Output1 and Output2) then append with the date and move to a storage folder once they have run the macro and gotten the tables they want. (If I am misunderstanding how querying works and the data needs to remain available in the same location and name for the pivot tables to continue existing please let me know.) This way I can hardcode the files to pull from and it's minimal effort on their part.

The problem When I record the macro, it makes a static name for each query with

ActiveWorkbook.Queries.Add

Which means that when I run it for the second month, I get an error that the query connection already exists and the macro errors out.

I found this code that is supposed to check if the query already exists, and if it does; delete it, but I can't make it work.

Dim qry As WorkbookQuery

If DoesQueryExist(TS) Then
    ' Deleting the query
    Set qry = ThisWorkbook.Queries(TS)
    qry.Delete
End If

I placed this inside my macro sub.

I wasn't able to find very much on the function "DoesQueryExist".
Is there another solution to instead create unlimited queries with arbitrary names instead of having to check and delete?

Community
  • 1
  • 1
PinkMonkeyBird
  • 11
  • 4
  • Did you find the function `DoesQueryExist` [here](https://www.msaccessgurus.com/VBA/Code/DAO_DoesTableExist.htm)? First of all, note that's for *Access*. – BigBen Mar 23 '22 at 16:00
  • I thought VBA was consistent for all Office Apps? I found it here:https://stackoverflow.com/questions/51386600/how-to-automate-a-power-query-in-vba – PinkMonkeyBird Mar 23 '22 at 16:03
  • For the most part, VBA is, but each application has its own object model and application specific functions. – braX Mar 23 '22 at 16:04
  • Okay. Darn. So that solution is out, is there a similar thing that can be used for excel or is the idea to find a way to increment on each use or make unique the query names the way to go? – PinkMonkeyBird Mar 23 '22 at 16:06

1 Answers1

1

You need to add the code for DoesQueryExist:

Function DoesQueryExist(ByVal queryName As String) As Boolean
    ' Helper function to check if a query with the given name already exists
    Dim qry As WorkbookQuery
    
    If (ThisWorkbook.Queries.Count = 0) Then
        DoesQueryExist = False
        Exit Function
    End If
    
    For Each qry In ThisWorkbook.Queries
        If (qry.Name = queryName) Then
            DoesQueryExist = True
            Exit Function
        End If
    Next
    DoesQueryExist = False
End Function

Sourced from archive.org.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • I am having the same problem for the name of the pivot table. Would this be usable to duplicate with the name of the pivot table, or am I going about this all wrong? Some other way to increment titles on each use? I think I see now that the Query can't just be named whatever I want, but I imagine the pivottable can – PinkMonkeyBird Mar 23 '22 at 17:38
  • You can create a similar helper function to see if a pivot table with a certain name exists. – BigBen Mar 23 '22 at 17:39
  • Wont that delete my existing pivot table though? I want to hopefully keep all the pivot tables moving forward – PinkMonkeyBird Mar 23 '22 at 17:50
  • Oh, I thought you wanted to delete the existing table. I'm a bit confused as to what your actual general goal is here. – BigBen Mar 23 '22 at 18:01
  • Once a month, there is two sets of data I want to make two pivot tables from. I want to create a macro that minimizes the amount of work needed before and after it's run. Then instead of keeping these gigantic files that have a lot of unnecessary data, we just keep the months pivot tables that give us at a glance for the things we actually care about. (extrapolated data though like counts so I can't just remove rows/columns) – PinkMonkeyBird Mar 23 '22 at 18:25