cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fhtech
Level: Powered On

Custom Flow Help Needed.

I have an Excel VBA script that runs automatically to login to customer's webpage and downloads two .xls files:

  • active<dattimestamp>.xls
  • completed<datetimestamp>.xls

from the client's site and saves them to the downloads folder (OneDrive for Business)  and renames them. 
I need to build to a flow that:

  • opens each .xls file in the downloads directory
  • create a table in Sheet1 called Table1 that encompasses all filled rows.
  • for each value in column E
    • Check to see if the value exists in another workbook (master!Sheet1 column E)
    • if a match, update the row in master!Sheet1
    • if np match, add a new row to master!Sheet1
  • save master!Sheet1 and close
  • close the. xls file
  • move the .xls file to the downloads folder to processed

 

Has anyone done something like this?  I am new to Flow.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Custom Flow Help Needed.

 

Hi @fhtech ,

 

You could try to understand the function of the existing Excel Online in MS Flow according to the topic of Excel online connector in the forum or the Online doc.

Please check this online doc:

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/

 

According to your description, you could achieve the corresponding requirements through the following actions.

  • Configure OneDrive for Business-When a file is created as a trigger.
  • Create a table using the Create a table action.
  • Configure List rows in a present table action to traverse the values contained in the specified column. Then use condition to compare it to the specified value.
  • In addition, the current function in Flow can only process the data in the excel table, such as update a row in table. If it is just a worksheet, Flow does not currently support updating the data inside.
  • Finally, if the file is stored in OneDrive, you can use Move file action to move the file.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fhtech
Level: Powered On

Re: Custom Flow Help Needed.

I have updated my VBA for Excel script to convert the .xls file to the .xlsx format.  Haven't been successful at creating the table before the save.  Got the range, but the  ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lRow, lCol)), , xlYes).Name = "Table1" is failing, perhaps a hierarchy issue.  Below is the code, just not sure where to put that code; have tried a couple of places.

Sub CovertToXlsx(ByVal vFilePath)

Dim iSeconds
iSeconds = 20

Set xlApp = CreateObject("excel.application")

With xlApp.Workbooks.Open(vFilePath) 'open file
With .ActiveSheet
'Set a freeze under column 1 so that the header is always present at the top
.Range("A2").Select
xlApp.ActiveWindow.FreezePanes = True

'Get Range Info from A1:A1 to SlRowSmiley FrustratedlCol
lRow = .Cells(Rows.Count, "A").End(xlUp).Row
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column


End With

'WScript.Sleep iSeconds * 1000 '20 * 1 second
Timeout (10)
xlApp.DisplayAlerts = False



'.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", 51


.Close True 'save and close
xlApp.DisplayAlerts = True

End With
xlApp.Quit
Set xlApp = Nothing


End Sub

 

 

Nevertheless, I will try the flow suggestions you mentioned.

 

-Reba

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 178 members 3,866 guests
Please welcome our newest community members: