0

Objective: I have a folder where multiple CSVs are dumped on my drive. These CSVs need to be converted to XLS files and saved (as XLS files) into the same, original folder. I have a code (pasted below) for it that works just fine, but...

Problem: A window pops up each time saying "Code execution has been interrupted," allowing me to Continue, End, or Debug. I can click Continue each time the window pops up (it pops up for each file that needs to be converted) and the script will work perfectly, but of course, I'd rather not have to click Continue potentially hundreds of times. The asterisk'd part of the code below is the part that is highlighted upon clicking Debug.

Sub Convert_CSV_XLS()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "xx:\xx\xx\xx\xx\xx\xx\xx\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

Set wb = Workbooks.Open(Filename:=strDir & "\" & strFile, Local:=True)
**wb.SaveAs Replace(wb.FullName, ".csv", ".xls"), 56**
wb.Close SaveChanges:=False

Set wb = Nothing
strFile = Dir
Loop

End Sub

Again - the code DOES work, it's just that the Debug window keeps popping up and I can't figure out what the issue is. By the way, I had to "xx" out the actual directory.

Thank you for any help!

Chase
  • 544
  • 1
  • 11
  • 25

1 Answers1

1

Try : this

It may help solving your problem, I had one of those sticky debug boxes too for no reason at all and this line helped me.

Edit: Here's the code from the website above which solves the problem described. Adding this line in the beggining of one's code will do the trick.

Application.EnableCancelKey = xlDisabled

Atheisthotdog
  • 55
  • 9
  • That worked perfectly. The problem on that webpage is exactly the same as I described, so the solution there was excellent. Thank you for sharing this information. I'll Accept this answer in 5 minutes - it's making me wait...Thanks again. – Chase Apr 30 '15 at 15:36
  • I simply googled when I had the issue, it was the first website. Try that first before questioning here mate :) – Atheisthotdog Apr 30 '15 at 16:39
  • Link only answers are not answers. Pls add in the code – brettdj May 01 '15 at 02:47
  • Sorry about that, it's fixed now. – Atheisthotdog May 04 '15 at 08:15