This is to run stored procedures (in the SQL Database), and pass variables. But...once the query is setup...all I really want to do is change the variable...and "fix" the sql query with the new variable value.
Is there a better way than: ....
I'm trying to update a query table in excel, but it keeps giving me the error 1004
Worksheets("Data-All").Range("A4").QueryTable.Refresh BackgroundQuery:=False
Cell a4 is in my query table.
Im using a spreadsheet with several third-party add-in queries using Showcase Strategy 4.5. The trouble I'm having is in writing a macro that will automatically refresh the queries. Currently I am using the line
application.run ("refreshdata")
however this refreshes all the queries and at several points I only need to refresh one or two. If anyone has any suggestions then I'd be very gratefull.
Also I feel the need to confess that I pilfered the above code from another forum and so I dont actually know how it works. VBA help doesnt seem to have anything relevant to say and I'd feel better knowing what the code I'm using actually does.
I have a form that laods when the workbook is opened. I also have an MS query that retrieves data from another source. When i open the workbook without the form loading i get a message asking if i want to update the query automatically. When i open the workbook with the form opening the message doesnt show until i close the form meaning i have no data in the sheet to use. Is there a way to get the query to refresh before the form opens?
View 2 Replies View RelatedI am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
I've got a web query setup to poll a table detailing traffic information from my state's DOT page.
I have the web query set to update every 5 min.
I would like to save a particular cell from this query every 5 min and place a timestamp next to it.
Ultimately, I would like to build a table, one column = time, the other column = traffic data and have each row be 5 min apart.
I am bringing data in from an ODBC database, I am using ActiveSheet.QueryTables(1) and then .refresh.
I want to run a sub after the data has been brought in which finds the last row and adds a formula.
The problem I have is that the data brought in is not show on the screen until all of the macros are finished.
Is there a way of forcing the screen to update?, I have tried the recalculate and refreshall commands or is there another method I should be using?
Sub autofill()
Dim myobj As Object
If ActiveSheet.QueryTables.Count > 0 Then
Call clearscreen
Application.DisplayAlerts = False
Set myobj = ActiveSheet.QueryTables(1)
I require the data to be on the screen before I can find the last row used which is done in "addmaths"?
i have some code (see below). at the part where it says
If ActiveCell. Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
i want to make this smaller - i have to write this line bout 60 times 3 times over (incresing the numbers as i go ((0, 9..10..11 etc etc and (Skill_32..33..34 etc etc) )) to give you an idea of what im trying to accomplish - im opening and writing to a database but if the cell doesnt contain any info dont update.
Function EditCPS(ByVal StaffNumber As Long)
On Error Goto Err_Handler
Dim SkillsBuilderDB As Database
Dim RSSkillsBuilder As Recordset
Dim MySQL As String
Set SkillsBuilderDB = OpenDatabase(Worksheets("Adding Data").Range("IV1")) ' database location
MySQL = "select * from CPS where StaffNumber=" & StaffNumber
Set RSSkillsBuilder = SkillsBuilderDB.OpenRecordset(MySQL)
With RSSkillsBuilder
.Edit
If ActiveCell.Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
Else............................................
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
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.
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 used the function = now() to have the most updated time but it updates a workbook when I open it in the first place. How I can avoid this?
View 9 Replies View RelatedLet's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B
Yes 12
12 13
No 10
13
No
10
Yes
And if I want to add in column A:
A B
Yes 12
12 13
No 10
13 25
No 15
10
Yes
25
15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
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 have some VB code which sequentially opens over 200 workbooks to extract data from each and populate another workbook. These workbooks do have links to other workbooks in them.
For some reason when some of these workbooks are opened I get a requestor window asking whether I want to Update or Don't Update the data. I always want to Update the workbook and believe this can be done in VB by hiding the requestor?
I have a vlookup to another workbook. It works fine if both workbooks are open. But if both are not open and I open the workbook with the links and click Update, #VALUE! returns. I have attached the two files. I don't think it is my formula, but here it is anyway. =IF( COUNTIF([Tempozgrid.xls]June!$A$52:$A$83,A3),VLOOKUP(A3,[Tempozgrid.xls]June!$A$52:$L$82,12,FALSE),0)
View 2 Replies View RelatedI have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
I have been trying to remove or disable the message on update to other worksheet " To update all linked click yes......" I have try the following unsuccessfully
Sub auto_open()
Application.AskToUpdateLinks = False
End Sub
On the menu bar choose Edit ---> Links Can not choose manual, as the option is grey out (disable) I would prefer a vba solution, but I am open to anything to get rid of this message
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
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 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 Related
