Controlling Excel through Access VBA

[BikeToWork]

For some reason, the following code errors out at
.Range(Selectio n, Selection.End(x lToRight)).Sele ct 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.

    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

[ADezii]

I 'think' you need to fully qualify the Selection Object with the Excel Application Object, as in:

Dim objExcelApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet

Set objExcelApp = New Excel.Application
Set wb = objExcelApp.Workbooks.Add("D:\FilePath\Trans_8-17-2016.xls")
Set wb = objExcelApp.Workbooks.Add("C:\Test\File1.xlsx")
    
Set ws = wb.Sheets(1)

With ws
  .Columns("A:A").Select
  .Range(objExcelApp.Selection, objExcelApp.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(objExcelApp.Selection, objExcelApp.Selection.End(xlToRight)).Select
    .Columns("H:I").Select
     objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
    .Columns("AN:AO").Select
     objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
End With

wb.SaveCopyAs "D:\FilePath\Trans_8-17-2016.xls"

'Close the workbook
wb.Close SaveChanges:=False
Set wb = Nothing
Set objExcelApp = Nothing

[BikeToWork]

Thanks for the reply, ADezi. You were absolutely right. I don't know Excel VBA very well. Now, I changed my code to the following and for some reason there is an instance of Excel running after the Access code runs. Can you see what I am doing wrong?

On Error GoTo BAIL
    
    
Dim objExcelApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet

Set objExcelApp = New Excel.Application
Set wb = objExcelApp.Workbooks.Add("D:\Databases\ALS_LAB\REPORTS\Trans_8-17-2016.xls")

   
Set ws = wb.Sheets(1)

With ws
  .Columns("A:A").Select
  .Range(objExcelApp.Selection, objExcelApp.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(objExcelApp.Selection, objExcelApp.Selection.End(xlToRight)).Select
    .Columns("H:I").Select
     objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
    .Columns("AN:AO").Select
     objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
End With

wb.SaveCopyAs "D:\Databases\ALS_LAB\Reports\Trans_Formatted_8-19-2016.xls"


'Close the workbook
    wb.Close SaveChanges:=False
    Set wb = Nothing


    Set objExcelApp = Nothing
    Set ws = Nothing

Leave:
    Exit Function
    
BAIL:
    Set ws = Nothing
    Set wb = Nothing
    Set objExcelApp = Nothing
    MsgBox Err.Description & vbCrLf & Err.Number
    Resume Leave

[ADezii]

1. It may be a left-over Instance of Excel, Reboot and run the Code again.
2. If the above doesn't work, try:
   

   objExcelApp.Quit
   Set objExcelApp = Nothing

[TheSmileyCoder]

When your code reaches this line

  With Selection

Access recognizes that Selection is a method in the Excel library. However you have not specifed which instance of excel in which to use this method. So Access tries to help and generates a hidden excel instance on the fly, from which to execute the Selection method.

Think of it like this is it helps. Imagine having 3 cars

Dim Car1 as Car
Dim Car2 as Car
Dim Car3 as Car

The following code would make sense:

Car1.Accelerate
Car1.Brake

But the below code, by itself does not make sense, which car are we talking about?

Accelerate
Brake

So any time you use code from the excel library you need to fully qualify it. Since we already have objExcelApp defined, we can use that

With objExcelApp.Selection

When you get leftover instances of excel is usually
A) Unqualified references such as the above
B) Errors in your code, that results in abnormal termination of code.