How to close and save excel file .

honey26 · November 16th, 2011, 01:48 PM

I am trying to delete first 10 lines from a .xlsx and b.xlsx , it is happening properly but sheets are not getting closed and saved . below is the code how to close and save the file .

Sub Test()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("D:\a.xlsx")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Set objWorkbook = objExcel.Workbooks.Open("D:\b.xlsx")
Set objWorksheet2 = objWorkbook.Worksheets("Sheet1")

objWorksheet.Rows(17).Offset(-16).Resize(16).Delete

objWorksheet2.Rows(18).Offset(-17).Resize(17).Delete

End Sub .

Shasur · November 16th, 2011, 09:45 PM

Hi

You can try something like this:

Code:

Sub Test()

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open("D:\a.xlsx")
Set objWorksheet1 = objWorkbook.Worksheets("Sheet1")
Set objWorkbook2 = objExcel.Workbooks.Open("D:\b.xlsx")
Set objWorksheet2 = objWorkbook.Worksheets("Sheet1")
objWorksheet.rows(17).Offset(-16).Resize(16).Delete
objWorksheet2.rows(18).Offset(-17).Resize(17).Delete
objWorkbook1.Save
objWorkbook1.Close (False)
objWorkbook2.Save
objWorkbook2.Close (False)
objExcel.Quit ' close Excel
End Sub

Cheers
Shasur

honey26 · November 17th, 2011, 03:47 AM

Thanks :) it worked ....