Macro Web Query Return Blank
Dec 19, 2006
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
View 3 Replies
ADVERTISEMENT
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Apr 19, 2006
I have been using the following formula to return a value where a % is used, is there anyway i can make the sum shorter.
=SUMIF(Q10,'Price Calculator'!$A$5,'Price Calculator'!B5)+SUMIF(Q10,'Price Calculator'!$A$6,'Price Calculator'!B6)+SUMIF(Q10,'Price Calculator'!$A$7,'Price Calculator'!B7)+SUMIF(Q10,'Price Calculator'!$A$8,'Price Calculator'!B8)+SUMIF(Q10,'Price Calculator'!$A$9,'Price Calculator'!B9)+SUMIF(Q10,'Price Calculator'!$A$10,'Price Calculator'!B10)+SUMIF(Q10,'Price Calculator'!$A$11,'Price Calculator'!B11)+SUMIF(Q10,'Price Calculator'!$A$12,'Price Calculator'!B12)+SUMIF(Q10,'Price Calculator'!$A$13,'Price Calculator'!B13)+SUMIF(Q10,'Price Calculator'!$A$14,'Price Calculator'!B14)+SUMIF(Q10,'Price Calculator'!$A$15,'Price Calculator'!B15)+SUMIF(Q10,'Price Calculator'!$A$16,'Price Calculator'!B16)+SUMIF(Q10,'Price Calculator'!$A$17,'Price Calculator'!B17)+SUMIF(Q10,'Price Calculator'!$A$18,'Price Calculator'!B18)+SUMIF(Q10,'Price Calculator'!$A$19,'Price Calculator'!B19)+SUMIF(Q10,'Price Calculator'!$A$20,'Price Calculator'!B20)+SUMIF(Q10,'Price Calculator'!$A$21,'Price Calculator'!$B$21)
Seems like a Pain to be using something this big, just cant seem to get it to work when i try to shorten it
View 2 Replies
View Related
Jun 23, 2013
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.
View 2 Replies
View Related
Mar 27, 2014
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies
View Related
Mar 15, 2007
I am using the attached code however when my database query does not return any data I dont want the Dim function to perform.
Sub macro3()
Worksheets("Data 1"). Range("I3:BU65536").ClearContents
'declare a variable to hold date
Dim strDate As String
strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
Sheets("Data 1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _...........................
View 4 Replies
View Related
Aug 20, 2009
I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.
View 3 Replies
View Related
Apr 3, 2014
in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 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
Jan 22, 2014
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??
View 3 Replies
View Related
Jun 1, 2008
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View 4 Replies
View Related
Jun 25, 2009
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies
View Related
Apr 24, 2013
Using one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
View 3 Replies
View Related
Apr 26, 2009
Clolumn D will always have a positive value, Column N sometimes will be zero.
using the formula =SUM(N6-D6) returns a negative value if 'N' is zero. I would like to return a blank cell or '0' if 'N' is zero.
View 14 Replies
View Related
Mar 6, 2007
I'm using this macro in order to bring some data to two cells.. it's currently working great...
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M" _
, Destination:=Range("A1"))
.Name = "fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
However, as you can see from the text in bold i'm currently telling the url instead of bringing it from a cell with an url. I want the macro to be able to select the url from a cell.
I'm trying to use this solution, but i keep getting a Run-time erro ´5´:
Invalid procedure call or argument.
connstring = Range("GUS8301!B3").Value
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"))
View 9 Replies
View Related
Jul 4, 2008
i have recorded a macro wherein i need to copy paste some data in my database everyday.
now the problem is tht the macro doesnot catch the last row.
for instance if i paste data today in A2:A10
tomorrow it should automatically paste data starting from A11.
the range of data tht i copy is not fixed.
This is a part of macro...
Range("A1").Select
Selection.End(xlDown).Select (This takes me to the last record on that row)
is there anyway i can copy the data in next column?
'Range("A11").Select (This will work only for the first time when i run the macro)
but if i use the same one tomorrow
Range("A1").Select
Selection.End(xlDown).Select This will take me to the last record but..
it will again go to
'Range("A11").Select
and then paste records there
View 9 Replies
View Related
Jan 4, 2009
if cell a24 has nothing in it how can i make sure a77 is blank at the moment it returns a zero
View 2 Replies
View Related
Mar 5, 2009
=IF(Q20+R20+S20>0,Q20+R20+S20,"")
V20
=SUM(T20*O20)
V20 gives me #VALUE
How can I have V20 blank if T21 is blank?
View 2 Replies
View Related
Feb 26, 2010
Shortened for example;
I have created a drop down list with gas, groceries and travel as the categories.
column a is labeled expenses and formatted as currency.
column b is labeled category with the drop down list in each cell.
column d contains each item, gas, groceries and travel on separate rows.
column e will contain the total for each item in column d using "sumifs formula".
If there are no travel expenses, instead of returning $0.00 I want the cell to be blank.
I know how to do this in a simple sum function but sumifs are sumthin else.
I have tried using <> for not equal to and everything else I can think of and I usually get a VALUE error.
how do I get it to be blank instead of $0.00???
View 8 Replies
View Related
Jan 20, 2008
I am using the formula : =VLOOKUP(G6,$R$8:$S$16,2) which is fine however i want it to return the value 0 if G6 is blank.
View 9 Replies
View Related
Jul 23, 2008
I am using Excel 2000
I am using the "IF" formula to pull in data into a forms section. I then save the forms section as a CSV file.
When the IF statement returns the "false" value, it is looking to pull over a "blank" cell. When that happems, a "0" pulls over. I tried to put a "" in the blank cell, but while the output of the formulaa looks "Blank" when I convert the tab to CSV, the output is a quote mark ".
I need the formula to display a real BLANK cell or something that acts like a blank cell
View 9 Replies
View Related
Sep 11, 2008
I finished mty project which shows how to return the value of the first non-blank cell in an array. That's great, but I now need to find the next value, and so on. In the example, I'd need to get "BBB" to come out of the formula.
My list has other blanks inserted throughout the array, so it would more closely resemble:
A1
AAA
BBB
CCC
Any ideas?
View 9 Replies
View Related
Oct 5, 2007
I'm trying to do several things. I have 6 columns (A:F) with column headers and 12 rows of data and not every cell has a number as its content. Some cells are blank. I have attached my file.
Using Row 2 as my example:
1.) I want to return the contents of the FIRST non-blank cell in the range (A2:F2) in cell I2.
2.) If I2 is not blank, then I want to return the column header (text) which corresponds to the resulting number content of cell I2.
3.) I want to return the contents of NEXT non-blank cell in A2:F2 in cell K2.
4.) If K2 is not blank, then return the column header (text) which corresponds to the resulting number of K2.
I will then repeat the 3rd & 4th scenarios in columns L thru S.
What I’m trying to do is place all non-blank data up in the first-foremost columns (H thru S) as I possibly can so I can use the data from these new columns (H thru S) as my merge fields in a Word document.
View 5 Replies
View Related
Oct 22, 2007
I worked on this macro for about 2 hours, with only partial success.
I have credit card data (will attached next post) sorted by tender type. I recorded teh subtotal feature, after selecting the data. I used teh go to, special, last cell, feature to get the end cell for selection, and also set the print area to this range. I did it in relative mode.
the macro worked, but when I also tried to insert three rows above the "grand total" cell, then insert a sub total of just master card adn visa, it in one of these added rows didn't work upon playback.
also - Can I lock the position of my macro buttons? IN properties it's set to locked but they still move when teh data changes. Can i put them on teh tool bars?
View 12 Replies
View Related
Dec 15, 2006
So I have some data that I'd like to extract from some financial sites that I'm a member of (one in particular is investors.com). I try and pull stock info from the site, but I have to manually initiate a new web query, go to the website, login, then cancel the web query for the macro on my spreadsheet to start pulling information. What I'd like to do is have two cells referencing my user id and password, and then click a button that will goto the website from excel, login, and allow the extraction of financial info to occur.
View 6 Replies
View Related
Mar 12, 2007
I have a Excel database query which of which i import into Sheet 1. On a daily basis I need to edit this query and change a critea field to yersterdays date. Is there a way in which I could run a macro to change this query for yesterdays date without having to manually go into the query?
I have tried to run the macro, however I can only run this if I have a specific date in my code e.g. 11/03/2007 and not a formula to show yesterdays date.
View 9 Replies
View Related
Apr 2, 2007
I want to write some VBA that retrieves data from a lotus notes database into excel. I will then have some other code that manipulates the data for the user. I have managed to do this with MS Query manually. However when I start to put into VBA my problems start. As I do not know Sql I use the macro record function. This worksso far so good. The problems seem to start when I re-run the macro and try to save the workbook. Excel just sits there with the CPU running at 99%. I left the pc for an hour and it was still in the same condition with the status bar showing save.
Sub Macro4()
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DSN=premax;Database=Premaxse000273.nsf;Server=local;UserName=John Cross/bsp;EncryptMaxSubquery=20;MaxStmtLen=4096;MaxRels=20;M" _
), Array( _
"axVarcharLen=1024;KeepTempIdx=1;MaxLongVarcharLen=1024;ShowImplicitFlds=0;MapSpecialChars=1;ThreadTimeout=60;" _ .............................
View 4 Replies
View Related
Nov 26, 2012
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.
View 3 Replies
View Related
Mar 29, 2007
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
View 2 Replies
View Related
Jun 23, 2014
Column B has values 1 2 or 3
I don't care about 2 or 3
If Cell B2=1 and cell C2 is blank, return the word "Fix"
If C2 has a value, leave blank
If B2= 2 or 3, leave blank
Then, as I "fix" these guys, it involvse inserting or deleting cells. which seems to screw up the formula
View 3 Replies
View Related
