SaveCopyAs changes SheetName

[carlo]

Hello All

IMO this is a strange behaviour.

I created an Add-In which manipulates data, deletes all sheets, except
one, then stores it in 2 different folders with SaveCopyAs.
Now, when I open the saved files, the sheetname (of the only Sheet in
the file) has changed to the name of the Workbook.

here's my code: (I left out the data manipulating part and the sheet
deletion, because it happens during saving)

Private Sub Backup_process()
'copy this sheet to a specified destination for backup purposes
'the windows script host object model has to be referenced in order to
succeed.
'--> go to Tools->References and check "windows script host object
model"

Dim objFSO As FileSystemObject
Dim strDestfolder As String
Dim strNewFileName As String
Dim arr_path As Variant
Dim str_path As String

Set objFSO = New FileSystemObject

FName = "C:\"

ActiveWorkbook.SaveCopyAs FName & "Data\LinkVC.xls"

strDestfolder = FName & "Backup\" & Format(Now(), "yyyymm")
strNewFileName = "LinkVC_" & Format(Now(), "yyyymmddhhmmss") & ".xls"

If Not objFSO.FolderExists(strDestfolder) Then
arr_path = Split(strDestfolder, "\")
For Each path_ In arr_path
str_path = str_path & path_ & "\"
If Not objFSO.FolderExists(str_path) Then
MkDir (str_path)
End If
Next path_
End If

ActiveWorkbook.SaveCopyAs strDestfolder & "\" & strNewFileName

End Sub

Does that make sense to any of you?

Appreciate any thoughts.

Thanks in advance

Carlo

 

[carlo]

Hello All

IMO this is a strange behaviour.

I created an Add-In which manipulates data, deletes all sheets, except
one, then stores it in 2 different folders with SaveCopyAs.
Now, when I open the saved files, the sheetname (of the only Sheet in
the file) has changed to the name of the Workbook.

here's my code: (I left out the data manipulating part and the sheet
deletion, because it happens during saving)

Private Sub Backup_process()
'copy this sheet to a specified destination for backup purposes
'the windows script host object model has to be referenced in order to
succeed.
'--> go to Tools->References and check "windows script host object
model"

Dim objFSO As FileSystemObject
Dim strDestfolder As String
Dim strNewFileName As String
Dim arr_path As Variant
Dim str_path As String

Set objFSO = New FileSystemObject

FName = "C:\"

ActiveWorkbook.SaveCopyAs FName & "Data\LinkVC.xls"

strDestfolder = FName & "Backup\" & Format(Now(), "yyyymm")
strNewFileName = "LinkVC_" & Format(Now(), "yyyymmddhhmmss") & ".xls"

If Not objFSO.FolderExists(strDestfolder) Then
arr_path = Split(strDestfolder, "\")
For Each path_ In arr_path
str_path = str_path & path_ & "\"
If Not objFSO.FolderExists(str_path) Then
MkDir (str_path)
End If
Next path_
End If

ActiveWorkbook.SaveCopyAs strDestfolder & "\" & strNewFileName

End Sub

Does that make sense to any of you?

Appreciate any thoughts.

Thanks in advance

Carlo

I just found out, that it doesn't happen all the time.
But i don't see any pattern at all.

In the function prior Backup_process() i change the sheetname to
"LinkVC".
As the workbookname of the first save is LinkVC.xls i cannot find out
if it changes or not, but the second Save, stores the workbook and
names the worksheet LinkVC_20080207090000 which is correct for the
File, but not for the Sheet.

Now I changed the manually adjusted name to "LinkVCbla" and it works
fine, both saved files have the correct names, and the sheets are both
called "LinkVCbla".

If I don't change the sheetname, the same thing happens like in the
first scenario (where i change the sheet name to LinkVC)

I'm getting more and more confused!

In order to give you all the information I can, here is the function
that calls Backup_Process:

'----------------------------------------------------------------------
Sub LinkVCCleanUp()

Dim SH As Worksheet
Dim WSH As Worksheet
Dim Col As Integer
Dim VCodeCol As Integer, CCodeCol As Integer

Set SH = ActiveSheet

Col = 6
VCodeCol = 7
CCodeCol = 3

For i = SH.Cells(65536, Col).End(xlUp).Row To 1 Step -1

If SH.Cells(i, Col) = "" Or SH.Cells(i, Col) = "$B6H<o%3!<%I(B" Then

SH.Rows(i).Delete

End If

Next i

For i = SH.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
If i <> VCodeCol And i <> CCodeCol Then
SH.Columns(i).Delete
End If
Next i

Rows(1).Insert shift:=xlDown
SH.Cells(1, 1).Value = "Customer"
SH.Cells(1, 2).Value = "Vendor"
SH.Name = "LinkVCbla"

'Delete Sheets
Application.DisplayAlerts = False

For Each WSH In ActiveWorkbook.Sheets
If WSH.Name <> SH.Name Then
WSH.Delete
End If
Next WSH

Call Backup_process

Application.DisplayAlerts = True

End Sub
'----------------------------------------------------------------------

Thanks again
Carlo

 

[carlo]

I just found out, that it doesn't happen all the time.
But i don't see any pattern at all.

In the function prior Backup_process() i change the sheetname to
"LinkVC".
As the workbookname of the first save is LinkVC.xls i cannot find out
if it changes or not, but the second Save, stores the workbook and
names the worksheet LinkVC_20080207090000 which is correct for the
File, but not for the Sheet.

Now I changed the manually adjusted name to "LinkVCbla" and it works
fine, both saved files have the correct names, and the sheets are both
called "LinkVCbla".

If I don't change the sheetname, the same thing happens like in the
first scenario (where i change the sheet name to LinkVC)

I'm getting more and more confused!

In order to give you all the information I can, here is the function
that calls Backup_Process:

'----------------------------------------------------------------------
Sub LinkVCCleanUp()

Dim SH As Worksheet
Dim WSH As Worksheet
Dim Col As Integer
Dim VCodeCol As Integer, CCodeCol As Integer

Set SH = ActiveSheet

Col = 6
VCodeCol = 7
CCodeCol = 3

For i = SH.Cells(65536, Col).End(xlUp).Row To 1 Step -1

If SH.Cells(i, Col) = "" Or SH.Cells(i, Col) = "$B6H<o%3!<%I(B" Then

SH.Rows(i).Delete

End If

Next i

For i = SH.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
If i <> VCodeCol And i <> CCodeCol Then
SH.Columns(i).Delete
End If
Next i

Rows(1).Insert shift:=xlDown
SH.Cells(1, 1).Value = "Customer"
SH.Cells(1, 2).Value = "Vendor"
SH.Name = "LinkVCbla"

'Delete Sheets
Application.DisplayAlerts = False

For Each WSH In ActiveWorkbook.Sheets
If WSH.Name <> SH.Name Then
WSH.Delete
End If
Next WSH

Call Backup_process

Application.DisplayAlerts = True

End Sub
'----------------------------------------------------------------------

Thanks again
Carlo- Hide quoted text -

- Show quoted text -

Ok, i found out what happened.....sorry for any inconvenience.

My coworker made me realize, that the file was in txt-format, saved
as .xls-file.
So by saving with SaveCopyAs i saved a txt-format which automatically
takes the workbook name as worksheet name.

Thanks anyways

Carlo