XL2007.
Hi,
I have a daily automated (vba) procedure that involves copying worksheets that have been created by a database tool into other workbooks. I am regularly seeing the following dialogue since moving up to 2007.
The odd thing is that this does not happen every day, just sometimes. I have no idea what the square represents?
However, if I click yes and the sheet is copied to the destination file (and saved), next time the destination file is opened, the file is corrupt and must be re-saved, which I do (save-as itself). Close and re-open, all is ok again.
The only thing that i can think of is print area settings, as a possible cause. I am going to try deleting all named ranges in the source file prior to copying to the destination file, to see if that solves the issue, unless anyone has any other suggestions? TIA.
Moving worksheet causes corruption
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Moving worksheet causes corruption
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Moving worksheet causes corruption
Sounds to me like your database tool is messing things up. Hard to be more specific without a sample of what it creates.
Regards,
Rory
Rory
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Moving worksheet causes corruption
Both the target file and the source file contain the same range name, which is why Excel prompts to ask you which one you want kept (in the target). But, since the range name contains an illegal character, it is a corrupt range name which -I think- is causing the crash subsequently.
To get a chance to rename that range name, toggle your workboook from A1 ref style to r1c1 ref style (Excel options, FOrmulas tab). Excel should prompt you for a new name.
To get a chance to rename that range name, toggle your workboook from A1 ref style to r1c1 ref style (Excel options, FOrmulas tab). Excel should prompt you for a new name.
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Moving worksheet causes corruption
Ok, this has driven me nuts today, i seem to be getting nowhere.
The database exports arrive in excel 2007 with valid defined print areas, which somehow become corrupt in excel 2007. They end up as a "square" as the defined name. We never experienced this issue in 2003.
It seems that it is not possible to get the data from the database without the defined print areas, however I don't want them, valid or not.
My idea is to just delete all defined names in the source file(s) prior to copying sheet(s) to the destination workbook(s). I have tried the following:
Due to the "square", I get the following error:
However, I can manually delete the names in the name manager. Can anyone suggest a way to delete these using code please?
The database exports arrive in excel 2007 with valid defined print areas, which somehow become corrupt in excel 2007. They end up as a "square" as the defined name. We never experienced this issue in 2003.
It seems that it is not possible to get the data from the database without the defined print areas, however I don't want them, valid or not.
My idea is to just delete all defined names in the source file(s) prior to copying sheet(s) to the destination workbook(s). I have tried the following:
Code: Select all
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Delete
Next n
However, I can manually delete the names in the name manager. Can anyone suggest a way to delete these using code please?
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Moving worksheet causes corruption
I can't test since I don't have a file with a corrupt name, but how about this, since it doesn't use the invalid name:
Steve
Code: Select all
Dim i As Integer
For i = 1 To ActiveWorkbook.Names.Count
ActiveWorkbook.Names(i).Delete
Next i
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Moving worksheet causes corruption
Unfortunately, I have never found a way to delete corrupt names through the VBA Name object model, in whichever variation.
I do have a tool that can fix names like that, but it delves deep into the dungeons of the Excel file binaries itself to fix them. Don't expect that is the solution you're after.
Which database package is causing this? Sounds like it contains some bugs. Can't you go through the get external data, new database query route to pull the data into Excel, rather than the other way around?
I do have a tool that can fix names like that, but it delves deep into the dungeons of the Excel file binaries itself to fix them. Don't expect that is the solution you're after.
Which database package is causing this? Sounds like it contains some bugs. Can't you go through the get external data, new database query route to pull the data into Excel, rather than the other way around?
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Moving worksheet causes corruption
Alternatively, can you show us the code that handles the sheet copying? Maybe you're better off just transferring values directly.
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Moving worksheet causes corruption
Thankyou for all replies.
This is where I am currently. The following code works:
This is an OK (hopefully short term) workaround. If only I could autofill the dialogue (shown below) then all would be sorted. The macro recorder does not capture any code to fill the dialogue box though.
This is where I am currently. The following code works:
Code: Select all
Sub Fix_Corrupt_Defined_Names()
Dim n As Name
'Switch to R1C1
Application.ReferenceStyle = xlR1C1
'Dialogue automatically appears here to rename corrupt names
'User has to change corrupt names to legal names manually
'Switch Off R1C1
Application.ReferenceStyle = xlA1
'Delete ALL defined names
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Moving worksheet causes corruption
Have you double-checked where these corrupted names are located:
- in the workbook generated by the database program
- in the target wb
- in both
If your target wb is free of these range names, I'd do the following:
- INsert a new worksheet in the target sheet
- use copy/paste special formats to format the new sheet (if needed)
- use code like this to transfer just the values:
Dim vValues as variant
vValues=SourceSht.UsedRange.Value
TargetSht.Range("A1").Resize(UBound(vValues,1),UBound(vValues,2)).Value=vValues
- in the workbook generated by the database program
- in the target wb
- in both
If your target wb is free of these range names, I'd do the following:
- INsert a new worksheet in the target sheet
- use copy/paste special formats to format the new sheet (if needed)
- use code like this to transfer just the values:
Dim vValues as variant
vValues=SourceSht.UsedRange.Value
TargetSht.Range("A1").Resize(UBound(vValues,1),UBound(vValues,2)).Value=vValues