Excel creating additional VBA projects when importing file

Question

I'm currently working on a project, that grabs a csv file from a website, and puts it in a pre-defined page in my workbook. It works...to an extent, but it keeps creating new VBA projects that I'm not really understanding, and eventually stops workings. Note, for the particular website I'm extracting this from I cannot use internet explorer.

The Code I'm Using:

Private Sub CommandButton3_Click()
 On Error Resume Next
 Kill "C:\Users\Me\Downloads\products.csv"
 On Error GoTo 0
 Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url url-string-here")
 Application.Wait (Now + TimeValue("00:00:25"))
 sCSVLink = "C:\Users\Me\Downloads\file.csv"
 sfile = "file.csv"
 ssheet = "CurrentListings"
 Set wnd = ActiveWindow
 Application.ScreenUpdating = False
 Sheets(ssheet).Cells.ClearContents
 Workbooks.Open Filename:=sCSVLink
 Windows(sfile).Activate
 ActiveSheet.Cells.Copy
 wnd.Activate
 Sheets("CurrentListings").Paste
 Application.DisplayAlerts = False
 Windows(sfile).Close False
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 Sheets("CurrentListings").Range("E:E").WrapText = False
End Sub

Like I said, it will work a few times but this happens each time:

Eventually it gives me a runtime error, any suggestions?

Also, have a look at [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Generally, relying on `ActiveSheet`, `ActiveWindow`, `Select`, etc., leads to code which is very difficult to interpret. Better to scope appropriate object variables and use them instead -- easier to read, easier to debug, easier to fix in the future :) — David Zemens, Feb 15 '17 at 02:48
Run-time error '1004' It's telling me to paste values to the first cell, which i've tried but I still get that error — Tyler Serino, Feb 15 '17 at 04:40
it's not telling you that. what *exactly* is the error message? — David Zemens, Feb 15 '17 at 04:41
"Run-time error '1004': To copy cells from another worksheet to this worksheet, make sure you paste them into the first cell (A1 or R1C1)." ...as a matter of fact. — Tyler Serino, Feb 15 '17 at 16:23

score 1 · Answer 1 · answered Feb 15 '17 at 02:42

The ghost projects in the VBE typically come about because of global variables holding references to the workbooks. If you're certain it's not your own code that's holding the variable references, it could be a COM add-in.

Try disabling all of your COM add-ins in Excel (Developer Tab, COM Add-ins button) and then running your code. Then add back COM add-ins until you replicate the problem.

In my experience, it has been the MS Power Query Add-in that causes the problem.

Excel creating additional VBA projects when importing file

1 Answers1