Excel 2010 - Verify the file is from a trusted source

[kgkev]

I have a code that saves a copy of a workbook.

If globalchange = False Then Else
    ActiveSheet.Copy
    Application.DisplayAlerts = False
    invnum = Replace$(invnum, Mid$("/", 1, 1), "-")
    invnum = Replace$(invnum, Mid$("""", 1, 1), "")
    ActiveWorkbook.SaveAs FileName:="\\ih-serv\company\Shipping\Invoices\Excel\" & invnum & ".xls"
    Application.DisplayAlerts = True
    ActiveWorkbook.Close

End If

As part of a seperate routine this file is checked to see if it exists and if it does to reopen it.

invnum = Sheets("input").Range("A1").Value
invnum = Replace$(invnum, Mid$("/", 1, 1), "-")
invnum = Replace$(invnum, Mid$("""", 1, 1), "")

If Fileexists("\\ih-serv\company\Shipping\Invoices\Excel\" & invnum & ".xls") = True Then
    runnew = MsgBox("This invoice has already been generated" & vbCrLf & "Do you want to generate a new copy?", vbYesNo, "Warning")
    If runnew = 7 Then
        globalchange = False
        Application.DisplayAlerts = False

        Application.Workbooks.Open ("\\ih-serv\company\Shipping\Invoices\Excel\" & invnum & "*")
        Application.DisplayAlerts = True
        
        Workbooks(invnum).Sheets("invoice EDIT").Range("A1:W402").Copy Destination:=Workbooks("invoice").Sheets("invoice EDIT").Range("A1").Paste
        Workbooks(invnum).Close
    Else
    newinvoice
    End If
Else
    newinvoice
End If

However if I get an error stating that the file is not in recognisable format and do I want to open it?

How do I save it so it doesn't give this error?


I have added the Application.DisplayAlerts = False and the file is opened but then "Workbooks(invnum)" doesn't work - Subscript out of range

 

[JD_GFI]

excel 2010 saves as xlsx, may be the prob?

 

[Ruddles]

You've told it to save with a file extension of XLS but you didn't tell it to write the file in 97-2003 format. Try this:-

ActiveWorkbook.SaveAs FileName:="\\ih-serv\company\Shipping\Invoices\Excel\" & invnum & ".xls"[COLOR=red][B], FileFormat:=xlExcel8    [/B][/COLOR]

 

[kgkev]

my code has to be universal from 2000 to 2010.

If I save as xlsx it will cause problems from older versions

 

[Ruddles]

Helpful info here: http://www.rondebruin.nl/saveas.htm.

 

[Andrew Poulsom]

If you are using Excel 2007 or above you need to include the FileFormat argument when using SaveAs. For more information see:

http://www.rondebruin.nl/saveas.htm

 

[kgkev]

Sorry was replying to the previous poster.

Thanks for the answer