We help IT Professionals succeed at work.

Excel VBA Save sheet as separate file

Murray Brown
Murray Brown used Ask the Experts™
on
Hi.I am using the following code to save a single sheet as a separate Excel file but it is causing errors on some people''s computer. The error is "object defined error"

F = xPath & "\Individual Holes\" & oDate & " " & oShaft & " " & oLevel & " " & oHole & ".xls"
            ActiveWorkbook.SaveAs Filename:=F, FileFormat:=56

Is there a different way to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Can you post the complete code.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi Roy. I the original code I used the FileFormat = 56 but found extra code to get a file format based on the Excel version so it currently looks as follows


For Each xWs In ActiveWorkbook.Worksheets
        oSheetName = xWs.Name
        If IsNumeric(oSheetName) = True Then
   
            oDate = VBA.Format(xWs.Range("F7").Value, "yyyy MMM dd")
            oHole = xWs.Range("F8").Value
            oLevel = VBA.Mid(xWs.Range("F9").Value, 1, 2)
            oShaft = xWs.Range("J12")
            xWs.Copy
           
            '****Get the right file format for the version of Excel
            If Val(Application.Version) < 12 Then
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                Select Case xWb.FileFormat
                    Case 51:
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    Case 52:
                        If Application.ActiveWorkbook.HasVBProject Then
                            FileExtStr = ".xlsm": FileFormatNum = 52
                        Else
                            FileExtStr = ".xlsx": FileFormatNum = 51
                        End If
                    Case 56:
                        FileExtStr = ".xls": FileFormatNum = 56
                    Case Else:
                        FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
            'First save new workbook to shaft named folder
            'ActiveWorkbook.SaveAs Filename:=xPath & "\Individual Holes\" & oDate & " " & oShaft & " " & oLevel & " " & oHole & ".xls", FileFormat:=56
            F = xPath & "\Individual Holes\" & oDate & " " & oShaft & " " & oLevel & " " & oHole & ".xls"
            'ActiveWorkbook.SaveAs Filename:=F, FileFormat:=56
            ActiveWorkbook.SaveAs Filename:=F, FileFormat:=FileFormatNum
            ActiveWorkbook.Close False
           
        End If
       
   
    Next
Group Finance Manager
Commented:
Have you tried

F = xPath & "\Individual Holes\" & oDate & " " & oShaft & " " & oLevel & " " & oHole &   FileExtStr 

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much
Roy CoxGroup Finance Manager

Commented:
Pleased to help