Activating a .MHTML workbook that auto opens

[allout80]

Hello all,

I'm having trouble dealing with a .MHTML workbook. This workbook opens automatically from as report generated from an ERP system. My end goal is to activate this .MHTML that opens and then save it as a .xlsx file.
Here's what I have so far. However I get a subscript out range error on the first line. I have checked and the name of the workbook that opens up is indeed "qmMMData.MHTML"

    Dim FName As String
    Dim FPath As String
    
    
    With Windows("qmMMData.MHTML")
        
        Windows("qmMMData.MHTML").Activate
        FPath = ActiveWorkbook.path
        FName = "qmMMData"
        ActiveWorkbook.SaveAs filename:=FPath & "\" & FName & ".xlsx"
    
    End With

 

[bobsan42]

Try to use Workbooks instead of Windows.

 

[bobsan42]

I read your post again ...
How exactly is the Workbook opened. Is it possible that it is opened in a separate instance of Excel.
You can check Task Manager to check how many Excel instances are loaded.
or check if the value of Application.Workbooks.Count is what you would expect.

 

[allout80]

I read your post again ...
How exactly is the Workbook opened. Is it possible that it is opened in a separate instance of Excel.
You can check Task Manager to check how many Excel instances are loaded.
or check if the value of Application.Workbooks.Count is what you would expect.

The just checked the value and it is 5. I would've expected 6 lol because the ERP system actually outputs 5 different reports. 4 are in .xlsx format and 1 is in .MHTML format

 

[bobsan42]

Check the task manager to confirm two instances.
The other thing to do is check application.workbooks(i).name for i = 1 to 5 to see if the mhtml is in there.

 

[allout80]

Check the task manager to confirm two instances.
The other thing to do is check application.workbooks(i).name for i = 1 to 5 to see if the mhtml is in there.

I just did that and all the names of the reports show up (5 in total). The name of the workbook that the main macro is located in doesn't show up. Maybe this is due to the fact that the reports are regular excel worksheets whereas the main macro is a Macro-Enabled worksheet.

 

[allout80]

I was actually able to find a workaround but why the code above didn't work still boggles me.

 

[bobsan42]

Nope.
I think my assumptions are correct.
You have to run these checks from the workbook with the macro - if you end up with two instances of vbeditor this means two applications. If your macro workbook is loaded but you don't see it in the project explorer of vbe - the same conclusion.

 

[bobsan42]

I was actually able to find a workaround but why the code above didn't work still boggles me.

Because the workbook you try to refer to is in another application instance.
The simplest workaround is to open the macro workbook after the reports are loaded.

 

[allout80]

Because the workbook you try to refer to is in another application instance.
The simplest workaround is to open the macro workbook after the reports are loaded.

Got it. Thanks for the help buddy