I am at a point where I am running out of RAM for the 2BG allowed in 2007. Does anyone know if Microsoft has a later version of Excel that will allow me to grab onto 3GB? My file sizes are close to .5GB. It seems that I am always pushing the limit of what Microsoft can provide.
View 3 RepliesFatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155. I have following macro, what worked just fine in 2003. Now I'm working with 2007 and I can't set up the Microsoft Visual Basic for Applications Extensibility library. Here's the
View 2 Replies View RelatedI have an Excel spreadsheet that I have developed into an over the top flight calculator/scheduling tool. I am using Excel because developing this prduct into a stand-alone program would be absolutely murder to get approved for use on my network.
Since Excel and Access has already been approved, I began coding this project there.
Now that my calculator functions are nearly complete, I am beginning to develop a database of airfields and other misc. flight data that I and others on my network can see and manipulate. To make my calculator funtion at 100% effectiveness, I will need to use VBA to open the Access DB and pull out specific data. I will also need it to open and display some Access pop-up forms that I have just developed. Does anyone know what code I would use for excel to display and retrieve Access' information?
I am using excel 2007 with XP pro.
I want to include some random numbers in a large multi-tabbed spreadsheet, but I do not want the number to recalculate every time I make another entry on the tab or elsewhere in the workbook. I would like each tab to have one or two random number options, but each should calcualte independently based on their own inputs.
The "randbetween()" function recalcs with every/any change in the workbook.
Specifically, I want to begin with a random percent between 0 and 50% and then add three fixed values (percents) and one different random percent between 0and 30%. then I want to total all of them.
Random % (0-50) + X% + Y% + Z% + random % (0-30) = total %
I've just suceeded writing some code that collects data from one spreadsheet and places them in another sheet from where it is transferred to an Access 2007 table. It all works fine apart from one little thing: I can't find a way to close down the empty Access window after the data have been transferred.
View 2 Replies View RelatedI have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.
At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue.
This is how I'm connecting to Access:
Code:
strTableName = "TableName"
strDBLocation = "xxxxuserhomexxxxxxxxxxxxDatabaseDatabase Backend.accdb"
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLocation & ";"
Set cn = New ADODB.Connection
cn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
And this is the Query I'm using:
Code:
strSQL = "UPDATE " & strTableName & " "
strSQL = strSQL & "SET [Work Order ID] = '" & Range("B" & r).Value & "', "
strSQL = strSQL & "[JOBSTATUS] = '" & Range("C" & r).Value & "', "
strSQL = strSQL & "[SUBSTATUS] = '" & Range("D" & r).Value & "', "
strSQL = strSQL & "[Job Status] = '" & Range("E" & r).Value & "', "
strSQL = strSQL & "[STATUSDATETIME] = #" & Range("F" & r).Value & "#, "
[Code] ..........
The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.
I'm using both Excel 2007 and Access 2007 on XP.
I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":
Private Sub Workbook_Open()
'My code
End Sub
I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/
I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies View RelatedWhen adding functions to the quick access toolbars in Excel 2007 sometime they do not have an associated icon with the function. The icon shows as a green circle. How to change the icons and also to have a selection of icons like you have had in previous versions of Excel?
View 5 Replies View RelatedIn Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of data into Access, then refresh the Excel file. Most of the time this works, but I've noticed sometimes one column doesn't refresh (even though I can see it in Access). If I modify the field name in the Access query, then refresh the table in Excel the newly named field is added in the in the last column of the table with current data. The column that wasn't refreshing stays the same.
When I originally created the link in Excel, I added various columns with formulas, but do not edit the columns that are imported from Access.
I am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.
The Access table will have three columns:
Part Number|Line|Catalog Code
I would like to import all three columns, but allow the user to select the records imported based on the catalog code.
The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.
I have 2007 versions.
i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.
i am ussing excel 2007.
I am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
How to replicate this procedure in Excel 2007?
I am Generating Excel file with Macro using my asp.net (c#) application.
I am able to generate Excel file in development environment, but in Production it gives following error:
"Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel"
I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below.
1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.
Can i do above changes at runtime (using some code)?
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
View 2 Replies View RelatedI have an Excel file ( named "Classes.xsl"), that has a worksheet (named "RawData") that is layed out something like:
Name Dept Class
John 0547 Class 1
Jane 0368 Class 1
Jim 0368 Class 2
Sue 1235 Class 2
I am trying to get an Access-Like report without utilizing Access. Is there a way to generate a report similar to below using Excel as the data source (could be mail merge, perhaps a macro with a printout) I am not sure which way to try and was hoping someone could point me in the right direction...and possibly provide an example.
Class EnrollmentClass 1John 0547
Jane 0368Class 2Jim 0368
Sue 1235
I have name 28 columns. The range in EACH columns is 6:14000, i.e. the first name is date and the range is from Row B6 to Row B14000.
I am using SumProduct instead of VlookUp to look up values. An example of one of my sumproduct formulas:
SUMPRODUCT(--(Date=$B$6),--(Book=$D$6),PnL___Book)
where the first columns has dates, the next column is bookname and the last column is the profit and loss for each book, i.e. the values that need to be addad.
Now, suddenly I do not get any numbers but just #VALUE! but if I change the range to a lot less, e.g. between Row B6 and B50, it works.
My questions is if this has to do with memory in Excel. Are there limits to how many formulas and how many names you can have before Excel crashes. I am aware that the 28 columns names are very large.
Are there any way of how to get around this, VBA-codes or other formulas that uses less power, not naming columns or what?
I have an excel sheet that has an onclose macro, and on certain computers i get an error message stating out of memory. can anyone explain why this is and how i could avoid it, by possibly clearing the memory first or something?
View 9 Replies View Relatedi have a spreadsheet on Excel 2000, which is 256 column's wide and 4000 long the only formulas are countif which start at row 1000-4000 and are copied to column 256. Just for the one sheet it is 14mb is this normal
View 11 Replies View RelatedI am currently using Excel 2003 to pull from 2 external spreadsheets to compile data. The first sheet (seniority list.xls) being referenced contains all of our employees (approximately 350 rows, but that can vary from 340 to 450) as well as some information (employee ID, full time or part time, etc.). The second sheet being referenced (pay.xls) contains a breakdown of all employees and their pay for a particular bi-weekly period.
What I'm looking to do is:
(1) Find out all of my current employees. I do this by referencing seniority list.xls.
(2) For each employee, find out how many hours they worked each week. I need to do this by referencing pay.xls. Unfortunately, that file works quite differently than seniority list.xls. Unlike the latter, which contains one employee number per row, pay.xls could contain as many or as few rows per employee as their schedule dictates. Each row contains things such as overtime, regular time, double time, alternate regular time, alternate OT, vacation, sick pay, etc. In total, pay.xls could contain as many as approximately 8000 rows. To determine how many hours they work, I'm using this CSE (array) formula:
(Note that the employee ID is in column A of the current sheet.)
I have a large amount of data on NBA teams and players that is extracted from external web pages. In order to have the data shown on separate sheets to do comparisons I have to use many array formulas and from what I have read that takes up a large amount of memory. I'm now at the point where I'm receiving a message that says "Excel cannot complete this task with available resources" when I open the workbook.
Is there any plausible way that I can resolve this situation? I have thought about converting the lookup formulas from arrays (index/match) to vlookups and hlookups.
I'm not as advanced with using Excel as most of you, and I realize I may be at a point to where I will need to use different software to analyze my data, but I would prefer to stick with Excel as it would be difficult to start from scratch and learn how to use a different program.
Using Excel 2007, Windows XP, 4gig ram,
I got this "Insufficient Memory .... close some application" message, once in a while.
I know that i got enough memory to run my Excel.
This is a random problem. Memory as been checked with mem86 and it work fine
Regarding the Memory Leak problem with ADO, does anyone know of a workaround?
The workaround mentioned on Microsoft's website (http://support.microsoft.com/default...;en-us;Q319998) pertains to using ADO to retrieve a recordset from an Excel worksheet, not from an Access Database.
1) Does anyone know if those workarounds would also address retrieving a recordset from an Access Database?
2) If so, is the "SELECT INTO" workaround effective?
Is this Memory Leak issue relatively minor or only affecting Excel 2000? I'm guessing it must be one of those 2 things since the concensus is ADO is superior to MSQuery..
I'm using a Dell P4, 1.6Ghz, 512Mhz of memory (Win2K & Excel 2003), and it's bogging on spreadsheets that aren't that big and complicated (compared to thing's I did years ago, in the days of 386's and Win98)...
It hits the 100% CPU usage and stays there for minutes (especially when saving) on files that are only around 2M in size, yet it's not using much of the memory...
Spreadsheets large or small seem to have little effect on how much memory is being used... is Win2K allocating a fixed % of total memory for Excel to use?... can this be changed, so Excel gets to use more of the available memory?
I've been cleaning up and redesigning my spreadsheets to make them more efficient, but there seems to be a disconnect between my hardware specs and Excel's performance with files that aren't that big ~:-/
Disabling my antivirus software (Nortons) prior to saving, seems to speed it up a little, but this is not something I should be having to do...
One of my apps I run is very large and memory intensive, and there are several subsequent procedures which tend to either bog down or errors out before the program finishes running.
Is there a way to clear the memory out?
And adding more physical memory is not an option, I have 4gigs already and Excel can only utilize 1gig anyway.
I have tried Tools/Options/Security Password to modify, but it can still be saved as a copy to the network which becomes very confusing to many. If I use Tools/Potect Sheet, I need to re-enter the password for all sheets twice. I am trying have it so that everyone is read only, select people can edit (with same password). Password protect all sheets and the workbook with the same password everytime when the workbook is closed
Basically 2 questions
1) Is there a way of remembering a password so that when the workbook is closed it and all sheets are automatically protected with the same password every time
2) Is it possible to have it not copied unless it is opened by an editor
I have been having an internal debate of how my worksheets shoud be configured.
My workbook could potentially contain 300+ worksheets, 10 control buttons and around 20 macros.
Each worksheet contains data in a range ~ A1:AC40 with many blank cells.
Do you think a workbook with this many worksheets would be unmanageable given the Comp Spec of the user PC below?
Most of the cells are simply short text data.
System info
Processor: 2.3Ghz Dual Processor
RAM: 2GB
32bit
I have created an excel tool w/ A LOT of macros in it, and the tool "speaks" to the SQL server (downloads data from it and uploads it back). My big problem now is that every time I run any of the VBA code the amount of RAM that is getting allocated to Excel (2007) is increasing - I am checking it in the Task Manager. So if I work for a long time without closing down Excel - my entire RAM is getting consumed and the system slows down, and sometimes crushes.
I was wondering if there is a way in VBA to clean up memory that is used by Excel.
Do you have any hints? I am using extensively 3 things - screenupdating off; displaymessages off, and calculations off. But this speeds up Excel but doesn't reduce the memory usage.
I have a macro which filters data on one sheet and copies that data onto 34 new sheets . This works perfectly until the 25th pass when Excel advises that there is insufficient memory to continue. I assumed that this was connected to the clipboard and its ability to remember 24 chunks of data so I turned it off but still Excel gave up at the 25th pass. Is there a way that I can clear the clipboard during the macro's execution or is there something else going on?
View 7 Replies View RelatedI have come across one of those annoying 'out-of-memory' errors. See my code below:
Code:
'Dimensions (first rows and cols)
Const frow3 As Integer = 17
Const frow4 As Integer = 19
Const fcol3 As Integer = 10
Const fcol4 As Integer = 11
Sub Extract()
Dim ws As Worksheet, target As Worksheet, meta As Worksheet
[Code] .......
The code executes fine until it gets to End Sub. Some object eats up memory but I don't understand which one.