1. S

    Mass extraction of multiple Pivot table setup conditions

    I inherited 5 excel files between them have >100 individual pivot tables across multiple worksheets. Some sheets have multiple tables. I’d like to extract the file name, worksheet, cell location and most importantly, pivot table setup conditions (filter selections, rows, columns & values) for...
  2. S

    VBA UserForm connected to Maria DB

    Good Morning, I have been working on a VBA code in order to modify data in a MariaDB database through a UserForm in Excel. The first time I run it works perfectly, the problem arises when I save it and try to open it again. It gives me a message of corrupted file and erases the VBA code. I...
  3. G

    Using Excel Column in SQL queries through Power Query

    Hello. I am not sure if this is the right group for this question. Anyway, I was wondering if it's possible to run SQL query, using a column from an excel as parameter, on an excel file. for example: Select * from (excel column) a inner join (Sql.table.column) b on a.column1 =b.column2. I want...
  4. G

    SQL Query Failing with Run-time error '3704: Operation is not allowed when the object is closed.

    I have a connection and query I need run against a MS SQL Server from EXCEL using VBA. Here is the code. Dim objMyConn As ADODB.connection Dim objMyCmd As ADODB.Command Dim objMyRecordset As ADODB.Recordset Dim iCols As Integer Dim results As ADODB.Recordset...
  5. K

    Return the latest date

    I have an import from SQL that gives me a Run Date Time column. I am trying to return the latest (most recent) value, so I can see at a glance what it is. I have tried an INT(A2) or Max(A2:A14) and I am getting errors in return. Thanks in advance for your suggestions...
  6. K

    Excel [DataSource.Error] Microsoft SQL:

    Hello Everyone, I am running into an error when uploading SQL in Excel from database. The message is below. How do I get around this issue with constraint or index? I have done a lot of research but I am stuck. I am not sure what I am missing at this point and I am seeking your help. Error...
  7. B

    Parameter with multiple values SQL

    Does anyone know if it's possible to have a parameter with multiple values in a SQL query? I am working on excel with a recordset and the parameter is in the cell B4, but I would like to give the user the option to add more than one value as a filter, using then a comma to separate the values...
  8. T

    Code Improvement - SQL Statement Update SharePoint List

    Hello experts, I have the code below which updates some columns of SharePoint list, based on excel values, through its ID in a loop, however if I have hundreds of entries it take some time intil updates, so I would like to know with you can help with improvements in the code. It uses SQL...
  9. M

    Power Query - Change SQL Datasource Parameter with Multiple Values

    Hi All, I've been stuck with this problem for over week - watched and tried numerous methods to no avail. Hoping anyone can help me with this. I connected a dataset from Microsoft SQL Server to an excel spreadsheet through Power Query. The problem is, the dataset is so large hence I have to...
  10. A

    SQL functions editting

    By god almighty, i haven't found a single way to edit this function.... Its called from SQL through VBA, when you open SQL in excel it is defined as a formula, but clicking on it only allows me to fill specific parameters. Trying to edit on query it only opens a function that calls that very...
  11. D

    ROOKIE here with (hopefully) simple request

    First off I apologize for the lack of detail, this is for work so I cant give exact info on variables etc. Also I am new to PowerQuery. I have established my ODBC connection (=Odbc.Query("dsn=private", "Select#(lf)variable1, ...") so: (Select x, y From z Where x in ('')) and I would like to...
  12. B

    VBA to Populate Excel Userform Treeview from MS SQL

    I can not seem to figure this out, can someone assist please. I have a userfrom in excel that has a treeview, I am trying to populate the treeview with a recordset from ms sql using ADO. I have managed to populate the Parent Nodes but have not been able to figure out how to populate the child...
  13. B

    Dynamically importing data into excel from a Database

    Hi, I have a spreadsheet with 40k rows or so which is added onto every week. Each row has an Employee ID, Employee’s Shift Number and Hours worked column (see attached image for sample data). The problem is that I have the work out the Hours worked from a database to which I have an ODBC link...
  14. O

    Cumulative Sum based on Invoice Number Column

    I have a database where I have invoices like customer charges (Invoice_Number, Created_Date, Amount) and invoice accounts receivable charge amount (invoice_Number, Created_Date). I want to get the sum of invoices total amount per customer with "Invoice_Number" going back 1 month back. I have...
  15. B

    Insert into sql server from excel userform

    I am using excel Userform and MS Server. I have a Userform (OrderFrm) that I populate 2 multi column combo boxes with lists from my database (Customers & Items) there is also text box for inputting quantity and a text box that is populated with Price on a change event of the Items combo box. I...
  16. H

    SQL Updates on OneDrive Workbook

    Hi there, When trying to run a sql update statement (on a sheet) that works fine locally, I get 'Cannot update. Database or object is read-only' when I move it on to OneDrive. My assumption is that it's because of co-authoring. My connection string is...
  17. P

    Running a long SQL query via ADODB

    I am trying to run an SQL query via VBA and seem to be running into an issue with the length of my query. The query is connecting to a SQL Server table, and is to select particular fields from the table. The length of the string with the code is ~2100 chars (the field list is built via a loop...
  18. J

    How do I search multiple item codes in a cell reference?

    Goal: Search a dynamic list of items to plug into a sales order query from a cell reference. I know how to reference a cell reference for only one item that can change in a cell reference, but I now need to know how to reference a cell or cells that have multiple items and filter to just those...
  19. bobsan42

    PowerQuery performance discussion

    Dear All, Especially the ones more familiar with M code, databases and data analysis. I admit that PowerQuery and M code seem to be quite a versatile tool, but I am puzzled by its low performance. So I wonder - is it me doing something wrong or it's just the way it is. Is there anything I can do...
  20. M

    VBA to update SQL table

    Hi All I am new to Excel /VBA and have been asked to create a excel workbook that takes data from a SQL table and the user then updates the spreadsheet and then clicks a button to update the table. I seem to be unable to get the update to work and to me its a very simple task! (Or Should be)...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back