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:
Code
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
Display More
Thanks for any help!