Web Query Causes Workbook To Freeze
Jul 23, 2007
I've had this workbook freeze during a perpetual dynamic web query for quite some time now and have mentioned in a few previous posts while trying to elimnate probable causes.
Nothing suggests anymore it's codes or structre of the codes in Excel or even physical memory issues.
Symptoms as best as I can describe.
It only seems to happen when observing some extra internet activity due to the icons flashing on the task bar, in a split second.
For example, perhaps; auto-update checks, browser pop ups when logged in to a secure web site, or I even suspect maybe some sort of Ping, yet it's part of the overall standard operation of Wndows OS Ping.
From my intense observations it's always when the 2 TCP icons on the task bar flash an extra "beat".
Hope this makes sense in trying to describe a cause or symptom.
The nature of this glitch happens on all computers in both Office 2000 and now 2003 versions of Excel.
Fresh install of the entire OS has been done with standard Installation and a fresh re-insatll of Office,with or without security softwares etc.
It's always exactly the same, it will freeze at the precise moment the flashing icons is not the regualr sync. during the perpetual web query process.
It will freeze all worksheets, meaning I have to Close and Re-open the Workbook and it's ok till it happens again.
Where to now with this glitch, or would it have something to do with certain port settings?
View 9 Replies
ADVERTISEMENT
Jun 1, 2012
I have a macro which I recorded, then modified. The first thing it does is to freeze the top row, then it goes looking for a particular row and inserts some formulae. Nothing complicated at all.
It WAS working perfectly, but I wanted some improvements in the insertion of the formulae. I got that working just fine, but now the panes freeze in the wrong place. No matter what I do, it freezes at cell I16. I want only the top row frozen.
Found this code which was reported to work:
PHP Code:
ActiveWindow.FreezePanes = False Range("A1").Select ActiveWindow.FreezePanes = True
It still freezes at I16. I have tried shutting down Excel, and even my computer, in case it is some weird bug where something is stuck in memory.
I tried recording another macro to format some cells and also freeze the top row. Same result.
PHP Code:
Sub wraptext_top_row()'' wraptext_top_row Macro''
Rows("1:1").Select With Selection
.HorizontalAlignment = xlCenter
[Code] ..........
Same result. I tried copying the contents of the worksheet to Notepad, then into a fresh workbook, just in case there's some weird hangup in formatting that I can't see.
There is NOTHING in the original code that references that cell, or even that row or column. NOTHING. How it got hung up on that one cell I cannot fathom.
The Freeze Panes command works normally if I apply it manually. Any cell, anywhere, it works as expected.
View 1 Replies
View Related
May 27, 2010
I am trying to include code into the end of my macro that will look in all worksheets and freeze pane row $1:$1
View 9 Replies
View Related
Dec 31, 2008
I recently added some command buttons to 6 excel files each with about 20 sheets. most of the sheets got 6 new buttons. After I added the buttons I would get memory errors when I treied to open other files. So I upgraded to Office 2003 and Excel 2003 because I read that it had increased memory available. It did not seem to help much, and now on average about evry say 10 minutes Excel hangs for about 1o seconds, sometimes saying "Not Responding" up the top. Then it starts working again. MY PC has Server 2003 and 1GHz and 512MB of Ram. Here is some info:
=INFO("memavail")1048576
=INFO("memused")50769256
=INFO("totmem") 51817832
=INFO("numfile") 173
=INFO("osversion")Windows (32-bit) NT 5.02
=INFO("release") 11.0
I do not think the PC memory or chip is being stressed it seems to be Excel's memory?
View 4 Replies
View Related
Mar 26, 2008
I have tried a couple times using MS Query to retrieve data from some rather complex Excel source files. I seem to be able to load the data into MS Query....and even add criteria and some calculated fields. However, every time I go to Return Data to the spreadsheet....it just hangs.
I'm using Excel 2003. Anyone know of any issues with doing this?
View 9 Replies
View Related
Jan 18, 2014
I am trying to do a MS Query from another sheet in the same workbook
I created a new data source using Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Tried read only checked and unchecked
Workbook is a .xlxs but same error with .xlsm workbook
Workbook is on the desktop
I receive the below error when trying to edit a MS Query table by right clicking Table>Edit Query
ODBC Excel Driver Login Failed
The Microsoft Access database engine cannot open or write to the file "(unknown)". It is already opened exclusively by another user, or you need permission to view and write its data.
How to use MS Query in the same workbook?
View 5 Replies
View Related
May 28, 2014
I am attempting to ensure the functionality of my workbook with queries to work on other computers (regardless whether it is a PC or MAC). It works perfectly fine on my computer but when I email the same workbook to a friend, it will not execute the same query.
What could be the issue here? Is there a specific line to 'migrate' the query table(s) with the workbook?
View 1 Replies
View Related
Jun 8, 2009
I've got a Workbook with multiple sheets, which are unhidden one by one (as the last item of data is added to each sheet, Cell L49 turns to "Yes" which in turn populates a variable - this is then used to determine which sheets are visible and which are hidden)
This works fine in a Workbook Open event (ie, get to L49 being "Yes", close and re-open and it's fine) but not how I'd like it in the SheetChange event
I'm using a UserForm which opens on a double-click. This then populates data. But it's not triggering the SheetChange action. I can go to a cell and type something in to make things work, but this is messy...
View 9 Replies
View Related
Nov 10, 2009
I have a workbook_open macro that refreshes a query. works great when opened directly from Excel 2007, but when I use Task Scheduler to call the sheet or call Excel the query will not refresh...macro runs but no refresh.
View 4 Replies
View Related
Aug 26, 2006
I am trying to find a way to automatically do a query and dump the data into a sheet when a previously created workbook is open. I know how to get the data (use the Tools, Import External Data option to retrieve data from Access) but what I don't know is how to make it do it automatically when the workbook is open.
View 2 Replies
View Related
Mar 6, 2012
I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.
View 9 Replies
View Related
Nov 13, 2009
I am trying to run an SQL query to the worksheet of the workbook opened. This is worth noting, that this code is being called multiple times. Suppose if I have around 100 items in a listview, any item I will check or uncheck, this query will be fired. I tried selecting the five items, and the following code ran five times, and the excel ghost instance appeared.
View 4 Replies
View Related
Oct 13, 2013
I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.
I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.
View 2 Replies
View Related
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 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.
View 3 Replies
View Related
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
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
View 2 Replies
View Related
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
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
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
Dec 17, 2007
I have multiple sheets as part of my excel calculations which refer to the first sheet in the group. Each one calculates different values for me based on the numbers I put into the sheet.
So to put in these numbers, I insert new rows to the start of the sheet which is what I always want it to calculate. Some of the calculations are comparative to other points from my original page. So I might ask it to go back and compare to 3 months ago or 6 months ago.
Long story short, when I insert those new rows, the formula on the other pages shift down to stay on the previous days data. How can I lock it in so it stays focused on just the cell I tell it to reguardless of how I move those cells around. So when i hit insert, I still want it focused on that sheets A1 and NOT A2.
I attempted to look up the help section but since I am having a hard time putting this into words, help was not much "help".
View 9 Replies
View Related
Jan 15, 2009
I tried highlighting the top 2 rows and clicking 'freeze panes' but it didn't seem to freeze correctly
View 3 Replies
View Related
Nov 20, 2009
Just wondering if I can lock a worksheet to display the content within the window only. Eg 22 Rows by 6 Columns? Therefore stopping people tampering or moving objects?
View 10 Replies
View Related
Nov 23, 2013
i want freeze only one cell. I have a button. I want keep that button permanently in a place. i am planning to freeze a cell where the button is located.
View 3 Replies
View Related
Nov 3, 2009
I'm trying to get one of my macros to freeze panes in several worksheets. For some reason though, sometimes it freezes the panes in the wrong place. What could be causing this? My code basically looks like this:
View 4 Replies
View Related
Jul 7, 2006
when ever it runs it brings up the print preview of the correct sheet (looks just like i wanted) but the vba object stays infront of the print preview window and locks up i cannot close it and cannot access the print preview window because it is blocked by the vba object even though i can see the print preview in the backbround.
i have tried asding "unload me" after the "activesheet.printpreview" line but does not make any differnce i end up having to close excel via task manager and reopen it.
Private Sub cmdPrtPreview_Click()
On Error Resume Next
Sheets("invoice").Select
ActiveSheet.PrintPreview
End Sub
View 5 Replies
View Related
Oct 16, 2009
I am wanting to freeze the first column on my spreadsheet (easy) but also the top few rows. Every time I try (am using 2007) it seems to make a strange selection where it splits the screen exactly into 4 equal parts rather than just the first column and top 4 rows.
View 3 Replies
View Related
Jan 28, 2013
Is there a way to freeze specific panes? I know that you can use freeze panes to freeze cells to the left and above a specified cell but I am trying to freeze specific cells. For instance I want to freeze cells A1 through K25 so that when I scroll up everything else scrolls up and when I scroll right everything else scrolls right?
View 2 Replies
View Related
Feb 8, 2006
I have a long sample list, (names and telephone no.), once a name and number
are used an outcome code is entered in the end cell like so ...
View 10 Replies
View Related
Sep 6, 2009
Is there a way to freeze both rows and columns? I want to freeze the first five rows 1-5 and columns A-C and have the ability to scroll while having thee rows/columns static?
View 7 Replies
View Related
Apr 12, 2012
I would like to freeze multiple rows, that are NOT together in the spreadsheet. For example. Rows 1-3 are always frozen, but the 4 row that is frozen will vary on where the user is in the spreadsheet. The last row (4th row) in the freeze pane is variable. If I am between rows 1-100 I want rows 1,2,3, and 5 frozen, if I am between rows 100-200, i want rows 1,2,3,101 frozen.
I have multiple tables with different headings in one spreadsheet. My heading for table from row 6-99 is in row 5, and my heading for table 102-200 is in 101.
View 2 Replies
View Related
Mar 16, 2013
I am trying to find any way possible to freeze left side 3 columns and 3 right side visible columns on the screen. and the rest columns in between these freeze columns scrollable on left or right arrow keys as normal.
View 4 Replies
View Related
