I've put toghether some code to open an excel file and change for format (page setup and set columns to autofit) and then save the file. It works except that I want it to work invisisibly.
If I set notifications to true and save it with a prompt the formatting changes are saved as I want. If I set notifications to false the changes aren't saved.
I haven't experimented with application visible true or false.
Is there a way to have it save the changes without going through the prompt.
The code is below:
Private Sub CommandButton1_Click() Dim oApp As Excel.Application Dim oExcel As Excel.Workbook Set oApp = CreateObject("Excel.Application") Set oExcel = oApp.Workbooks.Open(Filename:="C:\testxl\test.xls") oApp.Visible = True oApp.DisplayAlerts = False oExcel.Worksheets("Sheet1").Activate With oExcel.Worksheets("sheet1").Columns .Columns("A:Z").AutoFit End With With oExcel.Worksheets("Sheet1").PageSetup .Zoom = False .FitToPagesTall = 200 .FitToPagesWide = 1 End With With oExcel.Worksheets("Sheet1") .PageSetup.Orientation = xlLandscape End With ActiveWorkbook.Save oApp.Quit End Sub
Thanks for any help!