Moving worksheet causes corruption

[VegasNath]

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.

Capture.GIF

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.

[rory]

Sounds to me like your database tool is messing things up. Hard to be more specific without a sample of what it creates.

[Jan Karel Pieterse]

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.

[VegasNath]

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:

Code: Select all

Dim n As Name
     For Each n In ActiveWorkbook.Names
          n.Delete
     Next n

Due to the "square", I get the following error:

Capture1.GIF

However, I can manually delete the names in the name manager. Can anyone suggest a way to delete these using code please?

[sdckapr]

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:

Code: Select all

Dim i As Integer
For i = 1 To ActiveWorkbook.Names.Count
  ActiveWorkbook.Names(i).Delete
Next i

Steve

[Jan Karel Pieterse]

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?

[Jan Karel Pieterse]

Alternatively, can you show us the code that handles the sheet copying? Maybe you're better off just transferring values directly.

[VegasNath]

Thankyou for all replies.

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

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.

Capture3.GIF

[Jan Karel Pieterse]

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