0

I have a program that opens up a file, pulls data from it, and puts said data into the corresponding worksheet based on the some information form the file it opens (that part isn't important). I have a Power Query that tells me the names and locations of all the files within a specified folder.

The macro will open the sheet with the power query and begin opening/closing and pulling the data from the files one at a time. Up until now, it has run smoothly. However, today I received an error saying "ActiveX Control can't create object." I have never seen this error before. When I go to Debug, it shows me the line with the error:

ThisWorkbook.Worksheets("Query1").Activate

I am unsure as to why it would all of the sudden be erroring out. Any ideas?

Teamothy
  • 2,000
  • 3
  • 16
  • 26
zrobers
  • 7
  • 2
  • 1
    Hard to say without seeing more code. You may find [this](https://stackoverflow.com/help/mcve) to be useful in providing us with a minimal, complete example of your code – cybernetic.nomad Nov 20 '19 at 19:18
  • 1
    Unclear why you would need to activate any worksheet to begin with, or to dereference a worksheet *at all* from `ThisWorkbook.Worksheets`. Is the sheet created at run-time or it exists in `ThisWorkbook` at compile-time? – Mathieu Guindon Nov 20 '19 at 19:37
  • @MathieuGuindon I am not sure what you mean by that (I am relatively new to VBA). I was, however, able to fix this by changing any instance of "ThisWorkbook" to "ActiveWorkbook." I know this does not give you a lot to look at, but any idea as to why that would work? I know some implications can arise later down the road with using ActiveWorkbook as opposed to ThisWorkbook, but it will work for now at least. I see now that I could just reference the cells on the worksheet without activating it, but regardless, any instance of ThisWorkbook gives me the same error. – zrobers Nov 21 '19 at 20:23
  • `ThisWorkbook` is always going to be the document that's hosting your VBA project. `ActiveWorkbook` is whatever document is currently active. Read [this](https://stackoverflow.com/q/10714251/1188513) though: you almost (like 99.999%) never need to `Select` or `Activate` anything. – Mathieu Guindon Nov 21 '19 at 20:23
  • I know that. It's just strange that ThisWorkbook would not work, but ActiveWorkbook did. The workbook hosting the project is the only one I used/had open. The error came out of nowhere after running the program multiple times flawlessly. – zrobers Nov 21 '19 at 20:26

0 Answers0