Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel VBA Save sheet as separate file

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?
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you post the complete code.
Avatar of Murray Brown

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much
Pleased to help