Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel+Not Enough Memory..
I've got a 2 sheet, 4mb workbook-sheet 2 containing the
raw product data, and sheet 1 acting as a template as such, whereby a user enters a product code, and it looksup the relevant info from sheet 2. Problem..I have some VBA code incorporated into a control box button which, i had hoped, would, on clicking, would simply do effectively a paste special of sheet 1 into a new workbook, temporarily save the workbook, email it off to a pre-specified email address, and then delete the temp workbook. Here is the code i used:- Sub Mail_ActiveSheet() Dim strDate As String Application.ScreenUpdating = False strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss") FName$ = "Confirmation - " & strDate ThisWorkbook.ActiveSheet.Copy With ActiveWorkbook With .ActiveSheet.UsedRange .Copy .PasteSpecial xlPasteValues End With .SaveAs Filename:="c:\" & FName$ & ".xls" .SendMail ", "Confirmation " & strDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub The problem is this-i have 48,000 rows of 4 colums of raw data in sheet 2. But when i press my button, i get the error message "Microsoft Excel Error-Not enough memory". Now the lookups work fine and everything, and if i email the whole workbook, it works fine, its just when i try to email the first sheet i get this message, what am i doing wrong?? Any help greatly appreciated cheers olly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel+Not Enough Memory..
Hi Olly
Try this on the sheet Sub Mail_ActiveSheet1() Dim strDate As String ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _ & " " & strDate & ".xls" ActiveWorkbook.SendMail ", _ "This is the Subject line" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Olly" wrote in message ... I've got a 2 sheet, 4mb workbook-sheet 2 containing the raw product data, and sheet 1 acting as a template as such, whereby a user enters a product code, and it looksup the relevant info from sheet 2. Problem..I have some VBA code incorporated into a control box button which, i had hoped, would, on clicking, would simply do effectively a paste special of sheet 1 into a new workbook, temporarily save the workbook, email it off to a pre-specified email address, and then delete the temp workbook. Here is the code i used:- Sub Mail_ActiveSheet() Dim strDate As String Application.ScreenUpdating = False strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss") FName$ = "Confirmation - " & strDate ThisWorkbook.ActiveSheet.Copy With ActiveWorkbook With .ActiveSheet.UsedRange .Copy .PasteSpecial xlPasteValues End With .SaveAs Filename:="c:\" & FName$ & ".xls" .SendMail ", "Confirmation " & strDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub The problem is this-i have 48,000 rows of 4 colums of raw data in sheet 2. But when i press my button, i get the error message "Microsoft Excel Error-Not enough memory". Now the lookups work fine and everything, and if i email the whole workbook, it works fine, its just when i try to email the first sheet i get this message, what am i doing wrong?? Any help greatly appreciated cheers olly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel+Not Enough Memory..
-----Original Message----- Hi Olly Try this on the sheet Sub Mail_ActiveSheet1() Dim strDate As String ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss") ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _ & " " & strDate & ".xls" ActiveWorkbook.SendMail ", _ "This is the Subject line" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl Hi Ron Cheers for that, but now getting this message Run Time Error 1004 Select method of range class failed any ideas ? cheers olly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel+Not Enough Memory..
-----Original Message----- Hi Olly Try this on the sheet Sub Mail_ActiveSheet1() Dim strDate As String ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss") ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _ & " " & strDate & ".xls" ActiveWorkbook.SendMail ", _ "This is the Subject line" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl and now its back to out of memory error .. hmmm olly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
not enough memory to print excel ws in 7 | Excel Discussion (Misc queries) | |||
XL 2007 - Out of Memory - memory leak/bug? | Excel Discussion (Misc queries) | |||
Excel VB Out of Memory | Excel Discussion (Misc queries) | |||
Memory Leak in Excel | Excel Discussion (Misc queries) | |||
no enough memory to run excel viewer | Excel Discussion (Misc queries) |