| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro creates additional datafiles when saving copies of workb
Hi Pete,
Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
My nerves are already ground to a fine powder.
I'll keep you informed! Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
Norman,
I created a brand new workbook and typed everything else in again - NO copying and pasting from the original workbook at all. I saved all the code in Notepad, created new modules and pasted all the code back in - and I STILL create duplicate files - not only when I run my macro, but when I do "File Save As" from the menu, too. I might post on the normal Excel board, to see if anyone has any ideas about what could be causing this corruption, as it certainly doesn't seem to be my code - it happens anyway! Regards Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
Hi Peter,
Try closing Excel, rebooting the system and then deleting files and sub-folders from your temp folder. A quick way of navigating to the temp folder : Start | Run | %temp% | OK You might also clear files from the "C:\Windows\Temp" folder. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I created a brand new workbook and typed everything else in again - NO copying and pasting from the original workbook at all. I saved all the code in Notepad, created new modules and pasted all the code back in - and I STILL create duplicate files - not only when I run my macro, but when I do "File Save As" from the menu, too. I might post on the normal Excel board, to see if anyone has any ideas about what could be causing this corruption, as it certainly doesn't seem to be my code - it happens anyway! Regards Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
Hi, Norman,
The Temp files don't appear in my Temp folder - they appear in the same folder as the one where the Save As workbook is created - but I'll try this anyway. Pete "Norman Jones" wrote: Hi Peter, Try closing Excel, rebooting the system and then deleting files and sub-folders from your temp folder. A quick way of navigating to the temp folder : Start | Run | %temp% | OK You might also clear files from the "C:\Windows\Temp" folder. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I created a brand new workbook and typed everything else in again - NO copying and pasting from the original workbook at all. I saved all the code in Notepad, created new modules and pasted all the code back in - and I STILL create duplicate files - not only when I run my macro, but when I do "File Save As" from the menu, too. I might post on the normal Excel board, to see if anyone has any ideas about what could be causing this corruption, as it certainly doesn't seem to be my code - it happens anyway! Regards Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
Hi Peter,
The Temp files don't appear in my Temp folder I assume that you mean the temp files created when you save the problematic file, not the temp files generally created by Excel. If this assumption is correct, delete these files in addition to clearing the two folders indicated in my last post. If my assumption is incorrect, and you are saying that all temp files are being created in the workbook's folder, what is the full name and path of the folder? --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, The Temp files don't appear in my Temp folder - they appear in the same folder as the one where the Save As workbook is created - but I'll try this anyway. Pete "Norman Jones" wrote: Hi Peter, Try closing Excel, rebooting the system and then deleting files and sub-folders from your temp folder. A quick way of navigating to the temp folder : Start | Run | %temp% | OK You might also clear files from the "C:\Windows\Temp" folder. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I created a brand new workbook and typed everything else in again - NO copying and pasting from the original workbook at all. I saved all the code in Notepad, created new modules and pasted all the code back in - and I STILL create duplicate files - not only when I run my macro, but when I do "File Save As" from the menu, too. I might post on the normal Excel board, to see if anyone has any ideas about what could be causing this corruption, as it certainly doesn't seem to be my code - it happens anyway! Regards Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
D:\Pete's Operations\Personal\Archive
But, when I File Save As'd the file onto a floppy yesterday, a hash file appeared there, too! I'm stumped. I'm wondering whether or not to re-create the workbook on a different PC, althoughI don't get this problem with other workbooks, which would tend to suggest that it isn't my workbook template that is corrupted. I wonder if my Workbook_Change code could be running during the save? Regards Pete "Norman Jones" wrote: Hi Peter, The Temp files don't appear in my Temp folder I assume that you mean the temp files created when you save the problematic file, not the temp files generally created by Excel. If this assumption is correct, delete these files in addition to clearing the two folders indicated in my last post. If my assumption is incorrect, and you are saying that all temp files are being created in the workbook's folder, what is the full name and path of the folder? --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, The Temp files don't appear in my Temp folder - they appear in the same folder as the one where the Save As workbook is created - but I'll try this anyway. Pete "Norman Jones" wrote: Hi Peter, Try closing Excel, rebooting the system and then deleting files and sub-folders from your temp folder. A quick way of navigating to the temp folder : Start | Run | %temp% | OK You might also clear files from the "C:\Windows\Temp" folder. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I created a brand new workbook and typed everything else in again - NO copying and pasting from the original workbook at all. I saved all the code in Notepad, created new modules and pasted all the code back in - and I STILL create duplicate files - not only when I run my macro, but when I do "File Save As" from the menu, too. I might post on the normal Excel board, to see if anyone has any ideas about what could be causing this corruption, as it certainly doesn't seem to be my code - it happens anyway! Regards Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creates additional datafiles when saving copies of workb
Hi Peter,
To eliminate residual doubt, are you saying that *all* temp files are being saved to : D:\Pete's Operations\Personal\Archive or just the temp files created by this particular workbook? Were there any files or sub-folders in the %temp% folder and did you delete them? Were there any files in the Windows\Temp folder and did you also delete those? --- Regards, Norman "Peter Rooney" wrote in message ... D:\Pete's Operations\Personal\Archive But, when I File Save As'd the file onto a floppy yesterday, a hash file appeared there, too! I'm stumped. I'm wondering whether or not to re-create the workbook on a different PC, althoughI don't get this problem with other workbooks, which would tend to suggest that it isn't my workbook template that is corrupted. I wonder if my Workbook_Change code could be running during the save? Regards Pete "Norman Jones" wrote: Hi Peter, The Temp files don't appear in my Temp folder I assume that you mean the temp files created when you save the problematic file, not the temp files generally created by Excel. If this assumption is correct, delete these files in addition to clearing the two folders indicated in my last post. If my assumption is incorrect, and you are saying that all temp files are being created in the workbook's folder, what is the full name and path of the folder? --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, The Temp files don't appear in my Temp folder - they appear in the same folder as the one where the Save As workbook is created - but I'll try this anyway. Pete "Norman Jones" wrote: Hi Peter, Try closing Excel, rebooting the system and then deleting files and sub-folders from your temp folder. A quick way of navigating to the temp folder : Start | Run | %temp% | OK You might also clear files from the "C:\Windows\Temp" folder. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I created a brand new workbook and typed everything else in again - NO copying and pasting from the original workbook at all. I saved all the code in Notepad, created new modules and pasted all the code back in - and I STILL create duplicate files - not only when I run my macro, but when I do "File Save As" from the menu, too. I might post on the normal Excel board, to see if anyone has any ideas about what could be causing this corruption, as it certainly doesn't seem to be my code - it happens anyway! Regards Pete "Norman Jones" wrote: Hi Pete, Rob Boveys's addin is *very* frequently recommended by the conoscenti in this group. Given your problems, I would advocate running the CodeCleaner *first*. Including download, you will just about have time to drink a coffee - which might also assuage your nerves! --- Regards, Norman "Peter Rooney" wrote in message ... Hi, Norman, I'll take a look at it - AFTER I get this thing working again, and AFTER I take 74 backup copies when it does..! :-) Pete "Norman Jones" wrote: Hi Peter, You might first like to download Rob Boveys CodeCleaner addin which is freely downloadable at: http://www.appspro.com/Utilities/Utilities.htm Run the dwnloaded Exe file to invoke an automatic installation routine. Call the CodeCleaner from the Tools menu. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, I'm recreating the workbook as we speak. Hopefully copying and pasting the code won't carry the corruption over. I'll let you know how I go on Thanks again Pete "Norman Jones" wrote: Hi Peter, I've been experiencing application errors all morning with the workbook in question. I think that I would try to recreate your workbook. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Corrupt workbooks can cause strange phenomenons. It would not surprise me if your reported: filename made up of 8 random alphanumeric characters. was caused by corruption When Excel saves a file it produces an intermediate, temporary copy with a name such as you describe. It seems possible that this temporary copy is not being deleted. --- Regards, Norman "Peter Rooney" wrote in message ... Norman, Here it is, although I've been experiencing application errors all morning with the workbook in question. Do you think the fact that the workbook's corrupted in some way might lead to the erroneous file being saved at the same time as the proper one? Pete Sub DefineDatabase() Application.ScreenUpdating = False Set DBSheet = Worksheets("Database") Set DBStart = DBSheet.Range("DatabaseStart") Set DBCR = DBStart.CurrentRegion If DBCR.Rows.Count 1 Then DBCR.Name = "Database" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(DBCR.Rows.Count - 1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(DBCR.Rows.Count - 1, 1).Name = "GroupColumn" DatabaseSize = DBCR.Rows.Count - 1 '----------------------------------------------------------------------------------------- 'You need this in order for the validation routines to work correctly! 'You also need to do it if you want to set up a scrollarea FirstCellAddress = DBStart.Offset(1, 0).Address FinalCellAddress = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Address FirstDBRow = DBStart.Offset(1, 0).Row FirstDBColumn = DBStart.Offset(1, 0).Column FinalDBRow = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Row FinalDBColumn = DBStart.Offset(DBCR.Rows.Count - 1, DBCR.Columns.Count - 1).Column '----------------------------------------------------------------------------------------- Else 'set up single row "Data", "PrefixColumn", "CategoryColumn" and "GroupColumn" ranges DBStart.Offset(1, 0).Resize(1, DBCR.Columns.Count).Name = "Data" DBStart.Offset(1, 0).Resize(1, 1).Name = "PrefixColumn" DBStart.Offset(1, 7).Resize(1, 1).Name = "CategoryColumn" DBStart.Offset(1, 9).Resize(1, 1).Name = "GroupColumn" DatabaseSize = 0 End If 'MsgBox (DBFormatCallLocator & " - There is/are " & DatabaseSize & " row(s) in this database" & vbCrLf & vbCrLf & _ "First Row: " & FirstDBRow & vbCrLf & _ "Final Row: " & FinalDBRow & vbCrLf & _ "First Column: " & FirstDBColumn & vbCrLf & _ "Final Column: " & FinalDBColumn) 'CellsToAllow = FirstCellAddress & ":" & FinalCellAddress 'MsgBox (CellsToAllow) 'DBSheet.ScrollArea = CellsToAllow End Sub Sub YearWeekResourceName() Set WeekNumber = DBSheet.Range("WeekNumber") Set Year = DBSheet.Range("Year") Set ResourceName = DBSheet.Range("ResourceName") End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel creates multiple temp files when saving | Excel Discussion (Misc queries) | |||
| saving macro from workbook to Personal Macro Workbook | Excel Discussion (Misc queries) | |||
| Saving a document creates a shortcut | Excel Discussion (Misc queries) | |||
| Saving Macro to Personal Workbook | Excel Discussion (Misc queries) | |||
| Saving a workbook as PDF in Macro | Excel Programming | |||
Hybrid Mode
