I'm trying to get the data from this site
http://www.cmegroup.com/clearport/pr...=1&PageStart=1
When going through the Web Query in Excel and pulling data with the above address. I get the option to pull the data from the products table..the table with the code/name/product group/sub group..etc etc...
But when I launch the query, it doesn't pull the table data..I get everything but the product list.
I have built a model where I have various inputs and various outputs. I have managed to set up the model so that whenever i hit the 'add to portfolio' button, a new row is added putting in the details from the inputs to a new row (beginning in row 25). The problem I am having is that I cannot seem to get the output results (cells I12:I17) to be included as well. I have attached my excel file.
View 4 Replies View RelatedI have a Workbook containing a list of records. I'd like to create some code to
1) Get the user to input their initials when the workbook opens, and capture the response
2) Clear any filters currently applied
3) Apply filters to a non-continous range of columns
4) Apply the input response as the criteria to one of the columns, and 'blanks' to another 2 columns
Private Sub Workbook_Open()
Static varUser As String
'sets varUser variable as String & stores it
varUser = InputBox(Prompt:="Enter Intials (leave blank to view all)", Title:="Login")
'Activates input box to capture value
Dim ContractsSheet As Range
Set ContractsSheet = Worksheets("Contracts").Range("A1:AA999")
ContractsSheet.EntireRow.Hidden = False....................
I need to query table contents where some general items are met, and item id's are not contained in an array.
Example:
Code:
SELECT LOOKUP.*, LOOKUP.Var FROM LOOKUP WHERE ((LOOKUP.Name)="test" Or (LOOKUP.Name)="test2" AND ((LOOKUP.Type)=3) AND ((LOOKUP.Var) ("array", "of", "items")))
I need to know how to add an array to a query since I can't hard-code the list each time, and the array can be a different size each time, so using array variables doesn't make sense here.
I have a workbook that is connected to a MySQL DB.
I want to get all the months organised in columns then the Amounts of those months underneath the specific month. I can only do one at the moment in this select query but do not know how to go forward by creating the other month and their data?
My code looks like this:
Private Sub cmdGO_Click()
Dim conn As ADODB.Connection
Dim dbRecset As ADODB.Recordset
Dim sSQL As String
Dim l As Long, l2 As Long
Dim Last_Row As Long
How do I do a query from Excel table? I know that from sheet it works like SELECT * FROM [SheetName$], but how about a table?
View 9 Replies View Relatedattached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies View RelatedI'm trying to update a query table in excel, but it keeps giving me the error 1004
Worksheets("Data-All").Range("A4").QueryTable.Refresh BackgroundQuery:=False
Cell a4 is in my query table.
Our company's accounting system is physically split into 2 company databases, CompanyA and CompanyB. I'm trying to write a query which will pull the customer number and the accounts receivable balances from both companies into a single Excel table, for any customer with a total balance 0.
I have created the following query, which doesn't generate any errors, but is only successfully importing the data from the first company.
The CompanyA DSN is specified in the query ...
the attachment is a sample spreadsheet of what i am trying to do. I would like to create a pivot table so that for each site I can list all of the organic suites individually. Then show a count of only 1 for each site, even when there are several counts for each site. And then for this to be related to a cost of each of the suites so that I can get a total cost for each site.
eg:
site name
organic suites suite cost
o1 x
o2 y
o3 z
Total A
etc
I am having a excel vba code that copies tab from different excel file and then i want to use in sql query. It is always giving me error saying that "The Microsoft Access database engine could not find the object 'rahul'. Make sure the object exists and that you spell its name and the path name correctly. If 'rahul' is not a local object, check your network connection or contact the server administrator."
Though, when i rerun the macro after reopening the excel file, it is working fine. Seems like tabs dynamically created cant be used. Is their any way to get it done at runtime? I want to delete new created tab once data from new tab is inserted to required tab.
my macro calls first copyAllSheets(filename) and then writeSheet().
Code:
Private Function copyAllSheets(ByVal fname)
Dim destSheetCount As Integer
Const FUNCNAME = "copyAllSheets"
On Error GoTo ErrorHandler
Set wb = Workbooks.Open(fname)
destSheetCount = ThisWorkbook.Worksheets.Count + 1
wb.Worksheets("Buyer").Copy after:=ThisWorkbook.Sheets(1)
ThisWorkbook.Worksheets(destSheetCount).Name = "impBuyer"
[code]........
This is part of code so probably it wont be easy to understand the flow. but mainly i want to know how can i have newly created tab as table_name.
I've used this code prior and it worked fine in Excel 2007, now I'm having issues with it. Originally every Pivottable & Query pointed to a different CSV file. Now I have two pivottables built off a Excel worksheet that is built from a Query. This allows me to do some complex lookups that I couldn't do with just the Pivottable and MS Query alone.
Here's the code that's failing out: Any problems with it in Excel 2007?
Private Sub Update_All_Data_Click()
Dim pvt As PivotTable
Dim ws As Worksheet
Dim qt As QueryTable
mytitle = "This will refresh all data for validation, are you sure?"
Msg = "The Refresh process takes about 5 minutes, are you sure you want to continue?"
Response = MsgBox(Msg, vbExclamation + vbYesNo, mytitle)
Select Case Response
Case Is = vbYes
' Do Nothing, continue with program
Case Is = vbNo
Worksheets("instructions").Range("a1").Select
End
End Select
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.BackgroundQuery = True
qt.Refresh
Next qt
Next ws
For Each ws In ActiveWorkbook.Worksheets
For Each pvt In ws.PivotTables
pvt.RefreshTable
Next pvt
Next ws
mytitle = "Confirmation of data refresh"
Msg = "The data has been refreshed"
Response = MsgBox(Msg, vbExclamation, mytitle)
End Sub
I use web query to get some range of data. This data is refresing and changing on some time (i set query refresh on 1 minute). So, i'd like to fetch this data and make the table with all data (new data on eg. every 1 minute). How to do this?
I use Excel 2003.
I run a web query to populate a table in my spreadsheet. The data is dynamic - and on some days, I will get a couple of extra rows in the table (its weather data, so e.g. when its raining, they will forecast an extra row of how many mm, etc...)
To give a simple example, right now I use Hlookup to locate the value of F3 (the date) in my dynamic range of B26:G26 and step down 3 rows, where my needed temperature values are for that corresponding day. This works great like this already: =HLOOKUP(F3,B26:G29,3,FALSE)
The big issue is...what happens if my target values are not always 3 rows down? If the query refreshes with any extra rows, this reference is shifted. (Well, my starting "Anchor" is shifted)
I have to import data into Excel from Access (yes, I have to import it to Excel and cannot use Access exclusively). When I import; however, the formatting does not come over correctly, most especially losing the formatting of numbers. I need one of two solutions:
1.) How to get the formatting to move over correctly?
2.) Or how I can save the formatting of a table, then be able to apply it as a "saved" format? The biggest thing is that the numbers will changed the number of decimal places and/or change from general numbers when I want to see them as currency.
The top table shows how i want this formatted, the bottom shows how it is importing.
I have an SQL Server that has a query (a view stored on the server) which takes roughly 10-20 seconds to execute. This execution time is the same in Excel 2007 if I import data to worksheet/pivottable. It is also the same in Excel 2003 while importing data into a worksheet. It returns about 350 rows.
However, if I try to make a pivot table in 2003 based on this query, it is much slower. I can set it up fine, but refreshing this pivot table takes at least 10-20x as long to complete, in the order of 5-20 minutes, to sometimes taking so long that I give up and kill it by hitting escape a bunch.
It's important to note that I am forced to used Microsoft Query to set up a pivot table in Excel 2003. There is no other way, I believe.
While it is running, Excel just sits there saying "Waiting for data to be returned from Microsoft Query".
Here is the weirdest part. Once the refresh starts, Microsoft Query loads in the background, you can see it on the taskbar. I believe that is expected behavior, but if I click on MS Query and view its datatable, I can see all the data is sitting there.
So what is the hold up? MS Query has the data. Excel is waiting for it to be returned. What is going on? And remember from above, if I do a simple import data to worksheet, MS Query is not used and everything is fast.
So my question is does anybody have any tips/advice on this issue? I need a way to not use MS Query for a pivottable's data source or I need a way to make MS Query not freeze up. I don't get why MS Query needs to be involved in the first place since I am not doing any parameters or so forth.
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.
I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.
However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed).
Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it?
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
I've used the code below and it's worked to stop Update Links message, but i still need one tip to avoid continue message that shows up after Update Links massage:
Private Sub Workbook_Open()
Application.AskToUpdateLinks = False
Sheets("Feb").Activate
Range("A200:I400").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
End Sub
In pivot table field Setting > Advanced > page field option, the selection for "the query and external data source is disabled. How do i enable it.
View 9 Replies View RelatedI have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
View 2 Replies View Relatedi type into any cell in row 1 i need the time at that moment to be captured and shown in cell "B". When i type into row 2, the time which i entered the data must be captured into row 2 cell "B".
View 5 Replies View RelatedI have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
. A B C D E
1 JanFebMarApr
2 Galle JGFGMGAG
3 Matara JMFMMMAM
4 KalutaraJKFKMKAK
5 PanaduraJPFPMPAP
6 ColomboJCFCMCAC
7
8 City Month
9 Kalutara Mar MK
When I type 'Kalutara' in A9 and 'Mar' in B9 I should get the answer as MK in cell E9
'Colombo' " " 'Feb' B9 " " " FC in cell E9 by refering the above table
can we use MATCH & INDEX or VLOOKUP & HLOOKUP
What is the formula I should write in cell E9 to get this done?
I have code to Add a row to my Table ...
[Code] .....
Now, how do I select the entire Table row below ActiveCell?
I have a table with a lot of fields ( Date, Type, Name, Status ...etc). I want to make 2 textbuttons to put the Date intervals and a combo box to put the Type possibilities. After i put those condition i want to press a button to show only rows (from initial table)who are between data i entered and with kind of type i select.
View 1 Replies View RelatedI have many tables in separate sheet but in the same workbook. I need in sheet1 to call the specific table based on the value in D3 which it has the table name, how to do this?
View 9 Replies View Related
