VB Code to Save file as 97-2003 Worksheet

[rizmomin]

Hello:

Please refer to attached sheet.
I am using below code to rearrange the Clockin-Clockout of employees.
I need to add the code which will Save and close the file in Microsoft Excel 97-2003 Worksheet in the same location as the original after rearranging is done.
Please let me know if any questions.
Thanks
Riz

Sub TimeReport_CSR()
' THIS MACRO IS CONTROL+SHIFT+R
  Dim C As Long
  Dim n As Long
  Dim r As Long
  Dim Rn As Long
  Dim TimeIn As Long
  Dim TimeOut As Long
  Dim Wks1 As Worksheet
  Dim Wks2 As Worksheet
  
 'Wks1 = the original data sheet
   With ActiveWorkbook
     Set Wks1 = .Worksheets(Worksheets.Count)
     .Worksheets.Add After:=Worksheets(.Worksheets.Count)
     Set Wks2 = ActiveSheet
     Wks1.Activate
   End With
   
   Rn = 2
   n = 2
  
 'Count the Rows with IDs
   Do
     If Wks1.Cells(n, "A").Value = 0 Then Exit Do
       n = n + 1
   Loop
  
 'Quit if there are no IDs
  If n = 2 Then Exit Sub
  
 'Set the header row for Sheet2
  With Wks2
    .Cells(1, 1).Value = "ID_CODE"
    .Cells(1, 2).Value = "IN"
    .Cells(1, 3).Value = "OUT"
  End With
  
 'Copy desired data to Sheet2
  For r = 2 To n - 1
    IdCode = Wks1.Cells(r, "A").Value
      For C = 5 To 14 Step 3
        TimeIn = Wks1.Cells(r, C).Value
        TimeOut = Wks1.Cells(r, C + 1).Value
          If TimeIn = -1 Or TimeOut = -1 Then Exit For
        With Wks2
          .Cells(Rn, "A") = IdCode
          .Cells(Rn, "B") = TimeIn
          .Cells(Rn, "C") = TimeOut
        End With
        Rn = Rn + 1
      Next C
  Next r
  
  'Sort list in ascending order by ID
 With Wks2
   .Range("A2:C" & Trim(str(Rn))).Sort Key1:=.Range("A2")
   End With

  
'Save the original data worksheet name
  TabName = Wks1.Name
  
'Delete the worksheet
  Application.DisplayAlerts = False
  Wks1.Delete
  Application.DisplayAlerts = True
  
'Rename the new sheet
  Wks2.Name = TabName
  
End Sub

[6StringJazzer]

...Save and close the file in Microsoft Excel 97-2003 Worksheet in the same location as the original after rearranging is done.

This implies that you don't want to simply save the original, but rather save a copy "in the same location as the original." How do you want to name the new file?

[rizmomin]

Hello:

The name of the file would be the same as original.
Basically, after the rearrange of the data is done, then code needs to save the file in Microsoft 97-2003 Workbook with the original file name.

Please let me know if any questions

Thank u

RM

[djdjdj]

Like this maybe?

ActiveWorkbook.SaveAs "C:\Users\YourUserName\Desktop\ron.xlsm", fileformat:=56

Check this link for excel file formats: http://www.rondebruin.nl/mac/mac020.htm

[6StringJazzer]

The file you attached is already in 97-2003 format. Can't you just use AcrtiveWorkbook.Save? It will keep whatever format the file is already in.

[rizmomin]

Hello:

By adding the code

ActiveWorkbook.Save
ActiveWorkbook.Close

it gives Run Time erroe '1004
Method Save' of object '_Workbook' failed.

I would appreciate if you could try and let me know.

Once again thank you for your help.

Riz

[6StringJazzer]

Something odd is happening. I save your file to disk, then open it using Excel 2013, then try to save it (using the Excel interface, not code) and I get the following error:

'1-22-14_SF0003_TC_RPT.xls' cannot be saved in the current format. To save your changes, click OK, then save it as the latest format.

I have never seen this error and do not know what causes it, but it may be related to why you are getting the error in the code.

What happens when you open this file in Excel 2010 and then immediately try a save?

[rizmomin]

Hello:

I am using Excel 2013.
But the file "1-22-14_SF0003_TC_RPT.xls" is Microsoft Excel 97-2003 Worksheet file.
I will have quite a few files every day and would need to rearrange the Clockin-out data and save as MS Excel 97-2003 Worksheet file to work further with other applications.
That's why i will be using above code to rearrange but stuck at automatically saving the file in MS Excel 97-2003 worksheet format.
Please help.

Thanks a lot

Riz

[6StringJazzer]

The code that djdjdj posted will force a save in the 97-2003 format. Try that.