Unanswered

deletes macro in excel

(0) Share

Report

Posted on 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.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (14)

Answers (0)

Sort by

BalamuruganDev 57 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @pwj1 ,

Can you share the flow design for better understanding,

Thanks,
BD

Was this reply helpful? Yes No
pwj1 16 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
BalamuruganDev 57 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
pwj1 16 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No

Kaif_Siddique

2,108 Super User 2024 Season 1 on at

Like (0)

Report

Copy link

Link copied!

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

Was this reply helpful? Yes No

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
pwj1 16 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
pwj1 16 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Kaif_Siddique 2,108 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Run macros on an Excel workbook:
https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/run-macros-excel

Was this reply helpful? Yes No
pwj1 16 on at

Like
a
(0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No