Need to create a linked 2003 sheet from a 2007

[maxxev]

Hi,

We have an application (teklynx labelview if that helps) that I want to link to an excel spreadsheet (as we don't use a database here (as much as I keep pushing for one)).

Problem is we have mixed 2007 & 2003 excel versions on machines.

The software will only link up to a 2003 excel sheet (I can't get it to accept an ODBC or OLE link to the 2007).

The issue with that is that the source workbook (Depot shelf lives - all customers.xlsm) is in .xlsm format due to a link to ANOTHER excel sheet which opens the original 2007 workbook in a hidden window to run some calculations using indirect formula (as I need it to keep row order (rather than using lookups).

2007 won't run the code required if the original workbook is in .xls format.

So.

A: do you guys know of a way to VBA open a 2003 excel sheet in a hidden window in 2007.
Below is the current code which works fine as long as the sheet you are opening is .xlsm (change the extension and original file to .xls and it works fine if you open it with 2003, but not in 2007,it appears to open but it wont fill the inditect formula in the workbook it is opened in):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Range("B2").Activate
    Selection.ClearContents
    Workbooks("Depot shelf lives - all customers.xlsm").Close savechanges:=False
    If ThisWorkbook.Saved = False Then
    ThisWorkbook.Save
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Set newBook = Workbooks.Open("\\linux2\shared\Product Shelf life\Depot shelf lives - all customers.xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksNever)
    newBook.Windows(1).Visible = False
    ActiveSheet.Unprotect Password:="****"
    Selection.AutoFilter Field:=1, Criteria1:="=NEW", Operator:=xlOr, Criteria2:="=Y"
    ActiveSheet.Protect Password:="****"
    Application.ScreenUpdating = True
End Sub

B. If this is not possible, do you know of a way to have a 2003 worksheet be an exact duplicate of a 2007 sheet and update it when updating the 2007 (e.g. make it a linked sheet), allowing me to link to the 2003 version of the sheet.

Cheers

 

[GlennUK]

It's nothing to do with opening a 2003 workbook as hidden. You need to alter the INDIRECT processing to work with the altered file extension ( I'd imagine that it's referencing data with the external filename hardcoded ) ... as a guess.

Cheers, Glenn.

Beauty is in the eye of the beerholder.