Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1 range
"DropDownTeams" which is referred to as "TestDataCell" - the cells contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete




Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value = ""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=TestFolderName & Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Hi Peter,

Immediately before the line:

ActiveWorkbook.SaveAs Filename:=TestFolderName ...

paste the diagnostic code:

With testdatacell
MsgBox "testdatacell address = " & .Address(0, 0, , 1) _
& vbTab & "Value = " & .Value
End With

Then see if testdatacell is what you expect it to be in terms of location
and value.


---
Regards,
Norman



"Peter Rooney" wrote in message
...
Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1
range
"DropDownTeams" which is referred to as "TestDataCell" - the cells contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension
and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete




Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value = ""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
& Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Morning, Norman!

All the values are correct - the SaveAs files are created with exactly the
correct filenames, in the correct folder, based on the values in the range.
The problem is that the additional files are creared as well (also in the
correct folder). There is one erroneous file created for each valid one, so
it must be something that's happening during the save process.

Any more thoughts?

Pete

"Norman Jones" wrote:

Hi Peter,

Immediately before the line:

ActiveWorkbook.SaveAs Filename:=TestFolderName ...

paste the diagnostic code:

With testdatacell
MsgBox "testdatacell address = " & .Address(0, 0, , 1) _
& vbTab & "Value = " & .Value
End With

Then see if testdatacell is what you expect it to be in terms of location
and value.


---
Regards,
Norman



"Peter Rooney" wrote in message
...
Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1
range
"DropDownTeams" which is referred to as "TestDataCell" - the cells contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension
and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete




Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value = ""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
& Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Hi Peter,

What is the code for the

DefineDatabase
YearWeekResourceName

routines?

---
Regards,
Norman



"Peter Rooney" wrote in message
...
Morning, Norman!

All the values are correct - the SaveAs files are created with exactly the
correct filenames, in the correct folder, based on the values in the
range.
The problem is that the additional files are creared as well (also in the
correct folder). There is one erroneous file created for each valid one,
so
it must be something that's happening during the save process.

Any more thoughts?

Pete

"Norman Jones" wrote:

Hi Peter,

Immediately before the line:

ActiveWorkbook.SaveAs Filename:=TestFolderName ...

paste the diagnostic code:

With testdatacell
MsgBox "testdatacell address = " & .Address(0, 0, , 1) _
& vbTab & "Value = " & .Value
End With

Then see if testdatacell is what you expect it to be in terms of location
and value.


---
Regards,
Norman



"Peter Rooney" wrote in message
...
Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1
range
"DropDownTeams" which is referred to as "TestDataCell" - the cells
contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by
the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension
and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp
files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete




Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In
Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value =
""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
& Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

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




"Norman Jones" wrote:

Hi Peter,

What is the code for the

DefineDatabase
YearWeekResourceName

routines?

---
Regards,
Norman



"Peter Rooney" wrote in message
...
Morning, Norman!

All the values are correct - the SaveAs files are created with exactly the
correct filenames, in the correct folder, based on the values in the
range.
The problem is that the additional files are creared as well (also in the
correct folder). There is one erroneous file created for each valid one,
so
it must be something that's happening during the save process.

Any more thoughts?

Pete

"Norman Jones" wrote:

Hi Peter,

Immediately before the line:

ActiveWorkbook.SaveAs Filename:=TestFolderName ...

paste the diagnostic code:

With testdatacell
MsgBox "testdatacell address = " & .Address(0, 0, , 1) _
& vbTab & "Value = " & .Value
End With

Then see if testdatacell is what you expect it to be in terms of location
and value.


---
Regards,
Norman



"Peter Rooney" wrote in message
...
Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1
range
"DropDownTeams" which is referred to as "TestDataCell" - the cells
contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by
the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension
and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp
files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete




Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In
Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value =
""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
& Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

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

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

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

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

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

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

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

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

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

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

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

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

Norman,

I've conceded defeat.

I run my save code once, it works fine.

I run it again, it falls over with "document not saved"

I haven't got a clue why, especially as it was fine last week.

I work in Lytham St Annes, so i'm going to do a Reggie Perrin and run into
the sea.

Pete

"Norman Jones" wrote:

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

Hi Peter,

Before you drown yourself (and I seem to recall that Reggie returned), try:

(1) deleting all code (including event code) and modules. What happens now
if you save the book ?

(2) Since you have access to another machine, still with the code deleted,
try saving the book on the second machine.


---
Regards,
Norman



"Peter Rooney" wrote in message
...
Norman,

I've conceded defeat.

I run my save code once, it works fine.

I run it again, it falls over with "document not saved"

I haven't got a clue why, especially as it was fine last week.

I work in Lytham St Annes, so i'm going to do a Reggie Perrin and run into
the sea.

Pete

"Norman Jones" wrote:

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

Norman,

It's sorted.

I'm not quite sure how it happened, but earlier in the week, I changed the
format of the filenames to be created from beginning with an alpha to
beginning with a number. What was happening was, with all the concatenation
that was taking place to build up a filename consisting of labels, spacers
and three values brought in from various places on the worksheet, instead of
creating the filename IN the folder, the macro was creating the filename AS
the folder. Then, the next time the macro was run, the folder didn't exist,
so the document wasn't saved.

It seems to be working now, so i can come in from the sea (it was too cold
anyway)

Thank you VERY much for your persistence and advice, some of which has
contributed to my solving this problem.

Regards

Reggie



"Norman Jones" wrote:

Hi Peter,

Before you drown yourself (and I seem to recall that Reggie returned), try:

(1) deleting all code (including event code) and modules. What happens now
if you save the book ?

(2) Since you have access to another machine, still with the code deleted,
try saving the book on the second machine.


---
Regards,
Norman



"Peter Rooney" wrote in message
...
Norman,

I've conceded defeat.

I run my save code once, it works fine.

I run it again, it falls over with "document not saved"

I haven't got a clue why, especially as it was fine last week.

I work in Lytham St Annes, so i'm going to do a Reggie Perrin and run into
the sea.

Pete

"Norman Jones" wrote:

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.

When excel saves the file, it saves it as a temporary file with a funny name (8
characters--no extension).

If the save is successful, xl will delete the original (or rename it to its
backup name (like "backup of book1.xlk)) and if that's successful, xl will
rename the funny named file to the original's name.

Common things that get blamed for interruptions to this process are antivirus
software poking its head in or network errors--either permissions or physical
problems.

Maybe temporarily disabling the antivirus software would be the easier thing to
test.

Peter Rooney wrote:

Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1 range
"DropDownTeams" which is referred to as "TestDataCell" - the cells contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete

Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value = ""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=TestFolderName & Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

--

Dave Peterson

Hi, I've been having similar problems with a workbook on a network, som
wb's create the temp files some do not, i have been to most of th
machines and turned of the "keep change history" hoping that this cure
the problem so you could try that!

HTH

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=39452

Hi, Simon,

I finally tracked down the problem, and, believe it or not, the problem was
solved when I changed the filetype from XL9795 to XLNormal!
It seems I was somehow saving the workbooks AS the folder, instead of INTO
it, but now it's resolved. I guess it's one of those "I don't know why it
does that, but as long as it does, I'll leave it" things!

Thanks for your tip, I'll bear it in mind for future reference.

Regards

Pete

"Simon Lloyd" wrote:


Hi, I've been having similar problems with a workbook on a network, some
wb's create the temp files some do not, i have been to most of the
machines and turned of the "keep change history" hoping that this cures
the problem so you could try that!

HTH

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=394523

Hi, Dave,

I finally tracked down the problem, and, believe it or not, the problem was
solved when I changed the filetype in the SaveAs command from XL9795 to
XLNormal !
It seems I was somehow saving the workbooks AS the folder, instead of INTO
it, but now it's resolved. I guess it's one of those "I don't know why it
does that, but as long as it does, I'll leave it" things!

Thanks for your advice, I'll bear it in mind for future reference.

Regards

Pete


"Dave Peterson" wrote:

When excel saves the file, it saves it as a temporary file with a funny name (8
characters--no extension).

If the save is successful, xl will delete the original (or rename it to its
backup name (like "backup of book1.xlk)) and if that's successful, xl will
rename the funny named file to the original's name.

Common things that get blamed for interruptions to this process are antivirus
software poking its head in or network errors--either permissions or physical
problems.

Maybe temporarily disabling the antivirus software would be the easier thing to
test.

Peter Rooney wrote:

Good morning, all!

I am using a simple loop to create test data workbooks. There is a 5X1 range
"DropDownTeams" which is referred to as "TestDataCell" - the cells contain
ELS1, ELS2...ELS5.

My macro successfully creates the following workbooks:
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS1.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS2.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS3.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS4.xls
D:\Pete's Operations\Personal\Test\SOFT Report - 0530 - ELS5.xls

the "0530" being the contents of two additional variables generated by the
macro YearWeekResourceName. However, I ALSO get five additional files
created, the same size as the original workbook, with no file extension and a
filename made up of 8 random alphanumeric characters.

Can anyone suggest why these files are being created? Are they temp files,
and, if this is the case, why aren't they being deleted?

Thanks in advance

Pete

Sub SetTestFolderName()
TestFolderName = "D:\Pete's operations\Personal\Test\"
End Sub

Sub TestDataTeam()
For Each TestDataCell In Sheets("DropDownLists").Range("DropDownTeams")
Sheets("Database").Range("HeaderRemarks").Offset(1 , 0).Value =
TestDataCell.Value
Sheets("Database").Range("HeaderSubTeam").Offset(1 , 0).Value = ""
SaveTestData
Next
End Sub

Sub SaveTestData()
SetTestFolderName
DefineDatabase
YearWeekResourceName
'Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=TestFolderName & Year & "-" & WeekNumber
& _
" - SOFT Report - " & TestDataCell.Value & ".xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

--

Dave Peterson