Extract Same Table from Multiple PDFs
I tried to modify the recorded macro to accept another PDF in table 002 with the next monthly summary table (table001 & table002 confusion). All of the files are in the same folder numbered incrementally, 1-12 (Jan-Dec). There are other similar folders for different accounts requiring the same procedure be performed and are set up in similar increments by account. Would like all of the monthly summaries from the PDF table 001 listed within one worksheet or workbook. How can this macro perform the function described? Sub ExtrctMonthlySum() ' ' ExtrctMonthlySum Macro ' ' ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""T:\Traverse\Church International & Triumphant\DCTC\Financial\Treasurer\2024\Acct 1893\2.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table002,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [Table002 (Page 1)]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table002__Page_1" .Refresh BackgroundQuery:=False End With Range("C5").Select End Sub
Power Automate/Query Assistance - Add new table in file to bottom of existing/trend assistance
Forgive me if this has been answered elsewhere, I have had a look and not been able to find! Essentially, I have set up a flow using Power Automate that saves an XL I receive via a scheduled email into a sharepoint location. I have then created an excel document that queries this file in order for me to maintain a daily report. My issue is this. I am currently trending the data, but the only way I have of doing this is by copy and paste into a separate sheet (adding to the bottom of the previous data each day/week (select timescale) manually) as the data gets overwritten on each data refresh. I'm sure there is, but is there a way via either macro or power automate of me automating this process so that each day the table in the new file is just added to the bottom of the existing?? Thanks in advance
When onboarding a device using the DFE (Device Functionality Enhancement)
When onboarding a device using the DFE (Device Functionality Enhancement) onboarding script, the device should successfully enroll in Microsoft Defender for Endpoint (MDE) and show as "Managed" in the Defender portal. However, if the device displays as "Managed by Unknown" and the "MDE Enrollment Status" is shown as "N/A," it means the device has not been properly enrolled or linked to Defender for Endpoint.Automating Party Transactions in Excel Using VLOOKUP and VBA Macros or anything
Hi everyone, I have a main sheet named "DAILY INWARD OUTWARD" that contains transactions for over 50 parties. Each party does multiple transactions, and I need to manage their stocks. Currently, I manually create separate sheets for each party and record their transactions, which is time-consuming. See this is my master sheet and I make stocks like this That month Jodhka made 4 transactions, so by searching in master sheet all over made manually my diff party list. likewise i have to maintain 100+ party details, so anyone can help me reagarding this? I would like to automate this process using VLOOKUP and VBA macros or anything. Specifically, I need help with: Using VLOOKUP to fetch transactions from the main sheet to individual party sheets. Writing a VBA macro to create separate sheets for each party and copy their transactions from the main sheet. Any guidance or examples on how to achieve this would be greatly appreciated. Thank you!
How to delete multiple cells based off the value of a specific cell.
I have an excel file with multiple sheets. I was wondering if there was a way to complete this task. When I delete the value in the column of "Hose"(A), I would like it to automatically delete everything in C9:K9. Looking at the image uploaded, when I delete "811HT-24", it would then delete RCC,x,7458,1x24,2x24,3x32,1x48 if it worked like I'm hoping. In the B column I'm using VLOOKUP tied to an index. All of the sheets are identical as well. Any help would be appreciated.Help me with Formulas pls!
Hi everyone, I have a main sheet named "DAILY INWARD OUTWARD" that contains transactions for over 50 parties. Each party does multiple transactions, and I need to manage their stocks. Currently, I manually create separate sheets for each party and record their transactions, which is time-consuming. See this is my master sheet and I make stocks like this That month Jodhka made 4 transactions, so by searching in master sheet all over made manually my diff party list. likewise i have to maintain 100+ party details, so anyone can help me reagarding this? I would like to automate this process using VLOOKUP and VBA macros or anything. Specifically, I need help with: Using VLOOKUP to fetch transactions from the main sheet to individual party sheets. Writing a VBA macro to create separate sheets for each party and copy their transactions from the main sheet. Any guidance or examples on how to achieve this would be greatly appreciated. Thank you! hereby attaching excel file too https://filetransfer.io/data-package/beXdVs3F#linkIssues with Power Automate Scripts in Excel
I'm using Power Automate to run scripts in Excel. In my flow, I created a new Excel file and populated it with data from two other Excel files. After that, I ran a script to add a table to copied data, followed by another script to perform operations on that data. However, I'm encountering some issues: 1. Occasionally, I receive a response with status code 423 and a message indicating that the file is locked for shared use by the user. 2. Other times, I get a response with status code of 200, suggesting the script executed successfully, but it doesn't find any data for operation, even though data is present, which was added in the previous step. Interestingly, when I ran the same script directly from the Excel interface, it produces the expected results. Can anyone help me understand why this is happening and how I can resolve these issues?Get a row of an Excel table that contains Excel Custom Function using Power Automate
Hi, I created a custom function in Javascript using the Yeoman generator and it is published as an Office Add-in, which runs successfully in my Excel Online and Desktop. Calculates the cost of an employee based on some input parameters (salary, salary type, benefits) Now I want to use Power Automate to populate a table with different parameters and use the custom function on one of the columns of the table to retrieve the calculated value based on the parameters. I created a flow using Excel's "Add a row to a table" and "Get a row" actions. The first action (Add a row into a table) works fine as it enters the parameter values in each column of the table. The second action (Get a row) partially works as it retrieves the values, but only those of the parameters, not the one where the custom function is, instead I get the #NAME? error. If instead of using a custom function I use a native Excel function to do a test calculation, it works, I get the result of that test function. The problem appears to be related to the way Excel updates the output of the Custom Function in the cell. I tried adding a Run Script action to update the workbook before the "Get a Row", but it doesn't work at all. Do you have any idea how to solve this problem? Thanks in advance for your help!
Cells font color based on input formula in Excel
Hi! I would like to change the font color of the cells of row "K" based on the input formula. For example if the formula is=$C$2*J9 then the font color of cell "K9" should be blue, if the formula is =$E$2*J9 then should be red. So if the formula contains $C$2 then blue, if it contains $E$2 then red. Its a big table with many rows and columns and it's a bit slow to color the cells by hand. The conditional formatting is not working in this case so is there any other idea for this? Is it possible to do it without macros? It's (hopefully) only one project, so some "simple" solution would be great (if it exist). If only macro works then let be macro. Thank you!
