cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Community Support
Community Support

 

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.
Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (8,094)