sql

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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)...
  13. S

    Automating daily tasks using Excel/Power Query and SQL

    So I am looking at COVID stats all day and I have to prepare them for my manager. This is a multistage process and it's fairly repetive. As a result i want to automate it so i can spend my days doing more productive **** or cat videos. Either i am not bothered. It's a multi stage process and...
  14. A

    VBA Cascading Dependent Drop Down Lists does not accept values

    Hi all I have downloaded the next file. It has a userform that makes a dropdown list for each column and it filters the results (Which i belive it's very cool) The problem comes when i perform some changes to the database, in where i put some values for the last column, as you can see in the...
  15. D

    How to make a sql connection to CSV file from Excel VBA?

    I need to connect to CSV file in my downloads folder using SQL query from Excel VBA. This is the code I found from internet Sub SQLconnectCSV() Dim xlcon As ADODB.Connection Dim xlrs As ADODB.Recordset Set xlcon = New ADODB.Connection Set xlrs = New ADODB.Recordset...
  16. Z

    SQL Server query to Power Query - efficiency

    Hi, Tell me please how SQL database queries generated from MS Excel and loaded directly into Power Query works (via Data> Get Data> From Database> From SQL Server Database). Does the query generated this way run slower and overload the database more than if I wrote a SQL query by hand? Let's...
  17. R

    Disable external data connection for other users

    Hi, I’m using a simple notepad .vbs macro to refresh all spreadsheets in the folder which is scheduled to refresh twice a day by task scheduler. The data source of the report is SQL server and hence I’ll have to leave OLE DB refresh option enabled to make sure the data remains up-to-date. When a...
  18. A

    SQL remove header row from query

    Hello all, Is there a way to remove the header row on my output in a sql query? I just having to manually delete it. Thanks
  19. A

    SQL replace question

    hello all, I have this functioning SQL code: SELECT LOANMAST.BANK, LOANMAST.BRANCH, LOANMAST.LOAN_NBR, USERF.USER13A, LOANMAST.POSTDATE, LOANMAST.LOAN_AMT, LOANMAST.SHORT_NAME --USER13A must say "D" in all rows FROM LOANMAST LOANMAST LEFT OUTER JOIN BORRLIST BORRLIST ON...
  20. L

    SQL Statement - Check the earliest date as per group of records - If true , return custom text in a custom column

    Guys I have the following problem. I need to identify if the date of the record is the earliest date of the group. The idea is NOT return an aggregation table, the idea is return all the records but in a custom column I want to identify if the date of the record is the earliest of the group. If...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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
Top