Saving from 2010 to 2002 .xls

[ElvisHess]

I have a spread sheet that saves 2 of its tab to a file with a specific name.

Problem is that when my Customer (Who has Excel 2002) gets the file, It comes up with the message

"The file you are trying to open, '5312a863-4-PILLAR CTR INR UPR-110315gg.xls,
is in a different format than specified by the file extension.

Verify that the file is not corrupt and is from a trusted source before opening the file.
Do you want to open the file now?"

YES NO and HELP are the options that come up. When the Customer selects yes the spreadsheet comes up blank. I get the same message when I call up the Exported file also, but when I save as again to Excel 97-2003 Workbook .xls. And open the file the message goes away. Another issue I have when I double click the Exported file it calls up the Master File that it was created from (I don't want this to happen).

Please see my code below, I had help writing this when I was running 2003.

Private Sub btnCopyUSReports_Click()
    Me.Hide
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    Sheets("ScheduleA").Visible = True
    Sheets("ScheduleA").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="elvis1"
    ActiveSheet.EnableSelection = xlNoSelection
    Sheets("ScheduleB1").Visible = True
    Sheets("ScheduleB1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="elvis1"
    ActiveSheet.EnableSelection = xlNoSelection
    Sheets(Array("ScheduleA", "ScheduleB1")).Select
    Sheets(Array("ScheduleA", "ScheduleB1")).Copy
    ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
    NameofFile = "S:\PROJDATA\INITIAL\" & Range("ScheduleA!AG1") ' PART NUMBER - PART NAME - DATE
    Set NewBook = Application.ActiveWorkbook
    fileSaveName = Application.GetSaveAsFilename(NameofFile, fileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")    'Excel Files
    If fileSaveName <> False Then
        NewBook.SaveAs Filename:=fileSaveName
    End If
    ActiveWindow.Close
    Sheets("ScheduleA").Select
    ActiveSheet.Unprotect Password:="elvis1"
    Sheets("ScheduleA").Visible = False
    Sheets("ScheduleB1").Select
    ActiveSheet.Unprotect Password:="elvis1"
    Sheets("ScheduleB1").Visible = False
    UserForm.Show
End Sub

Please HELP!!!

Thank You

 

[xenou]

This line needs to be changed:

NewBook.SaveAs Filename:=fileSaveName

You need to provide a file format argument:

NewBook.SaveAs Filename:=fileSaveName, FileFormat:=56

As explained in more detail here:
http://www.rondebruin.nl/saveas.htm

I don't know what you mean by "it calls up the master workbook", but if that means the master workbook opens, then its probably due to some stray code somewhere.

 

[ElvisHess]

It appears to have worked it doesn't give me the message anymore. I am sending the test file to my Customer now.

Thank You so much, Lifesaver!!!

To explain the other issue. My Master file is the file that I initially create the 2 tabs that I export for the Customer.

After all is done and I open up one of the exported files. I double click and get the expected protection error, but when I click OK to get rid of that message it opens the master file. I'm not sure what will happen if the Customer double clicks it. I would like for that link to be gone. Not sure what kind of error they would get, But I have hundreds of these that I send.

 

[xenou]

After all is done and I open up one of the exported files. I double click and get the expected protection error, but when I click OK to get rid of that message it opens the master file.

Is there code (anywhere) in the exported files?

 

[ElvisHess]

None at all,

I have the line ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
in there so it doesn't ask to update links though.

I'm a self taught person, Have learned from examples. So........

Thank You

 

[xenou]

Are there links to the "Master File"?

 

[ElvisHess]

Yes all of the fields that had formulas in the original sheet Master Sheet refer back to the Master file.

Example: =+'F:\123files\[CustomerDocs-Mitsubishi.xls]Info'!B2

Is the possibly a method when I export to make the formula fields the value instead of the formula?

 

[xenou]

hi,

You want to break the links. You do this in native Excel under the edit links options on the Edit menu (2003) or Data tab (2007/2010).

Try adding to your code:

    If fileSaveName <> False Then
        NewBook.SaveAs Filename:=fileSaveName
 [COLOR="Blue"]       Call BreakMyLinks(NewBook)[/COLOR]
    End If

Include this Subroutine in your project:

Sub BreakMyLinks(ByRef wb As Workbook)
Dim arLinks As Variant
Dim intIndex As Integer
    
    arLinks = wb.LinkSources(xlExcelLinks)
    
    Application.DisplayAlerts = False
    On Error Resume Next
    If Not IsEmpty(arLinks) Then
        For intIndex = LBound(arLinks) To UBound(arLinks)
            wb.BreakLink Name:=arLinks(intIndex), Type:=xlExcelLinks
        Next intIndex
    End If
    On Error GoTo 0
    Application.DisplayAlerts = True

End Sub

E.g., the entire code from your last post, edited with the changes:
(this may not include changes you've made since - it's just to show how to use a call to a separate subroutine).
Note that I sometimes find the links don't actually break until I close and re-open the workbook (its a bit odd). I don't think it matters but you may want to test that when you send the workbook the links are broken. Otherwise, we may need to programmatically add a line to close the workbook and re-open it.

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] btnCopyUSReports_Click()
    Me.Hide
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    Sheets("ScheduleA").Visible = True
    Sheets("ScheduleA").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="elvis1"
    ActiveSheet.EnableSelection = xlNoSelection
    Sheets("ScheduleB1").Visible = True
    Sheets("ScheduleB1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="elvis1"
    ActiveSheet.EnableSelection = xlNoSelection
    Sheets(Array("ScheduleA", "ScheduleB1")).Select
    Sheets(Array("ScheduleA", "ScheduleB1")).Copy
    ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
    NameofFile = "S:\PROJDATA\INITIAL\" & Range("ScheduleA!AG1") [COLOR="SeaGreen"]' PART NUMBER - PART NAME - DATE[/COLOR]
    [COLOR="Navy"]Set[/COLOR] NewBook = Application.ActiveWorkbook
    fileSaveName = Application.GetSaveAsFilename(NameofFile, fileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")    [COLOR="SeaGreen"]'Excel Files[/COLOR]
    [COLOR="Navy"]If[/COLOR] fileSaveName <> False [COLOR="Navy"]Then[/COLOR]
        NewBook.SaveAs Filename:=fileSaveName
        [COLOR="Navy"]Call[/COLOR] BreakMyLinks(NewBook)
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    ActiveWindow.Close
    Sheets("ScheduleA").Select
    ActiveSheet.Unprotect Password:="elvis1"
    Sheets("ScheduleA").Visible = False
    Sheets("ScheduleB1").Select
    ActiveSheet.Unprotect Password:="elvis1"
    Sheets("ScheduleB1").Visible = False
    UserForm.Show
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'-------------------------------------[/COLOR]
[COLOR="Navy"]Sub[/COLOR] BreakMyLinks(ByRef wb [COLOR="Navy"]As[/COLOR] Workbook)
[COLOR="Navy"]Dim[/COLOR] arLinks [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] intIndex [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    
    arLinks = wb.LinkSources(xlExcelLinks)
    
    Application.DisplayAlerts = False
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] IsEmpty(arLinks) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] intIndex = LBound(arLinks) [COLOR="Navy"]To[/COLOR] UBound(arLinks)
            wb.BreakLink Name:=arLinks(intIndex), Type:=xlExcelLinks
        [COLOR="Navy"]Next[/COLOR] intIndex
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
    Application.DisplayAlerts = True

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[ElvisHess]

Its get stuck at the NewBook part of this line

Call BreakMyLinks(NewBook)

I tried to put FileSaveName in NewBooks place and it still got the error

Compile error:

ByRef argument type mismatch

 

[xenou]

NewBook is a variable, and must be a reference to a workbook, not a path or workbook name. I used NewBook because your code has that variable already.

Is this workbook open or closed when you try to break the links in it? It should be open.