SOLVED

How to bridge between: PowerApps->Closed Workbook & XLOOKUP->Open Workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-3068926%22%20slang%3D%22en-US%22%3EHow%20to%20bridge%20between%3A%20PowerApps-%26gt%3BClosed%20Workbook%20%26amp%3B%20XLOOKUP-%26gt%3BOpen%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3068926%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20is%20one%20problem%20that%20I%20can%20not%20find%20the%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EPowerApps%20can%20only%20update%20a%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eclosed%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3EExcel%20data%20table.%3C%2FLI%3E%3CLI%3EXLOOKUP%20can%20only%20read%20from%20an%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Eopen%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3EExcel%20data%20table.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EWe%20use%20Excel%20to%20create%20%22printouts%22%20for%20a%20subset%20of%20customers.%20XLOOKUP%20to%20the%20subset%20customer%20data%20table%20is%20used%20to%20populate%20the%20printout%20with%20the%20customer%20information.%20We%20open%20the%20subset%20customer%20data%20table%20workbook%20and%20then%20input%20the%20customer%20ID%20into%20the%20printout%20workbook%20and%20print.%20No%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20use%20PowerApps%20to%20add%20and%20modify%20customer%20information.%20Originally%20I%20tried%20to%20use%20SharePoint%20as%20the%20data%20source%20but%20XLOOKUP%20can%20only%20access%20from%20an%20Excel%20workbook.%20And%20Power%20Automate%20to%20add%20a%20row%20to%20a%20table%20was%20reasonable%20but%20the%20update%20a%20row%20has%20proved%20to%20be%20beyond%20my%20abilities%20(if%20it%20all%20possible).%20Using%20Excel%20as%20the%20data%20source%20was%20the%20most%20direct%20and%20simplest%20way%20to%20add%20and%20update%20a%20row%20in%20a%20table.%20This%20also%20works%20with%20no%20problem.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20lies%20in%20what%20I%20mentioned%20at%20the%20top.%20PowerApps%20needs%20to%20work%20with%20a%20%3CEM%3Eclosed%3C%2FEM%3E%20workbook%20and%20XLOOKUP%20an%20%3CEM%3Eopened%3C%2FEM%3E%20workbook.%20If%20this%20action%20were%20only%20being%20completed%20by%20two%20or%20three%20staff%20it%20would%20be%20possible%20to%20coordinate%20opening%20and%20closing%20the%20workbooks.%20But%20we%20have%20more%20than%20200%20staff%20over%2050%20locations%20who%20will%20be%20updating%20the%20customer%20information%20with%20PowerApps%20and%20then%20printing%20out%20the%20customer%20report.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20at%20a%20loss%20on%20what%20to%20do.%20Any%20and%20all%20help%20will%20be%20greatly%20greatly%20appreciated!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3068926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

There is one problem that I can not find the solution.

 

  • PowerApps can only update a closed Excel data table.
  • XLOOKUP can only read from an open Excel data table.

We use Excel to create "printouts" for a subset of customers. XLOOKUP to the subset customer data table is used to populate the printout with the customer information. We open the subset customer data table workbook and then input the customer ID into the printout workbook and print. No problem.

 

We use PowerApps to add and modify customer information. Originally I tried to use SharePoint as the data source but XLOOKUP can only access from an Excel workbook. And Power Automate to add a row to a table was reasonable but the update a row has proved to be beyond my abilities (if it all possible). Using Excel as the data source was the most direct and simplest way to add and update a row in a table. This also works with no problem. 

 

The problem lies in what I mentioned at the top. PowerApps needs to work with a closed workbook and XLOOKUP an opened workbook. If this action were only being completed by two or three staff it would be possible to coordinate opening and closing the workbooks. But we have more than 200 staff over 50 locations who will be updating the customer information with PowerApps and then printing out the customer report.

 

I'm at a loss on what to do. Any and all help will be greatly greatly appreciated!! 

2 Replies
best response confirmed by Chris_George (Occasional Contributor)
Solution

@Chris_George 

As variant you may keep the file updated by PowerApps always closed. Within form file Power Query the source and load it into helper sheet which could be hided. Desirably using Power Query with entered ID filter. Fill form with XLOOKUP this helper table. Thus the process will be

- open form

- enter ID

- Refresh All

- wait for a while

- print form

 

Unfortunately it requires some discipline from end users with refreshing.

@Sergei Baklan 

 

Everything works like a charm! Thank you so much!

 

I had never considered using Power Query as a snapshot of the data and then into an Excel table for the XLOOKUP. Because of this, I went ahead and changed my PowerApps data source to SharePoint List instead of Excel. Much smoother interactivity.

 

I put a big reminder on the printout to refresh all and so far so good!