cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 SlRow:SlCol
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

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

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 Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,170)