Custom Flow Help Needed.

(0) Share

Report

Posted on by fhtech

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.

Categories:

General topics

I have the same question (0)

All responses (2)

Answers (0)

v-bacao-msft on at

Like (0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
fhtech 30 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No