Detect When Web Query Has Updated

Aug 17, 2006

I'm trying to automate a spreadsheet that downloads a set of tickers from Bloomberg and then fills across a range with a series of data. I have most of the VBA code I need, but my problem is that on opening the spreadsheet, the code moves on to fill the range before all the tickers have downloaded correctly.

I've tried using application.wait and setting up loops until the time reaches a certain value, but these seem to stop Excel updating the list of tickers (presumably the loops stop excel gathering the data?). I suspect that what I need to use is something like the querytable. afterrefresh, but am not sure I'm using it correctly since my knowledge of VBA is very rudimentary.

(i) is there a way of getting the sheet to open, and then just wait for a period of time while still allowing the tickers to download, and/or

(ii) any way to detect when the tickers have finished downloading before the next step of code can commence?

View 2 Replies


VBA Recordset Not Returning Updated Results From Query?

May 9, 2012

I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.

I pass in the query name and the sheet name into qryName and sheetName name.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String


View 1 Replies View Related

Auto Change Data Range When Query Is Updated?

Dec 9, 2013

What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.

The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.

Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?

View 6 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

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 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

Query Parameters Which Takes The Date From The Cell Into The Query

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


View 2 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

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

Query An Access Query With VBA Function

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

Edit Query Using Query Wizard

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

Detect Add-in

Apr 25, 2008

I need to send a file to hundreds of users so that I can tell who has a particular add-in installed. This file will then be saved to a particular directory. The problem I'm having is controlling the error that occurs when the add-in is NOT installed. The below approach (using On Error Goto) clearly isn't the way to go. I need something like
If(Iserror(AddIns("AmartaSpecs") Then Cells(2,3)="No"

The code I have so far is:

Sub Amarta_Specs()
Dim a

'ActiveSheet.Shapes("Button 1").Delete
Cells(1, 1) = "Username"
Cells(1, 2) = "Computer Name"
Cells(1, 3) = "Add-in installed?"

Cells(2, 1) = Environ("Username")
Cells(2, 2) = Environ("Computername")

On Error GoTo Error
Set a = AddIns("AmartaSpecs")
Cells(2, 3) = "Yes"

Cells(2, 3) = "No"

ChDir "E:FinanceSQL Add-in test"
ActiveWorkbook.SaveAs Filename:="E:FinanceSQL Add-in test" & Environ("Username") & ".xls"

End Sub

View 9 Replies View Related

Detect Changes In A Row

May 4, 2009

I am trying to create a code in the Worksheet_change event. I have hundreds of rows of data in a sheet. Each row of data is one unique project. What I am trying to do is figure out if any cell in a specific row has changed, and place a date at the end of that row idicating the date of the change.

View 9 Replies View Related

Detect MsgBox

May 23, 2007

How to detect msgbox pup up event.

Does any function like msgbox.activate=true or somrthing can used.

I want to avoid msgbox show up and close msgbox automaticly.

I know


But it's seem not to apply to msgbox close.

View 9 Replies View Related

Detect If Workbook Is Already Open

Jan 26, 2007

I searched this site and found the code below to check if a worksheet to be updated is already open. It works fine (gets the desired message) if the workbook is open, but if not open I get "Run-time Error 9, Subscript out of range". I've played with it for a couple hours,

Dim wBook As Workbook
'Check that Summary worksheet is available for update.
Set wBook = Workbooks("CommittedSummary.xls")
If wBook Is Nothing Then 'Not open
'Do nothing
Else 'It is open
i = MsgBox("The 'CommittedSummary.xls' workbook is already in use and
cannot be updated. Please try again when the workbook Is available", _
vbCritical, "Committed Request Edit")
Goto EndUpdate
End If

View 5 Replies View Related

Function Or Sub To Detect Doubles

Jun 19, 2007

I have a column with employee id's. Sometimes two or more employee ID's are double. I need to dedect if there are ID numbers that appears more then ones. If I found such a number I can copy that.

Has someone an idea or is there a build in function to handle this?

View 10 Replies View Related

Detect Cells With Links

Jul 27, 2006

How can I detect on an excel workbook which cell have links(formula =) to
other workbooks without have to put my cursor on top of each linked cell.

I have had a hard time finding this cells. I can see the links by choosing Edit
then links, and excel will display list of links, but it does not specify
which cells contain those links

View 10 Replies View Related

VBA Detect Duplicates - Keep Only Newest Ones

Feb 23, 2012

I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.

View 1 Replies View Related

Detect Links Within A Workbook?

Jul 20, 2012

I have a workbook with a few worksheets. In sheet1 I have a few ranges which are linked to different parts in the other excel sheets.

Is there anyway to check if a range is being used as a link in another sheet.


In sheet1 , A1 i have a value Apple.

In sheet2, A1 I have linked it to sheet1 A1.

is it possible to detect the link in sheet2 A1 from Sheet1 A1? I tried using Trace dependents but all it shows me is the link symbol? An arrow with a small excel sheet.

View 1 Replies View Related

Detect If User Prints

Dec 12, 2006

Is there anyway to detect if the user has just printed. I have code which transfer data from the sheet to an access file when the user prints using the button I placed on the sheet, unfortunately some users are using the print button on the toolbar.

View 9 Replies View Related

Detect Key Press Upon Startup

May 27, 2007

Is there some way to detect that a key (specific or any) is being pressed when an Excel workbook is being opened to activate optional precessing in the macro code?

View 9 Replies View Related

Detect End Of Data In Row And Move To Next

Apr 12, 2009

if i have data across multiple rows than is it possible to analyze that data in say row 1 and once row 1 ends move to row 2. example

Array Data

Colmn A B C D E
Row1: 3 1 4 6 8
Row2: 6 7 8 9 10
Row3: 4 5 1 2 8
Row4: 6 4 3 2 1
Row5: 2 4 1 2 2

OutPut Format

Colmn F
Row 1: Age is 3
Row 2: Always Same - Note this appears even though not part of data
Row 3: Year is 1
Row 4: Time is 4
Row 5: Hour is 6
Row 6: Minute is 8
Row 7: !
!Not Part of Output Comment: Now row 1 data has ended. Move to Row 2!
Row 8: Age is 6
Row 9: Always Same - Note this appears even though not part of data
Row 10: Year is 7
Row 11: Time is 8
Row 12: Hour is 9
Row 13: Minute is 10

also note that the text that comes up in each row is not part of the data portion. Its just the number or string that gets pulled from the data portion.

View 9 Replies View Related

Detect Changing Color

Feb 8, 2007

How can I with vba detect when someone changes the color of the font in a cell

View 5 Replies View Related

Detect Blank Cell Value

Apr 12, 2007

I am trying to detect if a user leaves a cell empty. I have tried If statements like:

If Target.Value = "" Then


If (IsNull(Target.Value) Then

The problem is when the user uses the spacebar to delete an entry in that cell. If they leave any spaces after exiting the cell those if statements fail to run.

View 5 Replies View Related

Detect/Prevent Text

Jun 6, 2007

In column A i have a range of dates
In column B i have a range of descriptions
In column C i have formula which calculates an interest figure based on the date entered in column A and a date entered in a cell elsewhere. It also makes the cell display a couple of different strings of text if conditions are not met e.g. It displays "too early" if the date entered is more than 4 years before the date entered in the cell elsewhere

The problem that im having is this:

When i try to calculate column C i want it to throw an error if there is anything but a numeric value in the column. But it is obviously considering the cells in column C with for e.g. "too early" as a "0". I have tried all sorts of formula to do this to no avail, IFTEXT, IFNUMBER, ISTEXT, ISNONTEXT etc etc.

View 7 Replies View Related

Detect Operating System

Mar 8, 2008

how can I detect the OS of the computer a sheet is running on and launch a macro accordingly?

I have built a calculator that utilizes an embedded wave file but doesn't work correctly with Vista. With Vista it actually causes WMP to be launched instead of just playing the file. So I was hoping to be able to skip the sound on Vista machines but play for all other OS's.

View 3 Replies View Related

How To Detect Blank Rows And Then Delete

Apr 18, 2013

I need to detect blank rows and then delete them but for the formula not be be affected

In the example attached there are 4 components but i may only need to use 3 so row 4 would be blank

i need to find the blank rows and delete them and for the formula in cells G12:H14 to be update as necessary

at the moment if i delete the rows i get a REF# in place of the cell which has been deleted

i have tried this on a simple formula and when you delete a row the formula changes as required

View 5 Replies View Related

Macro To Detect Cell Range

Aug 25, 2007

I am currently using the following macro to input a formula into my worksheet:

View 12 Replies View Related

Parse A String And Detect ALT-ENTER?

Jul 16, 2009

I will have a cell that has company names in it and I need to parse each name an then do some work with them but when I started this project I was told each company will be seperated by some number of spaces.

Ex.) |Company1(534553) Company2(3544)|
Right now this is how I'm parsing a line like this:

View 4 Replies View Related

VBA Coding To Detect Internet Connection

Nov 4, 2009

Afternoon All - i have an excel workbook that runs on 20 remote machines that i do not have direct access to - i am placing upgrades on the net but looking at finding a way for excel to detect the update what i need it to do first is check if the machine is connected to the internet.

View 6 Replies View Related

Detect Hyperlinks And Source Links

Nov 21, 2013

I often have Excel sheets in which Excel tells me there are hyperlinks and content from other sources. I guess it happens because I copy something from one sheet to another.

First, is it possible to copy-and-paste from a sheet to another and only copy the concrete formulas and formats, and no source to the old file?

Secondly, how can I detect where these hyperlinks and links to other sources are? I see they exists, but I can't seem to find them. Does Excel have such function? Can I detect them with a VBA script?

View 1 Replies View Related

Detect Characters? Change Combobox?

Jun 5, 2008

let's say combobox1 has a list of:
apple (KG)
apple (PKT)
apple (BAG)
orange (KG)
Orange (PKT)
Orange (BAG)

and a command button.

Can I make it in a way that when command button detects (kg) in combox1, the caption of commandButton will change to "KG"? if detect (BAG) combobox1, then commandButton is "Bag"?

View 9 Replies View Related

Macro To Detect Cell Colors

Sep 8, 2009

We have a workbook with over 20 worksheets, each sheet with one or more tables in it. Several people use the workbook and we have decided that when someone changes a cell, they should mark the changed cell by filling it with blue using Format Cell. We have then nominated one person at end of each week who will check all the changes made during the week and either approve or reject them. So her task is to find all the blue cells, check them and then take the shading off.

What I would like to do is to create a macro to help her. I would like the macro to automatically identify all the blue cells in the workbook and give her a list of these cells. I wonder if someone could give me direction on a couple of things:

1) The command in the macro that would return whether a cell is shaded blue

2) The way in which I could cycle through all the content of each worksheet. I'm thinking worse case I would have to write instructions to go to each sheet, and then do an outer loop to go through each row, and an inner loop for each column (and the number of active cells varies by worksheet) -

View 9 Replies View Related

Copyrights 2005-15, All rights reserved