Create Then Refresh an HTML Using Excel Table Using Power Automate

[austinandreikurt]

Help!

I am really having a hard time on this. I want to create an HTML (website) and have it refreshed automatically every time an excel file is being updated. I really do not know the step by step and just tried to google some. What I created so far is using Power Automate (if you have better suggestion, I am okay with it as long as I can follow the steps) and have a "Manual trigger a flow" -> List rows present in a table -> "Select" -> Create HTML table then that's it. I do not know if what I am doing is correct. I need the flow to create a URL that is viewable for me. Then a flow to have that url refresh everytime that excel is updated.

Below is the "Table1" (Column A is not included in the table but has formula on it) I have on excel:

	Log In (Col B)	Password (Col C)	General Access (Col D)	FBS Generation (Col E)
1	marco	12456568	Active	Active
2	nestor@gmail.com	124893	Active	Active
3	leah125_56@yahoo.com	2498415	Active	Active

Then my last purpose once I have that working HTML URL is to use that URL to extract data to an excel vba that was on a local drive from different pcs using below codes:

Public Sub UseQueryTable()

Dim table As QueryTable
For Each table In Sheet1.QueryTables
    table.Delete
Next table
Sheet1.Cells.Clear

Dim url As String
url = "https://????"

'Dim table As QueryTable
Set table = Sheet1.QueryTables.Add("URL;" & url, Sheet1.Range("A1"))

With table
    .WebSelectionType = xlEntirePage
    '.WebTables = "1"
    .WebFormatting = xlWebFormattingNone
    .Refresh
End With

End Sub

I really hope someone can help me on this! Thank you in advance!

 

[ExcelToDAX]

I don't think Power Automate can create a new "website" for you, but you can dynamically change the URL in a string variable for other people to access. What kind of URL are you trying to accomplish? It looks like in the flow you are just creating an HTML table.

 

[austinandreikurt]

@ExcelToDAX Thank you for your reply. I am not really sure if what I am doing is correct. Basically, I just need it to create a file as an http url link and the content is what was on my Table1 of excel. Then have another flow that automatically triggers to update that link once the excel is updated. I am open to suggestions for alternatives but I am really just a beginner and don't know the step by steps.

 

[ExcelToDAX]

So you need to create an Excel file with the Table1 data. Then you need another flow to extract the table when it is updated, and upload to the same file that was originally created? So you just want one Excel file to update another Excel file every time it is updated?

 

[austinandreikurt]

@ExcelToDAX No. Basically I need it to be on a URL. Because the data will be needed and should be available for an excel in a local drive or any PC while maintaining the Admin Access in a sharepoint. The goal is that when a user who download an excel macro run its codes, the macro will first check on a URL link if the log-in and password supplied has an "Active" status. That URL link or Admin Excel will be maintained by us through a sharepoint. The easier way I see it is to create an HTML website and use that URL in my codes above.

EXCEL (saved on local drive of user) will be run by a client

VBA CODE will check for STATUS first before running other codes

VBA Code will connect with a URL to check on status (html content can be pasted on a sheet once executed for easier lookup of status)

URL is maintained and link to an EXCEL (sharepoint)

 

[austinandreikurt]

By the way, I tried using just Excel online (one that was on sharepoint) link to connect with an Excel app (saved on a local drive on another computer with no access to sharepoint) but it won't work. That is why I tried using URL link. The problem is how to create that URL link that can be updated/refreshed using the data on Excel online (one that was on sharepoint)

 

[austinandreikurt]

If there is a way to just connect between Excel Online (saved on sharepoint) and Excel App desktop (saved on local drive by anyone) then I am okay with it. I tried using Data Query but it needs permission first to connect them but the ones with Excel on their local drive are outside our organization and doesn't have permission to access our files. The only method I can think of is:
1. Connect Excel Online (sharepoint) to an HTML website (content is just what is in the Excel table) and will be updated automatically with excel as they are linked
2. Then Excel App (local drive) can connect with the HTML website (which is not editable) and capture its content.
Thus, HTML website acts as the 3rd party that will connect both Excels for the codes to work.
I'm okay with others solutions with your guidance. Thank you!