.Range(Selection, Selection.End(xlToRight)).Select and say with object not set. I copied the VBA from an Excel macro, but know very little about Excel. I basically cut and pasted the code into Access and used the Worksheet object for the with block. Any help is appreciated.
Expand|Select|Wrap|Line Numbers
- Dim objExcelApp As Excel.Application
- Dim wb As Excel.Workbook
- Set objExcelApp = New Excel.Application
- Dim ws As Worksheet
- Set wb = objExcelApp.Workbooks.Add("D:\FilePath\Trans_8-17-2016.xls")
- Set ws = wb.Sheets(1)
- With ws
- .Columns("A:A").Select
- .Range(Selection, Selection.End(xlToRight)).Select
- .Columns("A:BR").EntireColumn.AutoFit
- With Selection
- .HorizontalAlignment = xlGeneral
- .VerticalAlignment = xlBottom
- .WrapText = True
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- .Columns("H:H").Select
- .Range(Selection, Selection.End(xlToRight)).Select
- .Columns("H:I").Select
- Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
- .Columns("AN:AO").Select
- Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
- End With
- wb.SaveCopyAs "D:\FilePath\Trans_Formatted_8-19-2016.xls"
- 'Close the workbook
- wb.Close SaveChanges:=False
- Set wb = Nothing
- Set objExcelApp = Nothing