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..
i have a worksheet that gets data from web - its automated, but the website goes through a tunnel - with security - requires username and password
the Query Runs ok and smooth but i have to login manually by right clicking on a table where the query is and selecting "Edit Query" so i can login, excel vba doesnt save passwords for that part...
one way i find it possible to automate that part too would be to use maybe like the sendkeys statement to pop up the context menu from right clicking the mouse button, or like the options button on the keyboard.
I found this one:
[Code] .....
It works but the context menu comes exactly from where the current mouse position is..
I needed it to do the right click on a specific range in the worksheet (where the web query is)..
I am using the following code to run a Stored Procedure Query. It works perfect bringing back 4 columns of data. But is it possible to make it so that it only brings back 2 columns of data. I need the 2nd and 4th, which are named "DateOf" and "Gen", respectively. Someway to include a select command?
I posted here, without luck: http://www.mrexcel.com/forum/showthread.php?t=367809
I have recently installed excel 2007 how do you edit and ODBC query from the spreadsheet. In 2003 you right clicked on a cell that opened a dropdown where you selected edit query.
View 1 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
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!
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.
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 Relatedi 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 RelatedIs it possible to open microsoft query without opening Excel or Query Wizerd.
View 3 Replies View RelatedIs it possible to use the Web Query function in Excel on a PDF file?
View 2 Replies View RelatedI'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 RelatedI 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.
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?
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.
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.
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 RelatedI 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.
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...?
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"))
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.
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.
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.
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 RelatedI 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.
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"
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?
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
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) = "