[X]My Assistant

[expatriate]

Hi,
This works the first time. 2nd time fails as indicated. Rinse and repeat. Perhaps I should have a qualifier for "selection"? But it works the first time(?). Beats me

Sub test()

Dim xlApp As excel.Application
Dim xlBook As excel.Workbook
Dim xlSheet As excel.Worksheet
Dim ExcelWkb2 As String
Dim range As excel.range

ExcelWkb2 = CurrentProject.Path & "\Reports\AllDevices4Excel.csv"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(ExcelWkb2, 0, False)
xlBook.Worksheets(1).Cells.EntireColumn.AutoFit
Set xlSheet = xlBook.Worksheets(1)
xlSheet.range("a5:I55").Select
Set range = Selection
With range.FormatConditions.Add(Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0")
'>>Object variable or With block variable not set

.Interior.Color = RGB(208, 216, 232)
.Borders.LineStyle = xlContinuous
.Borders.ThemeColor = 1
.Borders.Weight = xlThin
End With

xlBook.Close savechanges:=False
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
Set xlSheet = nothing
End Sub

[cheekybuddha]

Hi,

You are on the right track.

Whenever you automate Excel from Access you must fully qualify all object references.

Another tip is to remove all instances of the Selection object in your code, since this is only for the UI and usually unnecessary for the functioning of your code; if anything it will slow it down.

Also, avoid naming your variables using existing built-in object names, eg Dim range As excel.range

CODE

Sub test()

  Dim xlApp As excel.Application
  Dim xlBook As excel.Workbook
  Dim xlSheet As excel.Worksheet
  Dim ExcelWkb2 As String
  Dim objRange As excel.range         ' <-- Changed here
  
  ExcelWkb2 = CurrentProject.Path & "\Reports\AllDevices4Excel.csv"
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = True
  Set xlBook = xlApp.Workbooks.Open(ExcelWkb2, 0, False)
  xlBook.Worksheets(1).Cells.EntireColumn.AutoFit
  Set xlSheet = xlBook.Worksheets(1)
  Set objRange = xlSheet.range("a5:I55")         ' <-- Changed here
  With objRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0")
    .Interior.Color = RGB(208, 216, 232)
    .Borders.LineStyle = xlContinuous
    .Borders.ThemeColor = 1
    .Borders.Weight = xlThin
  End With

  xlBook.Close savechanges:=False
  xlApp.Quit
  
  Set objRange = Nothing
  Set xlSheet = Nothing         ' <-- Changed order here
  Set xlBook = Nothing
  Set xlApp = Nothing
  
End Sub

hth,

d

[expatriate]

Thanks, David. That is appreciated big-time.