Excel Macro stops on .csv file open with Shortcut key

Question

I want to open a couple .CSV file and save them as an .XLSX file using a macro shortcut. When I run the macro below, saved in PERSONAL.XLSB, using View>Macro>Run all works fine. Two .csv files are opned and saved as .xlsx files. However, if I use the Macro shortucut key to run it the first file opens and everything stops. How can I get the rest of the macro to continue executing without stopping when using a shortcut key when the first file opens?

The .csv files are replaced daily so can't embed anything in them.

Sub SaveAsXLSX()

' SaveAsXLSX  Shortcut: Ctrl+Shift+W

    ChDir "T:\Temp"

    Workbooks.Open Filename:= _

        "T:\Temp\File1.csv"

    ActiveWorkbook.SaveAs Filename:= _

        "T:\Temp\File1.xlsx" _

        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWorkbook.Close

    Workbooks.Open Filename:= _

        "T:\Temp\File2.csv"

    ActiveWorkbook.SaveAs Filename:= _

        "T:\Temp\File2.xlsx" _

        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWorkbook.Close

End Sub

There is no difference between using a shortcut or use the Run option. Did you try to do it by Run button twice? Maybe it is stopped because the files are already created and it can not be saved. — David García Bodego, Oct 19 '19 at 06:51
It that is not you Idea use `ThisWorkbook`. Better use `Explicit Option` and use the name of the workbook to avoid this problem. Check [this](https://analystcave.com/vba-tip-day-activeworkbook-vs-thisworkbook/) — David García Bodego, Oct 21 '19 at 06:05
@DavidGarcíaBodego *"Activeworkbook is always calling to the workbook that owns the macro"* That's actually wrong! `ActiveWorkbook` is always the workbook that has focus (is on top) and can be any workbook the user clicks on. But `ThisWorkbook` is the workbook that owns the code (where the code is written in and runs in). — Pᴇʜ, Oct 21 '19 at 06:17
I don't think the two are handled the same. The Run command seems to continue running in the file from which it is launched. The shortcut seems to transfer control to the first newly opened file and that stops Macro execution. At least that is my guess. When I use the Run from menu method and the files exist the process pauses with a "file exists do you want to overwrite" message. I hit "yes" and the process continues. So the existing file does not block the process. — Adam Safier, Oct 21 '19 at 06:52
@Ishkatan Both methods to run the code **are** handled the same! You just use `ActiveWorkbook` wrong. The thing is that the active workbook is a different one so it behaves differently. It is not using the workbook that you expect it to use. Be more precise in calling your workbook (`ActiveWorkbook` is a very unprecise calling) and it will act the same with both methods. See my answer below. — Pᴇʜ, Oct 21 '19 at 07:03

Pᴇʜ · Answer 1 · 2019-10-21T14:59:03.650

0

The issue is that you use ActiveWorkbook which is the workbook that has focus and is on top. This can easily change by any interaction (user click for example). Avoid any statments that include Active at all cost (unless you know what you are doing and why you definitly need it). There are only very rare cases where you need them.

Instead define a variable for the workbook and set the open statement to that variable to use it later and avoid ActiveWorkbook

Sub SaveAsXLSX()
    ' SaveAsXLSX  Shortcut: Ctrl+Shift+W

    ChDir "T:\Temp"  '<-- this is not needed
    Dim Wb As Workbook

    Set Wb = Workbooks.Open(Filename:="T:\Temp\File1.csv")
    Wb.SaveAs Filename:="T:\Temp\File1.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Wb.Close

    Set Wb = Workbooks.Open(Filename:="T:\Temp\File2.csv")
    Wb.SaveAs Filename:="T:\Temp\File2.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Wb.Close
End Sub

You might benefit from reading How to avoid using Select in Excel VBA which explains this technique a bit more detailed.

edited Oct 21 '19 at 14:59

answered Oct 21 '19 at 06:59

Pᴇʜ

56,719
10
49
73

@Ishkatan right, I missed the parenthesis for the `Open` method my bad. Are you sure that you run this code (and not any old version) by shortcut? This should definitly work. – Pᴇʜ Oct 21 '19 at 15:02
I tried using a variable for the workbook. Still the same behavior. 1) Open Personal.xlsm 2) Ctrl+Shift+W 3) file1.csv Opens and stops 1) Open Personal.xlsm 2) Click View > Macros > Select SaveAsXLSX >Run 3) Each sheet opens, saves, closes In both cases Personal.xslm remains open BTW, I had to replace ... .open Filename:=" with ... .open("..... I am going to take some time to read the link you gave but I am not a VBA programer. I started with just recording a macro. – Adam Safier Oct 21 '19 at 15:04
Are you sure `Ctrl+Shift+W` is connected with the correct macro? Please put a `MsgBox "This is the correct Macro"` as first line. Then run again with the shortcut. If you don't see the message box your shortcut runs the wrong macro. – Pᴇʜ Oct 21 '19 at 15:08
Just cleaned up PERSONAL.xlsb to remove duplicate names and the only copy of the macro is in PERSONAL.xlsm. If .xlsm is open macro has the same behavior. If .xlsm is closed Ctrl+Shift+W does nothing. If you wish to take this on as a personal challenge we shoudl take it off line, but I do appreciate your effort so far. – Adam Safier Oct 21 '19 at 15:19
Well I'm confused you seem to be talking about something different now. Did you try the message box? Did the message box appear if you run the code with the shortcut? – Pᴇʜ Oct 21 '19 at 15:36
Yes, MsgBox "This is the correct Macro **" appears until I hit OK. :) in both cases. Use of Ctrl_Shift_W opens the first spread sheet and stops. View>Macros>Run shows the same msg box then completes the copy of both files. BTW, it does not matter if I have the .csv files open on screen or closed. If the target file exists I get a nice prompt asking if I want to overwrite. – Adam Safier Oct 21 '19 at 17:46
Just tried it on a 32 bit version of Office on another PC and it behaves the same (my office is 64 bit). Also, since T is a fileshare I tried changing it to C: for File 1. Behavior is the same. I need to research how I can launch Excel and execute a macro from a command line and see what happens. – Adam Safier Oct 21 '19 at 20:10
Actually that is very odd. It must be the same result (because the macro cannot know if it was started by a shortcut or the run button). Put a `Stop` in the first line so the execution of the macro halts there. Then use your shortcut to run the macro and if it stops with a yellow line at `Stop` then use F8 to run your code line by line. S you can check what your code does. – Pᴇʜ Oct 22 '19 at 06:16

Excel Macro stops on .csv file open with Shortcut key

1 Answers1