I am trying to get data in excel sheet using web query from the following web link:
But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.
I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.
I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.
I use Office 2010. I have an Access database that goes out and runs various queries from our systems of record. It then takes the data and dumps it into an Excel file. I have taken the info and created a file the grabs the raw data, converts it into Pivots and then Charts. All info is automatically updated however the size will change depending on what department is quering the data. It is really all done with a click of a button. I have tried to merely uncheck the "(blank)" but it comes back everytime the report is ran.
How can I keep it from returning??
I have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.
If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.
Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?
I have this web query to retrieve Stock Market data for individual scrips which used to work fine till 2 weeks ago:
[url]
Since then all it returns is the text in the tables. Numbers eg dates and stock prices come as blanks.
These show up alright in the browser (Mozilla as well IE).
Some other tables at this site plugged into query return complete data. eg [url]
I tried many different scrips with same results.
Since it was working fine till 2 weeks ago AND other tables at this site are working fine, I suppose the site changed something.
I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.
I pass in the query name and the sheet name into qryName and sheetName name.
Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
[Code].....
this example runs a named query in Access but it does not return the header row, just the data.
Code:
Sub RunAccessStoredQuery()
Dim objMyConn As New ADODB.Connection
Dim objMyRecordSet As New ADODB.Recordset
Dim strSQL As String
[Code].....
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
I 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.
I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:
4a - highest
4b
4c
3a
3b
3c
2a
2b
2c - lowest
There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.
Jacob2c
Mary3b
John2a
Sally2b
Sarah2b
Joe3a
Bill3a
If I manually sort the above data into descending order of national curriculum level it will look like this:
Joe3a
Bill3a
Mary3b
John2a
Sally2b
Sarah2b
Jacob2c
but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:
Mary3b
Joe3a
Bill3a
Jacob2c
Sally2b
Sarah2b
John2a
I know there must be an easy way involving creating a custom list, but I don't know how to do it.
Problem 2
A related query involves finding a way to get excel to complete the data in the Sub levels progress required column below:
NameLevelTargetSub levels progress required
Mary3b4a
Joe3a4a
Bill3a4a
Jacob2c2a
Sally2b3c
Sarah2b3c
John2a3a
At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.
I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.
This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.
[URL] ...
I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.
View 1 Replies View RelatedIm using a query to connect to a SQL server and return data into Excel. I can query and return the data in Microsoft Query editor but when I attempt to return the data to Excel in a table, it just says the name of the connection in cell A1. If I attempt to return it into a pivot table a get and "Problems obtaining data" notification.
View 1 Replies View RelatedBasicly i have a list of information and i need to be able to enter a start data in one cell and a end date in a different cell and then return all the dates between them.
View 1 Replies View RelatedI am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
I have an Access table which has following fields:
ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls
I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.
I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.
SQL code below:
Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;
I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.
I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.
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!
Below formula matches two criteria
=SUMPRODUCT(('[Value.xlsx]test!$B$5:$B$50=$B5)*('[Value.xlsx]test'!$C$5:$C$50=$C5)*('[Value,xlsx]test'!D$5:D$50)).
What it does is:
Matching B5 value in range B5:B10 of tab "test" in worksheet "Value"
Matching C5 value in range C5:C10 of tab "test" in worksheet "Value"
AND capturing the matching value in range D5:D50.
The problem is that i am getting 0 if the matching value is a blank or 0.
I want the formula to return nothing (i.e. blank) when the matching value is blank and 0 only when the matching value is actually 0.
I'm linking one sheet in a workbook to another. The formula is simple,
='Link 1'!E1
If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.
I have a current formula using SUM formulas obviously in the answer cell the answer is returning 0 due to no data inputed, how do i make the cell show empty.
eg. =SUM(A1*A3) returns answer 0 need cell to be blank.
I am using the following formula to return an "F" in the cell. If there is no "F" it just returns "#N/A". Is it possible to have the cell blank instead of the #N/A? If so how do I do it?
=INDEX(E1:E99,MATCH(1,(A1:A99=Selections!J5)*(E1:E99="F"),0))
and then ctrl + shift + enter
My formula is returning a 0 when true instead of being blank as specified. If I designate a value other than "blank" (i.e. "A Word") it still returns a 0. What am I doing wrong?
=IF(AND(CBAuditType="PHYSICAL",TxtInsuredName=""),"",TxtInsuredName)
The actual link from a list on the left side of that web page is "Printable List".
I can't work out why all other web pages work fine with;
Get External Data/Web Query, but this particular web page won't work.
Sub Test_A()
Sheets("test1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://acttab.com.au/interbet/racing?type=venues", Destination:= _
Range("A3"))
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub
I want to be able to return the value of the last non-blank cell in the horizontal range L3:BF3. This would need to account for any blank cells that might occur within the range. Those 'blank' cells would contain formulae and the non-blank cells would contain GCSE grades from A* down to U or National Curriculum levels in the format nx (e.g. 3a). I would need to be able to copy the formula down to other rows. The reason I'm not keen on an array formula is because they can slow things down, as I understand it.
I'm afraid I don't have a sample worksheet as this is all, for now at least, an emerging plan in my head. I should probably add that the grades will not necessarily increase in value in the range, so we're not looking for the largest.
If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?
This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)
I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))
Referenced cells returning zeros: can these be made blank or string data?
But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?
My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!$D$6:$K$100,3,FALSE)
I am trying to trigger a formula if a cell (B2) is populated (with anything) but if it is blank for the destination cell to remain blank.
The formula I am trying to trigger is =IF(G18=0,100,100-(100/(1+H18))).
