Create SQL Query In VBA And Display Result As Table In Worksheet
Feb 3, 2010
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.
Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:
No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20
View 9 Replies
ADVERTISEMENT
Jul 2, 2010
I created a union query in Access to join two tables (Projections and Actual Sales). The query produces the results I want. I need to create a pivot table in Excel using the union query as the source. When I pull up the data import function in Excel, the union query does not appear. Do I need to do something else? I have tried to create a select query where I select all from the union query and I can find that fine.
When I use this query to create the pivot table the results end up all zeros when I try to sum the values. It creates some crazy results when I show it as count of also.
I can provide the data in either the Access database or Excel spreadsheet.
View 4 Replies
View Related
Oct 16, 2008
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
View 2 Replies
View Related
Sep 2, 2013
The new worksheet is created to the left of the existing source worksheet.
View 2 Replies
View Related
Sep 6, 2008
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View 9 Replies
View Related
Jun 26, 2014
i want to be able to display the underlying data in a pivot table by clicking on the cell within the pivot table
ie, normally if you double click a cell within a pivot table, it will generate a new tab, and the data will be displayed in the newly created tab,
i want to be able to click a cell within my pivot table, and have the underlying data display beneath the pivot table..
View 1 Replies
View Related
Jun 20, 2012
I have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from
Code:
Sub SalPiv()
'
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick
[Code].....
View 1 Replies
View Related
Feb 25, 2013
Can I validate data based on a list in another workbook? is what I mean to say.
View 2 Replies
View Related
Oct 9, 2013
I am trying to create a table to display name of employees and their relevant rota. Attached is a spreadsheet as an example of one rota I have.
The rota at the top gives the 3 week rotation, the table underneath is the names and which week they start on and what week.
Trying to create a table at the bottom that when I put someones name in and change the date it will populate his rota for that day/week.
I have loads of different rotation plans and need to have them all together when I enter a date.
View 1 Replies
View Related
Jan 9, 2013
I am working with data and I am trying to identify people that have moved departments using the following criteria, a person are classed as a mover if their:-
Manager AND Dept ID have changed
OR Job Code has changed
OR Status has changed
I have been trying to come up with a formula but just can't get one to work?
View 6 Replies
View Related
Sep 12, 2013
I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.
The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.
*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.
Code:
Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection
[Code].....
View 2 Replies
View Related
Mar 25, 2013
Why this sql query doesn't return values? My excel version is 2007.
Code:
if object_id( 'TEMPDB..#TMPDOC') is not null
begin
drop table #TMPDOC
end
create table #TMPDOC (Code varchar(5) NOT NULL)
[Code] ...........
View 6 Replies
View Related
Oct 19, 2006
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View 8 Replies
View Related
May 22, 2014
When I enter a number, say 2456 in this textbox why does it display "$2.45" instead of "$2,456". I've tried fiddling with the ###'s and .'s but it just gets worse.
[Code] ....
View 4 Replies
View Related
May 2, 2014
I've got results in row 3 - 16 386 and sometimes these results are exactly the same in some rows. What I now would need is row AB to display only one of each result and row AH to display how many percentages of the time this particular result appears.
View 14 Replies
View Related
Feb 10, 2009
I have a count for each site for certain cloumn headers.
But i want to collate these so that if there is a 1 in the column the it will output it with the column header. But there are 10 column headers and I would like to get a result that has all the columns with 1 in.
eg:
a b c d e f g h i j k l m n o p q (Organic Suites) (Inorganic Suites)
sitea 1 0 1 0 1 1 0 1 0 1 1 1 0 0 0 0 (O1 O3 O5 O6 O8 OS) (I1 I2)
View 6 Replies
View Related
Jan 15, 2010
I want to sum a column and then display the result but in the same cell put some text:-.....
View 4 Replies
View Related
Sep 26, 2007
I have a formula in A1 that I would like to execute from A2. I want the A1 to display the formula (so no "="), but I would like cell A2 to execute the formula in A1.
If A1 contains: "SUM(1+2)", how can I get A2 to display "3" without reproducing the formula?
A1: SUM(1+2)
A2: ????
I suppose I'm looking for something like this:
A2: ==A1
But, of course, that doesn't work.
If I do this it gets close:
A2: ="="&A1
But that returns "=SUM(1+2)" instead of executing the formula.
View 9 Replies
View Related
Dec 28, 2012
I need to create a web query.
I have lot of post codes which needs to be checked in a web page and the output column needs to be updated in the spreadsheet.
Link: - Find UK Postcodes Inside a Radius
for example: - The post code WD18 1TB will be there in Excel Sheet this has to be copied and pasted in the link in column
Step 2 : Click on map OR Place radius by location name or postcode
and Step 1 : Radius should be updated as 10 and then the Draw Radius needs to be clicked. Then the output needs to be copied (in this case the output is: AL2,HA1,HA2,HA3,HA4,HA5,HA6,HA7,HP3,UB9,WD1,WD17,WD18,WD19,WD2,WD23,WD24,WD25,WD3,WD4,WD5,WD6,WD7) in our spreadsheet in the next sheet.
View 1 Replies
View Related
Jan 6, 2009
There is an autofilter applied to worksheet A, I would like to transfer only the autofiltered range from worksheet A to worksheet B, a clear worksheet B statement would also help.
View 9 Replies
View Related
Dec 6, 2012
I have 4 columns in a worksheet.
A B C D
number type date name
I am using an input box to enter the value of column D. After the value of column D is entered, I want a message box that displays the corresponding data in column A and B. with the vba coding?
View 9 Replies
View Related
Sep 13, 2013
creating a .Find code to search a range on a sheet and then display each result seperately in a msgbox. An inputbox will be the value .find searches the range for.
the msgbox needs to be a vbyesno
VB:
'SEARCH CODE
Dim myItem As String, myRNG As Range, NewLoc As String
Dim Found As Range [code].....
View 7 Replies
View Related
Mar 6, 2014
I am using the below formula to find the latest date (column N) based on fund (column G) and Vendor (column O) reference. How can I find (column I) where the balance of that payment is? I know there are formulas like INDIRECT RC[-1] but how to add them to my formula.
{=MAX(IF(Payments!G:G=B4,IF(Payments!O:O=C4,Payments!N:N)))}
View 1 Replies
View Related
Dec 8, 2009
Hopefully you will be able to help again. Is it possible to do a vlookup that references data on other tabs within the worksheet so that the result of the formula is in the cell not the formula.
So if my vlookup was =vlookup(a2,$a$1:$b$12,3,0) and the result was john smith i want just john smith in the cell. I know about copy and paste values but i was looking for a more automatic way. One that doesn't need intervention.
View 12 Replies
View Related
Feb 11, 2009
I have a formula on cell A1, if the result of this: =SUM(COUNTIF(F:F;M40);COUNTIF(M:M;M40)) is equal to 1, I want the cell to display the word OK.
I have tryed this but it didn't work:
=IF((SUM(COUNTIF(F:F;M40);COUNTIF(M:M;M40)))=1;"ok";"error")
View 5 Replies
View Related
Sep 26, 2009
I have 15*2,14*2,14.5 in cell A1 and would like this to be computed and its result (i.e 72.5) displayed in cell A2. See below for clearer picture:
A1 A2
15*2,14*2,14.5 72.5
View 8 Replies
View Related
Nov 9, 2006
i have a routine which loops through a range looking for past dates and when it finds one display the result in a message box in my testbook i only use 25 rows but it gets annoying having to click ok for every find....is it possible to collect all results and display them in the message box at one time?
Private Sub Workbook_Open()
Dim Mycell
Dim Rng
Set Rng = Sheets("Sheet1").Range("B1:B25")
For Each Mycell In Rng
If Mycell.Value < Date Then
MsgBox Mycell.Offset(0, -1).Value & " Is Overdue By " & Date - Mycell.Value & " Days, Take Action Now!", vbOKOnly, "Tasks Overdue"
End If
Next Mycell
End Sub
View 4 Replies
View Related
Nov 15, 2006
Sub startup
Dim numwords As Integer
numwords = WorksheetFunction. CountA("H5:H64")
Range("H1").Select
Selection.FormulaR1C1 = numwords
I have a quick question regarding this simple code thats been drviing me nuts. There x distinct pieces of Data in the range speciifed, but when i run this code, the reply posted in cell H1 is 1, instead of x.
Eventually the code will be built to check if the result is odd or even and then add an entry into the first blank cell if the result is odd.
View 4 Replies
View Related
Jul 7, 2014
I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.
View 1 Replies
View Related
Jun 5, 2014
I am trying to find a way to display text that is the product of a concatenate function in a defined cell, but not to have the function itself in that cell. Basically, I want to have the below function in cell A1. I want to add a command to it to take the result and display it in cell A2.
=CONCATENATE(C5,"_",'Attachment 3-A'!C9:E9,"_",IF(C5="","",IF(C6="MPL",IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Lease","Lease_Contributable",
IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Pre-Lease","Managed_Pre-Lease",
IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Non-Contributable","Managed_Non-Contributable","Error"))),
IF(VLOOKUP(C5,Sale!B:F,5,FALSE)="Assignable","Lease_Assignable","Managed_Non-Assignable"))))
The reason for this is that I need the cell to be selectable (it is generating a file name that needs to be selected and copied), but I don't want the code behind it to be seen. I can't find a way to make the cell selectable AND hide the formula from being seen when the cell is selected. This is because our people keep copying the formula rather than the resulting text.
I was hoping for something like a DISPLAYIN(target) function, but it doesn't seem to exist.
View 2 Replies
View Related
