deletes macro in excel

[pwj1]

Hey in here.

I have a excel with macros, that need to update every day. Its a simple power automate command, it opens the file (the excel macro is set to run when the file is open) power automate waits until its finished and save and closes again.

The problem is that sometimes the macro in the excel file is deleted, and i cant figure out why it does this, the excel file is saved as an .xlsm file.

Dont know if it is power automate that corrupt this or if its the excel file that is the problem. But it can run severel weeks without deleting the macro.

[BalamuruganDev]

Hello @pwj1 ,

Can you share the flow design for better understanding,

Thanks,

BD

[pwj1]

The language is danish, so i hope you understand it anyway 🙂

[BalamuruganDev]

Hello @pwj1 ,

Below solution works to me, and after flow run could see the macro available in excel file.

Make sure PAD is up to date.(version 2.28)

Thanks,

BD

[pwj1]

My problem is the excel file. When it opens and runs this macro:

Private Sub Workbook_Open()

    ActiveWorkbook.Connections("Forespørgsel - _50048_Prod Sedler").Refresh

    ActiveWorkbook.Connections("Forespørgsel - _50066_Maskinlinier").Refresh

    ActiveWorkbook.Connections("Forespørgsel - Dato(1)").Refresh

End Sub

This macro dissapears randomly at some point, it can run severel weeks, and all of a sudden the macro is gone, i dont know if its power automate that is messing it up or its the excel file itself.

[Kaif_Siddique]

Hi @pwj1 ,

Power Automate flow and Macro both are fine.

I think issue is happening because of network or connectivity(or VPN connectivity). So basically, ActiveWorkbook.Connections extract data from external source. And if any network is failed/weak, then ActiveWorkbook.Connections will not execute successfully.

So, I would suggest add additional code in macro to create a log file.  This will let you know the refresh was successful or not.

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FileToCreate = FSO.CreateTextFile("C:\Test\TestFile.txt")

FileToCreate.Write Now() & " --> Successful"
FileToCreate.Close

Another point, the wait time is 5 seconds, but if file not refreshed within 5 sec due to network or any other reasons, then flow will close the workbook without completing the refresh. This might be the reason of deleting the macro.

If I have answered your question, please mark my post as Solved.

If you like my response, please give it a Thumbs Up.

Regards

Kaif

[MichaelAnnis]

@BalamuruganDev is right. Except for the fact that your macro runs on open. Here is what I would do:

create a new .xlsm which has a macro which:

opens the file (it should wait for the macro to run on its own, cuz it’s excel)

saves and closes the file

use the bot to open this new xlsm and run the macro (the new one that opens the other file)

the bot will wait for the macro to finish, so you will no longer have to guess in wait time. 

make sure to save and close your new workbook in the bot. 

good luck!

[pwj1]

Thx for your great input, im a bit new to power automate. So i will test it and then i will return if it works. Maybe some input how the power automate is set up to wait for the for the macro to begin.

[pwj1]

Is it possible to insert the macro script in power automate, and tell power automate to run the excel script when excel opens the file?

[Kaif_Siddique]

Run macros on an Excel workbook:

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/run-macros-excel

[pwj1]

@Kaif_Siddique  i want the macro code to run in power automate, and not in the macro in excel. Is this possible? and where to put the code in power automate

[ThomasFonn]

Hi, you could use the "Run VB-script"-action to accomplish this but you may need to alter the macro code a bit to get it to work.

[pwj1]

I have this to run in excel

Private Sub Workbook_Open()

    ActiveWorkbook.Connections("Forespørgsel - _50048_Prod Sedler").Refresh

    ActiveWorkbook.Connections("Forespørgsel - _50066_Maskinlinier").Refresh

    ActiveWorkbook.Connections("Forespørgsel - Dato(1)").Refresh

End Sub

don´t know if i just can paste the macro to the vb script? Or what changes do i need to do here?

[ThomasFonn]

Sadly, i'm not proficient in VB-Script enough to guide you any further,

However, The things your macro performs is easily achieveable with a few actions in Power Automate Desktop.

1. Open the excel sheet.
2. Select the top left cell on the current sheet.
3. Use the Send-Keys action and send {Alt}({F5}) , this will do the refresh-command.
4. Repeat for all sheets (steps 2 and 3 can be wrapped in a loop if you like).
5. Close Excel.

Done!

[pwj1]

I found another user with the same problem. The one suggestion is to update excel. There were updates to excel. So maybe i will wait to see if this solves the problem, before doing a lot of other things. It would be easy if this solved the problem