Hiding Sheets Causing A Runtime Error On External Data Refresh
Apr 20, 2007
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies
ADVERTISEMENT
Aug 23, 2007
I have just signed up to the forums as I am having a very specific problem. I have looked on google and used the search feature (on several forums!) and found very limited information that is useful to me. I am having trouble with a very simple few lines of code in a macro. The code looks like this:
Function startmarketIDs()
Dim targeturl, writerow, readrow, textmass, xmlHTTP
targeturl = "http://lite.betfair.com/ Events.do?s=00010913z"
Set xmlHTTP = CreateObject("Microsoft.xmlHTTP")
xmlHTTP.Open "GET", targeturl, False
xmlHTTP.send
MsgBox xmlHTTP.StatusText
textmass = xmlHTTP.responsetext
MsgBox textmass
End Function
My problem occurs at the line "textmass = xmlHTTP.responsetext". The responsetext command is obviously causing problems because if I remove it, the code executes without error and the "xmlHTTP.statustext" says "OK". With the responsetext command left in, the code generates the following error: Run-time error '-1072896658 (c00ce56e)': System Error: -1072896658. If I change the targeturl to www.betfair.com, the code executes fine and I get a message box (as desired) with the source code of the website displayed. Without meaning to sound like Im answering my own question (!!), perhaps there is something about the url or the way in which I am using the xmlHTTP object that is causing the issue, I wondered if someone would mind inspecting my code and perhaps pointing me in the right direction?
View 2 Replies
View Related
May 6, 2009
I'm relatively new to this, and its entirely possibly (more likely probable) that I am attempting to this in the wrong way, but here's what I'm dealing with...
View 8 Replies
View Related
Jun 29, 2009
I am having some issues using 'refresh all' to update my excel spreadsheet. Basically, it doens't seem to like doing anything greater than 65 queries before it gives me the error "Too many client tasks".
I've written a macro that updates all spreadsheets seperately, but this takes forever (I have approximately 250 worksheets within this workbook). Is there a way to speed this up? I believe the most time consuming part is establishing the connection to the access database. Is there a way to establish the connection to a database, and keep it open until all data has been updated?
Below is the code I am using:
Sub RefreshAll()
Dim e As Double
Dim sht As String
e = 1
Application.ScreenUpdating = False
Do
Sheets("All ESNs").Select
Cells(e, 1).Select
sht = ActiveCell.Value
Sheets(sht).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("All ESNs").Select
e = e + 1
Loop Until IsEmpty(ActiveCell)
Application.ScreenUpdating = True
End Sub
"All ESNs" refers to a worksheet that contains a list of all serial numbers. These serial numbers correspond to the worksheet names.
View 9 Replies
View Related
Oct 25, 2007
I am trying to set up a button that can be used to refresh data from an external source. The problem is that I'm not sure what to put in the area that I am using the "query" variable
Sub refresh_data(ByVal WS As Worksheet, query As String)
Application.DisplayAlerts = False
Sheets(WS).QueryTable(query).Refresh
Application.DisplayAlerts = True
End Sub
what i end up getting is an error saying basically that I am using incorrect data.
View 3 Replies
View Related
Apr 30, 2014
I am using an Excel file (Raw.xlsx) to store my raw data, and take another Excel file (Pivot.xlsx) to make PivotTable from Raw.xlsx
When generating the PivotTable, every works fine. When click [Refresh] in the Raw.xlsx, whatever changes been made in the Raw.xlsx can "sync" to the Pivot.xlsx
However, once I save and reopen the Pivot.xlsx, I can't refresh the PivotTable anymore. I've tried changing Data Source, but it doesn't work.
View 1 Replies
View Related
May 29, 2008
In the Data tab under Connection Properties, I have "Refresh every x minutes" checked. Question is how do I capture in a cell within a sheet the last time it was refreshed?
View 2 Replies
View Related
Jan 4, 2014
I need to use custom "Refresh all" function, so i set ctrl+alt+F5 to mine one and trying to hide/remove "Refresh all" from command bar control "Data" i tried different ways and no effect
VB:
Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("Data").Visible = False
End Sub
[Code].....
View 2 Replies
View Related
Dec 4, 2013
I have a pivot table report connected to an external data source (OLAP cube).I want to automatically refresh the pivot table report everytime the OLAP cube data changes.
I know we can use Automatic refresh on open of workbook or time intervals after which to refresh .But i want the auto refresh to work even if the workbook is already open and there is no definite time interval after which the cube is likely to change.SO,by defining intervals for auto refresh i do not intend to waste time refreshing even if there is no change.
There are multiple pivot table reports and pivot charts connected to the same OLAP cube.
View 1 Replies
View Related
Jul 9, 2014
I have an excel table that connects to an external sharepoint site.
When I refresh (via external data connection), the "start date" and "end date" column formats change. The only way I can correct this is to copy those columns to notepad, and then paste them back into the spreadsheet.
I have several calculated cells based upon these date fields. Once refreshed, the data is incorrect until I copy and paste those dates as stated above.
This removes the functionality of simply "refreshing" the data resulting in a manual process.
View 2 Replies
View Related
Nov 25, 2009
I have a macro that first refreshes a specific data range that imports data from a text file as such:
View 2 Replies
View Related
Mar 23, 2012
I am pulling external data from an Access 2007 database query that uses the nz function. While linking to the query, I get an "Undefined Function nz" error. The query works perfect when run in Access.
View 1 Replies
View Related
Jun 27, 2014
I have a macro which creates and names worksheets. I am making a button which also deletes the latest of these created worksheets, but doesn't delete other sheets. I am getting the error: Run-time error '424': Object Required. Here is my code for deleting the sheet:
[Code] .....
MSCount stores the highest "MS#" sheet.
The first line of the IF statement is where the error is.
View 7 Replies
View Related
Mar 5, 2014
I'm trying to set up a macro that will capture all the unique values in a column, and then create a new sheet for each value. What I've got so far technically works, but it's also adding an extraneous sheet (with a generic sheet name, not the specified name I'm giving the sheets) and also giving me "Run-time error '1004': Application-defined or object-defined error" when it runs.
View 3 Replies
View Related
Mar 3, 2009
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View 4 Replies
View Related
Nov 15, 2013
Trying to be slick with my macro I have 2 sheets collecting data in a workbook. I am attempting to send as attachments only those 2 sheets. I am getting an error at the following space within the code. How do I get around this?
Code:
Sub Send_to_Me()
' Send_to_Me Macro
Dim objTemp As Object
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim FileFormat As Variant
[Code] ......
This is the line that I get with the message above. It is only 2 sheets. Excel 2010 Windows 2007
View 2 Replies
View Related
Aug 26, 2009
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
View 4 Replies
View Related
Apr 13, 2006
whether I can handle errors in a way that tells me which object caused the error?
For example if a userform has two combobox's and one causes an error, can I return to a message box containing the name of the object (combobox1 or combobox2)?
I have tried returning err.source but I am either not using it correctly or it isn't intended to be used this way.
View 3 Replies
View Related
Nov 16, 2009
worked at one point but now gives a '1004' runtime error. What I am trying to accomplish is have the user display a line of data on a userform, modify it, then write it back to another sheet in the workbook.
My ...
View 9 Replies
View Related
Apr 17, 2014
I'm generating a runtime 1004 error with the macro I have attached to CommandButton1, "Clock_In".
The macro is copying the name, date and time from 3 cells on Sheet1 and pasting special values to another sheet.
View 2 Replies
View Related
Feb 14, 2014
I have a very basic code to copy "non-blank" data from one sheet and paste to another. The code is not complete yet - I am running in debug mode I get the above error. My code is as below.
Sub SampleFind()
Dim StrWord As String
Dim Quantity As String
Dim i As Long
Call nrows
For j = 2 To 2
For i = 2 To nrows1
[Code] ..........
View 4 Replies
View Related
Mar 10, 2009
My formula to add a row to a worksheet worked fine, except for the fact that it was including items with 0%. I need items with >0% but <=50%.
Here's the code that sorta works...
View 6 Replies
View Related
Mar 10, 2014
why the following code generates an error if I use the backspace key to clear the textbox?
Code:
Dim t As Date
If Len(Controls.Item("txtstart").Value) = 4 Then
On Error Resume Next
t = TimeSerial(Left(Controls.Item("txtstart").Value, 2), Right(Controls.Item("txtstart").Value, 2), 0)
On Error GoTo 0
ElseIf Len(Controls.Item("txtstart").Value) = 5 Then
On Error Resume Next
[code]....
View 9 Replies
View Related
Aug 17, 2009
The IF formula works if placed in a cell, but Vba doesn't seem to like the Quotation Marks in Rc11="".
View 2 Replies
View Related
Apr 24, 2014
I have a VBA excel file that pulls data from a CSV file downloaded from a link. I format this data into a table through excel and then copy it over to Outlook. This is where the problem is. I get a runtime error 4065 for "file is locked for editing".
View 1 Replies
View Related
Mar 12, 2014
A large Excel file that I have been working with for months is now giving me trouble.
When I tried to delete 2 rows, it displayed a message: "Excel cannot complete this task with avail resources. Choose less data or close other applications."
I opened a previously saved version of the file and tried deleting rows, and the same message came up.
I shut down the computer, rebooted, same problem.
Any way to determine if a file has been corrupted .... or even worse ... is there a possibility that my Excel program itself is corrupted?
View 8 Replies
View Related
Feb 7, 2014
why my code would hit a run time error on a shared file? It works fine unshared. This is on a ActivateSheet Worksheet Event, Code is housed in a Module and called when activated.
[Code]......
View 2 Replies
View Related
Oct 26, 2008
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
View 14 Replies
View Related
Jul 14, 2014
I am receiving the following error:
-2147417848 automation error the object invoked has disconnected from its clients
Unfortunately, I can't tell where the error is occurring because everytime it happens excel crashes or at least stops responding. I tried waiting it out (about 20 minutes) but no luck. the only thing I can do is force the program to shut down through the task manager.
The program is designed to pull information from finviz.com (a financial site), filter the data. pull the next round of data and run the filters again. there are a total of 8 queries and i think it gets hung up during the 6th or 7th run through.
Stepping through the code does not seem practical since each query might pull as many as 300 rows of data that is evaluated in a for/next loop (thats a lot of F8s).
View 5 Replies
View Related
May 3, 2006
I'm getting the error for the following piece of code.
Sub itconfandscratch()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "sturecord"
Database_Name = "Scratch" ' Enter your database name here
SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ""..............
View 2 Replies
View Related
