Save the file as xlsm or some sort of macro enabled format

Question

I have a macro that open xlsx file, refresh the add-in to download data and then save. However, I cannot open that file again. When I open, Excel just appears with the gray window. I think Excel save the file as some macro-enabled format. However, it still says it is an xlsx file How do I stop this? 1. How do I save the file as in xlsx format 2. Convert the previous files (the ones that were opened and saved as some sort of macro-enabled format) to xlsx format so I can open. Based on the size of the file, there are data. Here is my code :

For Each oFile In oFolder.Files
    'Set variable equal to opened workbook
    myFile = oFile.Name
    Set wb = Workbooks.Open(filename:=myPath & myFile)
    Windows(wb.Name).Visible = False
    Set cmd = Application.CommandBars("Cell").Controls("Refresh All")
    cmd.Execute
    DoEvents

    'Ensure Workbook has opened before moving on to next line of code
    wb.Close savechanges:=True

Your code is hiding the window in which the workbook would be displayed. Windows(wb.Name).Visible = False Therefore you can't see it. If you wish to see it, don't make it invisible. FYI, the format of the file, whether XLSX or XLSM, makes no difference to its appearance on your screen. — Variatus
yes, I literately just found that out. Thank you so much. How do I make your comment the answer? — duckman

Variatus Variatus · Accepted Answer · 2020-05-16T09:22:42

A grayed out application window, meaning Excel is still running, indicates that the ActiveWorkbook is invisible. Since your code hides the window with the workbook in it the gray screen indicates that there is no other workbook that Excel might show instead, i.e. one that is both open and visible.

Since you are looping through all files in a folder your intention probably is to do it invisibly, behind the scenes as it were. So, the thing that's missing might be the scenes, e.g. a workbook that can be shown while all the activity is going on invisibly. Under normal circumstances that should be ThisWorkbook, i.e. the one containing the code.

Save the file as xlsm or some sort of macro enabled format

1 Answers