Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't open workbook
Hi,
I've been pulling my hair out for the last couple of days over this one, hopefully someone can help me out. - running Excel 97. My routine establishes some temporary links to other workbooks (about 100), extracts a small amount of data, generates a simple report then erases all the links. So, at the end of the routine, all that remains is 1 sheet with 6 cells containing data. The problem starts if I run this routine 3 or 4 times in succession, and save it, and close. The file size increases by about 4K. If I open it again, then save without running the macro, it drops back to it's original size, about 60k. So during the normal process of development, a few cycles of running the macro, saving and closing - all at once I can't open the file, and get an error message from Excel "This program has performed an illegal operation and will be shut down". Nothing I do permits me to open that file again, which now has increased to 72k. I have a suspicion that it has something to do with those links, even though I'm clearing them at the end of the routine. Perhaps clearing the cells that contain the links isn't sufficient, and I should be taking an additional step. Thanks for any help on this, Regards, Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't open workbook
Hi Dave,
Posting your code would help would address any code issues you might have that could be causing the problem. Alan "The only dumb question is a question left unasked." "Dave Unger" wrote: Hi, I've been pulling my hair out for the last couple of days over this one, hopefully someone can help me out. - running Excel 97. My routine establishes some temporary links to other workbooks (about 100), extracts a small amount of data, generates a simple report then erases all the links. So, at the end of the routine, all that remains is 1 sheet with 6 cells containing data. The problem starts if I run this routine 3 or 4 times in succession, and save it, and close. The file size increases by about 4K. If I open it again, then save without running the macro, it drops back to it's original size, about 60k. So during the normal process of development, a few cycles of running the macro, saving and closing - all at once I can't open the file, and get an error message from Excel "This program has performed an illegal operation and will be shut down". Nothing I do permits me to open that file again, which now has increased to 72k. I have a suspicion that it has something to do with those links, even though I'm clearing them at the end of the routine. Perhaps clearing the cells that contain the links isn't sufficient, and I should be taking an additional step. Thanks for any help on this, Regards, Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't open workbook
Hi Alan,
Here's a listing of the code - it hasn't had much refinement yet, but it should illustrate the problem. Basically, it stores a directory listing in a worksheet, then sets up a link to each of those files to test whether it falls within a certain date, and if it does, extract some data for a report Restating the problem - every time I run the macro, and do a save, the file gets a bit larger, by about 4k. If I re-open it, and do a save without running the macro, it drops back to its original size, about 56k. So, it seems I'm Ok until it hits a critical size, and then I can't open it any more. That seems to be about 72k, and takes about 10 runs in succession, then a save, to produce that. I'd be extremely grateful for any help on this. Regards DaveU Option Explicit Dim rng1 As Range, Cell1 As Range, RowCnt As Integer Public dt1 As Date, dt2 As Date Dim wk1 As Worksheet, wk2 As Worksheet, wk3 As Worksheet Dim X As Long, Y As Long, Z As Long Const MyPath As String = "C:\Documents and Settings\Dave\Desktop \NewTom\" Sub EntryMain() Set wk1 = Worksheets("Report") Set wk2 = Worksheets("Selected") Set wk3 = Worksheets("List") wk1.Cells.ClearContents wk2.Cells.ClearContents wk3.Cells.ClearContents: Application.ScreenUpdating = False Call DirList 'get listing of files Call Selected 'select files that fall between dates Call GetReport 'get results Application.ScreenUpdating = True End Sub Sub Selected() Dim str As String wk3.Activate Set rng1 = Intersect(Columns(1), ActiveSheet.UsedRange) dt1 = "10/4/6": dt2 = "1/31/07" RowCnt = 1 wk2.Activate For Each Cell1 In rng1 str = "= '" & MyPath & "[" & Cell1.Text & "]Sheet1'" Cells(RowCnt, 2).Formula = str & "!C4" 'date If Cells(RowCnt, 2) = dt1 And Cells(RowCnt, 2) _ <= dt2 Then Cells(RowCnt, 1) = Cell1.Text 'file name Cells(RowCnt, 3).Formula = str & "!B38" 'cars count RowCnt = RowCnt + 1 Else Cells(RowCnt, 2) = "" 'not this file End If Next Cell1 End Sub Sub GetReport() wk2.Activate If Range("A1") = "" Then MsgBox "No files found meeting search criteria . . .": Exit Sub Set rng1 = Intersect(Columns(3), ActiveSheet.UsedRange) X = Application.Sum(rng1) wk1.Cells(1, 1) = "Report for " & dt1 & " to " & dt2 wk1.Cells(3, 1) = "Total Cars =" wk1.Cells(3, 3) = X wk1.Activate 'cleanup wk2.Cells.Clear wk3.Cells.Clear End Sub Sub DirList() Dim r As Long, direct As String, F As String wk3.Activate r = 1 direct = MyPath & "STR*.xls" 'MyPath in declarations F = Dir(direct) Cells(r, 1) = F Do While F < "" F = Dir If F < "" Then r = r + 1 Cells(r, 1) = F End If Loop End Sub On Jan 31, 2:27 pm, Alan wrote: Hi Dave, Posting your code would help would address any code issues you might have that could be causing the problem. Alan "The only dumb question is a question left unasked." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: Open closed workbook/Search data tables/Return data to open workbook | Excel Discussion (Misc queries) | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
how do i open a data workbook when i open a timesheet workbook | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
How do you program so that Workbook B cannot be open unless Workbook A is open? Plus I need to validation | Excel Programming |