Crashing between 2003 & 2007

[T4FF]

Hey all

I've been putting together an automated spreadsheet that has external data sources to SQL. I have been having some strange problems with it between versions of Excel. Unfortunately, the company's standard is 2003 but quite a lot of the company use 2007. I have been developing this report on 2007, the server that runs the automated version of the spreadsheet runs 2003 (and cannot be upgraded beyond 2003 as it is running Server 2000!).

The template opens fine on my 2007 machine and all development work has been run on my machine including the downloading of data. The VBA script runs something like

refresh datasources
paste value =today() fields
save as
save as (i.e. to two locations)

Bit more than that, but that in a nutshell. The quirk which I don't understand, is the once the script has been run on the server in 2003, the outputs won't open in 2007 - Excel crashes twice then recovers it by stripping all data connections, formatting etc.

The recovered spreadsheet is very basic - simply formulas etc - and couldn't be used as a report so I need to try to resolve this issues between versions. The only thing I can think of is that there is some conversion issues with the data connections - i.e. it's ok to go from 2007 to 2003 but not the other direction.

So I'm after one of two things....

Either some insight into what might be doing this? Is it data connections as I suspect? Might it be something else?

Or, what I want to do is add a command to the script that deletes the data connections and I'm not entirely sure how I would go about doing this. I can't even work out how you delete a connection manually in Excel 2003!?!? In 2007, I've recorded a macro of me deleting the connections and the command returned is:

ActiveWorkbook.Connections("[CONNECTION NAME").Delete

However, if I try to run this in 2003 it returns the error "Object doesn't support this property or method.

I'm not the best with VBA but from that error message, I'm assuming that the object identification is correct, but the .Delete is not.

Can anyone offer any help?

Thanks

[romperstomper]

There is no Connections property of a workbook in 2003, which is why that code is failing. As a general rule, you are much better off developing in the earliest version of Excel that you need to support in terms of maintaining compatibility. (unfortunately maintaining compatibility between 2007 and earlier versions seems to be something of a minefield). Do you have SP2 applied to all your 2007 installations?

[T4FF]

The main problem isn't code failing? That is me trying to get a fix together.

The problem I am trying to resolve is the output crashing 2007 on open? In 2003 it works flawlessly.

[romperstomper]

Without seeing any code or a workbook, it's pretty difficult to troubleshoot what might be happening. SP2 is also relevant as it helps with a few similar issues.

[T4FF]

Ok here's the script in full. Since last response, I've added some more code having had another think about how to solve this. I've added the Range("A3").QueryTable.Delete bits to this which does what I want (it removes the links to SQL) and lo and behold, the spreadsheet now opens in 2007! It still comes up with an error message: "File Error: Data may have been lost." I've had a quick scan of the file and there isn't any data missing but it isn't great that it still says that as this isn't too reassuring for users.

I must add, I know that it is pointless being broken down to different subs but I have inherited this and it was a lot more complex, doing a lot more pointless actions, I just wanted to get it working first and then tidy it up properly.

Public OutputFilePath As String
Public OutputFileName As String

Sub Data_Refresh()

Application.ScreenUpdating = False

Sheets("Summary CC0").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC1").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC2").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC3").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC4").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("WorkGroup Summary").Select
Range("A3").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete

Sheets("Exception 1").Select
Range("A3").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete

Sheets("ASR").Select
Range("A1").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete

Application.ScreenUpdating = True

End Sub

Sub Publish()

OutputMISFilePath = Range("MISFilePath")
OutputFilePath = Range("FilePath")
OutputFileName = Range("FileName")

Sheets("Cover Sheet").Select
[a1].Select

ActiveWorkbook.SaveAs Filename:=OutputMISFilePath
'ActiveWorkbook.SaveAs Filename:=OutputFilePath

End Sub
Sub Save()

Windows(OutputFileName).Activate
ActiveWorkbook.Save
Application.Quit

End Sub

Sub Controller()

Data_Refresh
Publish
Save

End Sub

[T4FF]

Ok stupid question time...how do you find out what version / how to date Excel is? I am still at the bottom of my learning curve with 2007

[T4FF]

Ok stupid question time...how do you find out what version / how to date Excel is? I am still at the bottom of my learning curve with 2007

Nevermind, found it...

[T4FF]

I haven't got SP2 or SP3 installed so that may resolve things....TBH I was under the impression that my place managed updates automatically! Will update both SPs and see what that does.

[romperstomper]

I know that it is pointless being broken down to different subs

Not at all. It's far better to have small single-purpose procedures than one giant one - a lot easier to debug and maintain!

[broro183]

hi,

Before installing SP3, have a read of the related section in the below link & perhaps do a quick google search as well...
http://windowssecrets.com/comp/040325/

I would be interested in hearing Romper & RoyUk's (recently viewing this thread) thoughts on the use of Conditional Compilation to allow for the correct code to run based on the Excel version (search VBE's Help files for "Conditional Compilation" or see link for a brief explanation: http://www.dailydoseofexcel.com/arch...ion-arguments/).
Excel's Help Files state:

You can use conditional compilation to run blocks of code selectively, for example, debugging statements comparing the speed of different approaches to the same programming task, or localizing an application for different languages.

You declare a conditional compiler constant in code with the #Const directive, and you denote blocks of code to be conditionally compiled with the #If...Then...#Else directive. The following example runs debug code or production code, based on the value of the conDebug variable.

hth
Rob

[romperstomper]

Hi Rob,
Where does Office XP SP3 come in?

I'm not a big fan of conditional compilation myself (though I don't do a lot of cross-version/platform work) - there were several better alternatives mentioned in that DDoE article. At the end of the day, if you have to support an older version too, it seems like too much trouble to use features that aren't available in older versions, if it can be avoided. Otherwise, keep all version-specific code in its own module and call the appropriate module from a parent sub that checks the Application.Version

[broro183]

T4FF,
To help others who may have a similar problem, can you please post your solution?

Romperstomper
Ooopps!
tbh, on re-reading the whole thread, I'm not sure where SP3 for Office XP comes into it - that'll teach me for skimming posts & not giving them my complete attention!

[hijack]
At the risk of being a thread hijacker...

...keep all version-specific code in its own module...

Yes, I like that idea. Unfortunately, I may need to have code that is compatible for both versions. I've recently come across some limited specific cases where some named arguments in 2007 aren't available in earlier versions (& I'm sure there were a couple in the vice versa situation?) and I had been considering putting repetitive blocks of conditional formatting throughout my code. I'll reread the link & chew over the other approaches.
[/end hijack]

Rob

[romperstomper]

Unfortunately, I may need to have code that is compatible for both versions. I've recently come across some limited specific cases where some named arguments in 2007 aren't available in earlier versions (& I'm sure there were a couple in the vice versa situation?) and I had been considering putting repetitive blocks of conditional formatting throughout my code. I'll reread the link & chew over the other approaches.

That was my point. All the version-specific code goes in its own module. For example you have one module with 2003 code and one with 2007 code, plus a main common module. The procedures in the common module do the version check and then call the procedures from the relevant version module.

For conditional formatting it should be pretty much moot if the code is in a workbook since you can only really use the old style CF to maintain compatibility anyway.

[T4FF]

T4FF,
To help others who may have a similar problem, can you please post your solution?

Right now, I haven't got one?

I think I will start again just to make sure I've not confused anyone...

The script below is in a template spreadsheet (an .xls file) that is called by a .vbs file. All data connections in the spreadsheet (that are refreshed below) were created and configured using 2007 but it was previously a 2003 file and is still saved as a 2003 file. The connections are to SQL and use AD to connect - nothing is automatically refreshed, it must be refreshed by the script.

Public OutputFilePath As String
Public OutputFileName As String

Sub Data_Refresh()

Application.ScreenUpdating = False

Sheets("Summary CC0").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("Summary CC1").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("Summary CC2").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("Summary CC3").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("Summary CC4").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("WorkGroup Summary").Select
Range("A3").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("Exception 1").Select
Range("A3").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Sheets("ASR").Select
Range("A1").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select

Application.ScreenUpdating = True

End Sub

Sub Publish()

OutputMISFilePath = Range("MISFilePath")
OutputFilePath = Range("FilePath")
OutputFileName = Range("FileName")

Sheets("Cover Sheet").Select
[a1].Select

ActiveWorkbook.SaveAs Filename:=OutputMISFilePath
'ActiveWorkbook.SaveAs Filename:=OutputFilePath

End Sub
Sub Save()

Windows(OutputFileName).Activate
ActiveWorkbook.Save
Application.Quit

End Sub

Sub Controller()

Data_Refresh
Publish
Save

End Sub

The script is called by a Server running 2003 and the script runs fine - no issues whatsoever. It runs all the code, and outputs to two locations. The template file opens fine on both 2003 and 2007 HOWEVER the output files open fine in 2003 but crashes if opened in 2007. 2007 attempts to recover but then fails and strips the spreadsheet of everything except raw data.

The only thing that I could think of that was more complicated than a bit of formatting or raw data was the connections themselves so my attempt at fixing this was the following (additional entries in red) which was my initial reason for posting as I couldn't think how to clear down the connections:

Public OutputFilePath As String
Public OutputFileName As String

Sub Data_Refresh()

Application.ScreenUpdating = False

Sheets("Summary CC0").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC1").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC2").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC3").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("Summary CC4").Select
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Range("G4").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete
Range("G3").QueryTable.Delete

Sheets("WorkGroup Summary").Select
Range("A3").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete

Sheets("Exception 1").Select
Range("A3").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete

Sheets("ASR").Select
Range("A1").QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial xlValues
[a1].Select
Range("A3").QueryTable.Delete

Application.ScreenUpdating = True

End Sub

Sub Publish()

OutputMISFilePath = Range("MISFilePath")
OutputFilePath = Range("FilePath")
OutputFileName = Range("FileName")

Sheets("Cover Sheet").Select
[a1].Select

ActiveWorkbook.SaveAs Filename:=OutputMISFilePath
'ActiveWorkbook.SaveAs Filename:=OutputFilePath

End Sub
Sub Save()

Windows(OutputFileName).Activate
ActiveWorkbook.Save
Application.Quit

End Sub

Sub Controller()

Data_Refresh
Publish
Save

End Sub

Now that got me halfway there - the file now opens in 2007 BUT it comes up with the error message "File Error: Data may have been lost." Nearly fixed - I can't really provide this to the business with a warning that suggests data has been lost. Is there anything else anyone can think of?

If I'm understanding this correctly, conditional compilation would be writing different code for different versions? I'm not sure if this would solve this because nothing runs on open? If actual code was the offending item, the code would still be there whether it was conditionally compiled or not wouldn't it? Unless I've misunderstood...

Really strange this, I can't think what else I can do short of rewriting into a new spreadsheet in 2007 - I have created an amended version of an old spreadsheet.

[broro183]

hi all,

Blimey, I've had a shocker in this thread...

Romper, Thanks for your comments & sorry, what I had meant to say in post 12 was...
"it is unfortunate but I will need to maintain compatibility for both versions. Your suggestion of a specific module for the version specific code is a good one." Plus where I had typed "conditional formatting" I had actually meant "conditional compilation" but I had been on an incorrect autopilot while typing

T4FF,
Sorry, I thought your post of

Nevermind, found it...

meant that you had the complete solution, not just how to identify the current Excel version (my lack of attention again!).

------------

Anyway, back to your problem...

If I'm understanding this correctly, conditional compilation would be writing different code for different versions? I'm not sure if this would solve this because nothing runs on open? If actual code was the offending item, the code would still be there whether it was conditionally compiled or not wouldn't it? Unless I've misunderstood...

Don't worry, I think it was me who misunderstood

I haven't got SP2 or SP3 installed so that may resolve things....TBH I was under the impression that my place managed updates automatically! Will update both SPs and see what that does.

Did installing these updates help you?
(for a few comments about query tables see http://news.office-watch.com/t/n.aspx?a=849

Ignoring any version specificity, does the below work for you?

Please Login or Register  to view this content.

hth
Rob

[T4FF]

Hi both I have been messing around with different ways of solving this and have managed to get to a point where I think I can give you an explanation of why this happens. I have managed to fix it - or perhaps this may be more of a workaround but either way, it works.

broro183, thanks for your effort but I've managed to get it to work in another way. Due to time constraints, I won't have the time to play with your suggested code, but perhaps one for a rainy day.

So here it is as far as I can see....


File1
An XLS file, all connections and scripts were created in 2007. Scripted run of the file was carried out on 2003 and an output saved. The output would open in 2003 but crash 2007.

File2
An XLS file, all connections and scripts were created in 2007. Scripted run of the file was carried out on 2003 HOWEVER additional commands were added to remove connections. The output would open in 2003, open in 2007 but with errors (data may be lost).

File3
An XLS, all conncetions were deleted and recreated in 2003. Scripted run of the file was carried out on 2003. The output now opens in both 2003 and 2007!


So basically, I rewrote the file in another XLS that was created in 2003, copied over all the formatting then recreated all the connections in 2003. I don't see any problems at all. My only conclusion...and this is a very uninformed conclusion that one of you might be able to turn into a meaningful one...is that 2007 must add additional info to the connections which when opened and saved in 2003, is dropped, but 2007 then looks for when it reopens, can't find it and crashes. It might be something completely different, but hey either way, I've got it to work in the end!

Thanks for all your help guys.

[broro183]

I'm pleased you've got something working

Can you please mark the thread as solved by editing the prefix of your initial post in this thread?
(You may need to contact a moderator due to the age of the thread - I think the details are in the Forum rules.)

Rob

[T4FF]

I'm pleased you've got something working

Can you please mark the thread as solved by editing the prefix of your initial post in this thread?
(You may need to contact a moderator due to the age of the thread - I think the details are in the Forum rules.)

Rob

Done!

Does anyone have a more technical explanation of why this didn't work? Just out of curiosity more than anything.

[broro183]

Thanks

I don't know the technical reason & some Googling may be your best bet now, but you may (... no promises ) also be able to identify it if you record a macro of when you manually create the connections in 2003, do the same in 2007 & then compare the two sets of recorded code.

Rob