How do I handle URL connections - such as logging into a web application?
Example 1
When I use .Refresh BackgroundQuery:= False and there is a problem connecting, Excel hangs ... cannot Ctrl-Break ... only option is to kill Excel (and lose changes).
global QT As QueryTable
Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL,
[Code]....
Now, if it's still refreshing, it's ok. But if not, it gets stuck on .Refreshing with run time error 424.
Ideally I want to use .Refresh BackgroundQuery:= False as I am calling a number of urls. I just need to provide an "out" when the url fails and hangs on .Refresh BackgroundQuery:= False
See the attached spreadsheet with a macro that gets the querytable in Sheet1. In sheet2 there is a formula to get one of the balances from the querytable result. Is there a way to find the balance on sheet2 from the querytable at runtime so that I do not have to download it to Sheet1? Then I could do away with Sheet1 altogether.
I have some code that runs and loops through each pivot table to refresh it; however, I remmed out the code so that it only loops once - telling it to refresh the first pivot; the thing I'm confused about is that after the code runs, it appears to have refreshed all my pivots. I think it's also important to note the each pivot table is linked to the same data source. (an external ds)
Maybe when this is the case, refresh will always refresh all?
I have an Excel Workbook with Showcase Queries embedded into it.
Upon a manual open, the user is prompted to either refresh data or keep data since last save, the user then has to enter a username and password to allow the query to access the database.
Using the code below to open the workbook, no such prompts are given. Is the code overriding the prompts and automatically refreshing the data without password? [Given nature of data, visual validation of refresh is not possible]
If not, how can we code a data refresh through showcase query with user/pw req's?
I was hoping that my pivot table would refresh with the new data that I added on sheet 1 but it is not working and yes I have refreshed my pivot table. The new data is not capturing.
I have a csv archive file on a remote server that I am accessing with excel. The problem, if I use QueryTable, all info is placed in the first column. If I use the workbook.open method, the info is placed in the proper cells, but it is in a seperate workbook.
So, how do I get the workbook.open results, except in the active workbook/worksheet? Or, is there a way to QueryTable (my preferred method) and get it into seperate cells?
I have the following code which works but is slow. All the code is the same except the address part and the destination range. Any help simplifying would be great. Also, every time I run the sub it asks me if I want to replace the destination cells. Is there any code to circumvent that? Lastly, although the second and third query requests have a different destination range, excel shifts the previous query to the right, hence first command after final query request.
When a QueryTable is refreshed and the number of rows it returns changes, I *expect* that a corresponding Named Range should be automatically resized. The sheet-level Named Range (same name as the QueryTable) should cover the query result's header row and all returned data rows.
But now I've got a Named Range that seems to have become disconnected from its QueryTable. The query returns fewer rows but the Named Range stays the same size.
This is even more perplexing because the aberrant QueryTable is right next to another QueryTable that is behaving normally. The Data Range properties of both are identical.
I have a querytable running on my sql database. In the query I have a trimleft function because the numbers are stored with several proceeding blank spaces. When I execute the query through my macro, everything works fine, except I can't average or sum the results. They can be multiplied, divided, etc, but not averaged or summed (they just show as zero on summing, which makes the average DIV/0). The really strange part is that if I select a number and go to the formula bar and hit delete at the beginning of the number (which actually deletes nothing), the number is suddenly capable of actually performing as a number. I assume this has something to do with how the querytable is returning my results, but does anyone have an idea for a workaround for this issue?
I have a bunch of workbooks that are used to capture information about implants used in surgical cases. I use a small VBA process each morning to update a querytable which contains all of the core patient information for future cases, however it is not working properly. The code goes through the process OK, or so it appears, opens and closes each sheet, and saves them appropriately, but the query table doesn't seem to refresh no matter what I do.
Sub RevisedRefresh() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch .LookIn = "G:SHSTheatreCommonReceptionImplants" .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then For lCount = 1 To .FoundFiles.Count Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)...............................
I have been working with one sheet (call it tab A) that has 40-50 vlookup formulas that reference another sheet (call it prices) within the same workbook. Everything worked fine. I change my prices tab and everything auto changes in tab A.
Now I copied 'tab A' 22 times creating tabs B - W. I need to slightly modify each tab and based on my prices data, print out the appropriate tab.
Problem is now, when I change my pricing tab, tab A - W do not update. F9 does nothing. Rebooted didn't work. If I go into the formula and hit F2 to edit & return it recalculates to the correct data. But I can't get it to auto update.
I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.
Is this correct?
If so, is there another way to "refresh" the usedrange property without saving the workbook.
If not, is there another way I can determine what the usedrange actually is at any point in time?
I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
I've built a spreadsheet with quasi-animation for a physics class on projectile motion. In it there's a animated chart showing velocity change as a function of distance. The chart has on it an ActiveX textbox that refers to a cell that contains a formula showing "V_y = {velocity}". I've tried a number of things to get the display of the textbox to change during the animation. If I break the code at the line that updates the cell referenced by the textbox the display changes. But I can't figure out how to get the display to update while the code is running.
Im using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?
If i open the excel all the cells with this similar formula show as 0. If i press Shift+Ctrl+Alt+F9 then they calculate.
I have another SUMPRODUCT formula on another worksheet in the same book that seems to be fine. =SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(Data!$E$7:$E$2000=B7),Data!$M$7:$M$2000)
I can only assume that the reason why it's not calculating automatically is to do with the TEXT "mmmm" reference, is that correct?
Is there anyway i can have it autocalculate so that if i send this as a report i don't have to ask the ppl looking at it to please press Shift+Ctrl+Alt+F9 when they open it :P
I have some code that downloads prices for me in excel. However, instead of running this macro everytime I was wondering if it is possible to write a piece of code that would run this macro every x minutes?
I have been working on creating a form in Excel. I have already figured out how to have combo boxes that are dependent on one another. Now I have a question on that. Let's say that my user makes a selection from the first combo box. Clinical Services for example. The second combo box should now show only the values that pertain to Clinical Services (which it does, that part is working fine). So let's say they choose Retail under Clinical Services. However, now let's say that the user changes their mind and they go back and change their selection in the first combo box to Mail instead of clinical programs. The second combo box stays on the selection that they had previously picked (Retail) even though that value does not pertain to their new selection (Mail). How do I refresh this to delete their old selection in the second combo box (Retail)? If they change their mind of the first combo box and select something else the second combo box does display the correct drop down information, it just doesn't clear out the old selection.
I'm looking for a way to refresh my pivotchart and update my pivot table data every time that a certain macro runs. I could just delete it and make a new one everytime but that runs in to the problem of defining a new sheet every time. Is there a simple way to do this. say I my pivot chart is on "Sheet 7" and it there is only one pivot chart in the workbook and my source sheet is on "Combined Data". and at the moment the data range is "A1:N435" and say next time I run my macro the data range will add 20 rows of data and the next 25.
I have a oval which changes color using the color given in a certain tile, temporary called "Hi" . However the oval doesn't change color automatically with the tile changing, it waits till the user clicks the checkmark for the tile, I'd like it to do it automatic, but it doesn't. Is is possible to animate this script a bit more so it refreshes upon a change in the "Hi" title.... ?
I have a pivot table that I refresh using .refresh in the VBA code.
My problem is that when I loop through all the PivotItems in a filter list in order to make all visible, there are PivotItem names that aren't in the drop down menu and so the code breaks.
Do I need to refresh the pivot table slightly differently than just using .refresh?
In another column I enter a comment for customer 30010(and leave the comment column for customer 30041 blank) as below Data 1.xls Customer Total sales(USD) Comment
30010 500.00 Test 1 30041 2000.00 Later on the content of the file changes as below:
Customer Total sales(USD)
30025 1500.00 30010 500.00 30041 2000.00 When the file is refreshed I would like to have comment as per the former file i.e the comment “Test 1” should appear against customer 30010 and similarly for any other customer that I enter the comment.It should be as below: Customer Total sales(USD) Comment
30025 1500.00 30010 500.00 Test 1 Customer Total sales(USD) Comment
I've referenced a few threads this one in particular [Solved] Pivot Tables : Refresh on protected sheet
I've tried the example given in the link and it still does not work. I want to protect the sheets that contain pivot tables, but still be able to refresh them. When I try I get an error. I've tried this a few ways.
My first attempt was to protect the sheets using allowuserinterfaceonly at a workbook open event. This caused warnings as soon as the workbook was opened. Then I tried Dave's suggestion and I get a runtime error 1004: Cannot edit PivotTable on protected sheet.
I've got a workbook with a several pivot tables. For convenience, I've deleted the pivot table source and left the pivots "self-standing". Problem is now when I try to refresh the workbook, I get a "Reference is not valid" error which prevents the workbook from updating.
Its been ages since I used a pivot table and I cant remember much.
I did one last week - lets pretend on the range A1, B100.
Ive since added some extra info into the range and it now goes from A1, B110.
I go to my pivot table and hit "!" and it updates. How can I be certain its included everything from my range? Is there a way I can check the pivot table to see where the range is from?
I would like to refresh all external links to other xl files (on a network drive) and break all links
I have a code like this:
Code: For Each l In ActiveWorkbook.LinkSources Workbooks.Open Filename:=l, updatelinks:=1, ReadOnly:=True ActiveWorkbook.Close False Next l
For Each l In ActiveWorkbook.LinkSources ActiveWorkbook.BreakLink l, xlLinkTypeExcelLinks Next l
It seems to be working, but if i go step by step (f8) it updates values but sometimes after closing the source workbook and updating next one, the previously updated values disappear...
I cannot open all files because there are many of them and they are like 40mb each.
There are no defined names in the file, except print_area
I have a worksheet with an auto filter to display only non-blank rows. Each row is a ledger account, with a cost. The formula in each cell sums the total of its corresponding gl in another worksheet, but also takes into account the region I am asking it to search based upon another cell. So the cost in a specific can fluctuate based upon which region I am telling the rows to lookup. So it can be blank with one region and populated with another. When I change the cell that tells the row data to look up a new region, the auto-filter does not automatically update the list of non-blank cells based upon the new region being searched, but rather the old one. So what is happening is I am left with an incorrectly auto-filtered result.
Is there a way to get the auto filter list to automatically update for the refreshed list of rows?
