How To Get Hyperlink From Web Query
Jun 24, 2014
I have some thousand web queries in an excel sheet, and now I need to get the hyerlinks from each of the query into cells in another sheet. I need this as the users in addition to looking at the data I have pulled need to access the actual webpages to look at the original report - where there are a lot of images that is not effective to put into my excel report.
What I want is to get the "name" value from the properties of the web queries, and then paste the value in a cell in another sheet. I can't seem to find any answers to how to extract the actual parameter values from a web query - but it seems like it should be possible...?
View 5 Replies
ADVERTISEMENT
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
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
Jul 2, 2009
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
View 10 Replies
View Related
Jan 30, 2014
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies
View Related
Oct 17, 2006
I am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
View 6 Replies
View Related
Jul 8, 2008
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
View 9 Replies
View Related
Apr 25, 2006
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
View 2 Replies
View Related
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
Mar 24, 2009
i import a table from the internet through web query and i reformat the cells to suit my liking, but when i refresh the data the cell formatting goes back to how it started. how do i make it so it always keeps that same cell formatting?
View 11 Replies
View Related
Jul 19, 2006
Is it possible to open microsoft query without opening Excel or Query Wizerd.
View 3 Replies
View Related
Feb 17, 2007
Is it possible to use the Web Query function in Excel on a PDF file?
View 2 Replies
View Related
Dec 8, 2008
I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?
View 2 Replies
View Related
Apr 30, 2009
I have a spreadsheet which we use to rank scores for our golf society.
At the start of our season it was decided that if anyone of the top 3 scores were tied we would separate them using a second criteria(back nine score). Any scores from position 4 down would still be tied.
From searching this forum i have managed to separate ties using the 'back nine' second criteria(using a hidden helper column) but only for all the ranking positions not just the top 3.
I have attached the file.
View 9 Replies
View Related
Oct 18, 2009
how I would be able to weed out and remove previous references to query names.
For example if I have an external data query named "qry1 by week", then remove the query, then create another one, how do I use the same query name again? Surely there's a reference to it somewhere, that I can eliminate?
View 10 Replies
View Related
Sep 16, 2007
Here it goes.
My spreadsheet is populated by data coming from MS Query, i'm entering a parameter value to display the desired data in my spreadsheet. My problem is, i have to close and open the file to have the parameter prompt so in that case i can enter the parameter value.
Is there anyway to call the parameter prompt so i will not open and close the file, its really time consuming...
If possible, i just want a command button that calls the parameter prompt.
View 9 Replies
View Related
May 30, 2009
I am having problems getting to work a web query in a VBA macro. The goal is simply to get a value retrieved from a web query into an excel cell.
The below code works well with standard html pages (like replacing the url with www.google.com). However, the page I want to retreive (a query at geonames.org that retreives an altitude value for given lattitude/longitude values) does not contain any HTML headers, but just a number in plain text. In this case the below query does not returns anything. I tried different formating options for the query but up to nownothing works.
View 3 Replies
View Related
Oct 7, 2009
What my excel sheet is doing is importing a bunch of data from a query. I have all my queries linked to buttons through macros. There are going to be 20-30 buttons that are linked to a query, and each query will produce a different amount of data(in this case, account numbers). When pressing a button linked to a query(i also have one 'Clear' button that just deletes the data, thats all), i use a macro to delete the previous query so it can import the new query into the same cell. The problem I am having is that when a button is pushed and there is no query, I am getting an error message. Here is my original code(macro), and the underlined code is where i am getting the error
View 3 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
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
Mar 29, 2007
I have a table that contains data for a region broken down into North South East and West areas.
New data is added to one spreadsheet (Region).
What I would like to do is have information for the North team automatically moved (or copied via a link) to the North sheet for printing off.
View 9 Replies
View Related
May 22, 2007
Is there any way to use an Excel cell as a parameter for MS Query.
i.e. Select fields from table where field = "cell value"
I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.
View 9 Replies
View Related
Jul 30, 2007
Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.
I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.
View 9 Replies
View Related
Sep 20, 2007
I am "Getting External Data" from our main frame. I have a column in the data named item and only want to bring in "Items" with 5 charactors. There is not a Len filter in query and I have tried to use the "Like" with some wild cards with no success.
View 9 Replies
View Related
Oct 19, 2007
I am trying to create a web query that will automatically collect information from a dynamic web page. There are five tables on the web page that can appear. However, sometimes there are only 3 or 4 tables that appear.
The web page automatically updates every 60 seconds so I would like to have a web query check the site every 55 seconds.
I can do a query and get the information but I have to do it manually.
View 9 Replies
View Related
Nov 28, 2007
Can someone tell me how to have 2 options using isnumber.
I'm using ISNUMBER(SEARCH("SUBMITTED",N156)) but I'd like to have "SUPPLY" & "SUBMITTED"
View 9 Replies
View Related
Mar 26, 2008
I have tried a couple times using MS Query to retrieve data from some rather complex Excel source files. I seem to be able to load the data into MS Query....and even add criteria and some calculated fields. However, every time I go to Return Data to the spreadsheet....it just hangs.
I'm using Excel 2003. Anyone know of any issues with doing this?
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
Aug 22, 2008
I keep getting run-time and application defined errors..
Theere are 2 sheets srctestdata and lists
after entering data in the start and end row...the macro reads column 5 in srctestdata character by character. if any of the characters are ," ", commas etc...it ignores it and concantenates the letters are numbers. this is stored in chemNameSrc.
focus then moves to lists sheet.
here th macro reads the list starting at row 12 ending at 211.
stores letters are numbers in chemNamelist
chemNameSrc and chemNameList are compared. If equal then ok. If not, macro goes through the full list on lists sheet. It will color the cell red where there are no hits.
here is the
Option Explicit
Dim startRow, endRow, manhole, i, j, u, v, x, y, k, characters, endRowList As Integer
Dim nameHolder, letterHolder, chemNameSrc, chemNameList, testYear As String
Sub PopulateSheet()
startRow = InputBox("Enter the start row of the data where you would like to enter data?")
endRow = InputBox("Enter the last row of the data where you would the entered data ends?")
manhole = InputBox("Enter the manhole for which you are entering the data?")
testYear = InputBox("Enter the test year for which you are entering the data?")
For i = startRow To endRow
Cells(i, 1) = manhole
Cells(i, 3) = testYear
If Left(Cells(i, 6), 1) = "
View 9 Replies
View Related
