I've used a VBA procedure to pull stock price historical data from Yahoo. Something has changed something and now the procedure will not work. With a worksheet named 'Web Data', I execute the following procedure. I now get an error from Excel (2003) stating that "The file could not be accessed. Try one of the following: ...", and then "Invalid Web Query". Here is the code that I am using:
'e.g. pull Dow Jones data for Feb 14 thru Feb 15.
smo = 2 ' Feb
sda = 14 ' 14
syr = 7 ' 2007
emo = 2 ' Feb
eda = 15 ' 15
eyr = 7 ' 2007
fund = "^DJI"
Sheets("Web Data").Activate
Range("a1").Select
Selection.Clear
With Selection.QueryTable
.Connection = _
"URL;http:// finance.yahoo.com/q/hp?&a=" & smo - 1 & "&b=" & sda & "&c=" & syr _
& "&d=" & emo - 1 & "&e=" & eda & "&f=" & eyr & "&g=d&s=" & fund
.Refresh
End With
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.
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
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.
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..
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.
Any Excel document that I try to open, I get the message
"XXXX.xls is not a valid Win32 application."
However, if I open Excel and then open the application it works.
It initially started on my XP PC. I uninstalled Office and reinstalled it...no change. Two weeks ago I upgraded to Vista(not for this problem)...no change.
How can I get the apps to open when I click on them?
When I insert a row into a worksheet (by selecting the row and then hitting ctrl+plus, or by going through the menu insert, row), a message pops up, as follows:
I created this worksheet from scratch and am not aware of any invalid reference.
I have a error with this code Row Invalid Qualifier how can I qualifier Row
Private Sub CommandButton2_Click() Dim Row As Double Dim addItem As Integer Dim count As Integer count = 0 UserForm1.ListBox1.List(count, 0).Value = "" ActiveCell.Value = "" Sheets("Sheet2").Cells(1, 1)(Row.count, "A").End(xlUp).Offset(1).Value = ""
Running a worksheet with vba, at the start of the code I turn warnings off and at the very end of the code I turn them back on.When the routines complete a warning is raised:
A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.
When I run Error Checking everything comes up clean.
Clicking on "Show Formulas" shows no formulas for there are no formulas on the sheet to show, just a chart, and clicking "Show Formulas" just turns my dates into serial numbers and screws up the display of the two comboboxes, messing the fonts and drop-down arrows, and since closing and reopening didn't revert the comboboxes back to normal I will now have to blow them away and resurrect them. I also see Show Formulas also messed up my command buttons. nice...
The only way I find to avoid the error is to turn Warnings OFF at the start of the charting routine and NEVER turn it back on.
I made certain that none of my names had any errors in them. Everything looks proper. So what next?
I fear something will create an error for me now along the lines of Murphy's Law
What is the best way for my UDF to return an error to the calling worksheet if it detects an invalid parameter?
In the past, I have usually set a breakpoint so I could check the the values and the logic. Other times, I return an invalid result, like 0 or -1.
I am working on a UDF now that is called hundreds of times. The workbook is a work in progress so I am constantly making changes to the UDF and the calling cells. Periodically, I screw up and do something that causes every call to get an error (like divide by zero).
I am getting an invalid procedure call when the portion of the code that has the ASC function runs. The only change I made to the workbook was to increase the available rows that this macro is totaling from 150 to 300. If I don't increase the rows I do not get the error.
What this code is doing is grouping information from 20 different sheets and totaling them and placing the total in the correct group. Most of the totals will begin with a number, however there will be a small amount that will begin with a letter. The items with letters need to be grouped in the 17000 category.
Dim c As Range Dim rng As Range Set rng = Range(Cells(3, "R"), Cells(lastrow, "R")) For Each c In rng
If c < 20 Then c.Offset(0, 1) = "01000" If (c > 19) * (c < 26) Then c.Offset(0, 1) = "02000" If (c > 25) * (c < 161) Then c.Offset(0, 1) = "02600" If c > 159 Then c.Offset(0, 1) = WorksheetFunction.Text(c, "000") & "00" If Asc(Left(c, 1)) > 58 Then c.Offset(0, 1) = "17000" If c > 170 Then c.Offset(0, 1) = "18000" Next c
I am trying to create a macro to search through a workbook and delete "empty" cells, leaving just the range of cells with valid data. So far i've come up empty handed. I have attached a sample workbook with a spreadsheet on it that has the empty cells I speak of. Press CTRL+END to see where it takes you in the sheet and it will be well beyond the range of data I have inserted. The reason I need to do this is because I gets spreadsheets back from clients that need to be imported into SQL and it is very time consuming to have to go through each workbook.
My goal to checkup/analyze our contact details database, to do that i have to manually tag the invalid entries ( blanks, 0, wrong cellphone format, x, NONE etc. ) for cellular phone field as " INVALID " and if the cellular phone format is correct we'll tag it as " VALID " ( please refer to my attach file ) i have to get this task as fast as i could and our database comprises of 200,000 imagine if i have to manually tag it even in batches i have to figure out how to automate this..but the filter function of excel seems to be lacking for me. if there's a way how to automate this with this function:
- define field to be filtered out as for this case ill define cellphone # format and tag it as " VALID " - define field to be filtered out as invalid entries ( blanks, x , 0, numbers less than 10 digits, entries that are telephone format, NONE, /// , XX, aa, @ ) or any sort of entries that are not cellphone format
I'm trying to attach an Excel file but I keep getting a message stating it's an invalid file. I don't remember having trouble before attaching here, but I am now. What must I do?
I have a userform that has validations in it. One of the validations is to ensure the user has entered in a correct date. Right now if the user has the correct date in the form, but decides they no longer want to add any data into the spreadsheet using the userform, they can simply click the Close Form button at the bottom of the form and the form will close out.
I just discovered however that if the user inadvertently put in an incorrect date and then attempted to close the form, the validations will not allow the user to close the form until they have corrected the date. I would like to set up my close form button to close the form no matter what data has been entered into the form. Basically have it so the close form button overrides any validations within the userform coding. Currently I only have unload me within the cmdClose_Click() sub routine, It looks like this:
[Code]....
Could I use something to the effect of clearing all fields when the close form button is clicked...
I am combining the information from multiple files--one per state, for several states--into a single large table so it's more easily reviewed. Each file has multiple worksheets, named for categories. The category/worksheet names are standardized, but not all worksheets are found in every file.
There is some overlap of items from state to state, so my macro copies key fields from each worksheet and pastes them into the new table and adds a column on the left with the name of the category/worksheet each item came from. It then removes any duplicates to create a list of unique items. Across to the right, the table has two columns for each state, one to show if the item exists there, and the other the date it was added.
In the final step I am using lookup formulas to populate the states columns, using the category/worksheet name from the left-hand column to identify which worksheet to pull from. When a worksheet doesn't exist in a source file, however, this creates an invalid reference.
Is there any way to use VBA to identify which worksheets are in a file so I can use the results in an IF/THEN statement to bypass any lines that would create the invalid references?
This error occured me, and i clicked "Debug" to see where was the error and i found a strange thing that i don't know how to solve it: The line has the following
I am trying to get a combo box to work, but keep encountering the error "Invalid Property Value."
When the combobox entry is deleted and the user moves to the next text box in the userform, this error pops up which is very annoying. It also pops up when the word entered doesn't match, like it is supposed to.
I have MatchRequired set to True, because I want an error message to come up, but with my own error message like " That name doesn't exist, please try again ".
I can't figure out a way to ignore the "Invalid Property Value." error message, and show my own customized message.
I have a macro that asks the user to enter a file name and have written a small function to search the name given to the file to find any invalid characters, trouble is I must be doing something wrong as I doesn't work. Everytime I write something in the box I'm told it's invalid, regardless of weather it actually is or not.
I have a database in excel format containg 20,000 + rows with multiple columns, one of the columns contains web addresses in the format [url].
I have a list of around 3.500 web addresses that are no longer valid ie thay dont work, I want to filter the original list to delete all the rows that contain an invalid web address including all the other data in the row, does anyont know a way to do this without having to do it manually one at a time?
