deletes macro in excel

Like (0) Share

Report

Posted on 13 Feb 2023 08:10:19 by 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.

All responses (14)

Answers (0)

pwj1 16 on 20 Feb 2023 at 10:52:21

Like (0)

Report

Re: deletes macro in excel

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
ThomasFonn 272 on 17 Feb 2023 at 13:57:03

Like (0)

Report
Re: deletes macro in excel

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.
Open the excel sheet.
Select the top left cell on the current sheet.
Use the Send-Keys action and send {Alt}({F5}) , this will do the refresh-command.
Repeat for all sheets (steps 2 and 3 can be wrapped in a loop if you like).
Close Excel.
Done!
pwj1 16 on 17 Feb 2023 at 07:36:32

Like (0)

Report

Re: deletes macro in excel

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 272 on 17 Feb 2023 at 07:28:22

Like (0)

Report

Re: deletes macro in excel

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 16 on 17 Feb 2023 at 07:15:17

Like (0)

Report

Re: deletes macro in excel

@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
Kaif_Siddique 2,104 Super User 2024 Season 1 on 14 Feb 2023 at 18:49:28

Like (0)

Report

Re: deletes macro in excel

Run macros on an Excel workbook:
https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/run-macros-excel
pwj1 16 on 14 Feb 2023 at 07:38:43

Like (0)

Report

Re: deletes macro in excel

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?
pwj1 16 on 14 Feb 2023 at 07:02:02

Like (0)

Report

Re: deletes macro in excel

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.
MichaelAnnis 5,721 Super User 2025 Season 1 on 14 Feb 2023 at 06:14:06

Like (0)

Report

Re: deletes macro in excel

@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!

Kaif_Siddique

2,104 Super User 2024 Season 1 on 13 Feb 2023 at 20:40:09

Like (0)

Report

Re: deletes macro in excel

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.