My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
![]() Posts: 412 Joined: 23-April 10 From: Thailand ![]() | 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 ![]() QUOTE 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 |
![]() Post#2 | |
![]() UtterAccess Moderator Posts: 11,918 Joined: 6-December 03 From: Telegraph Hill ![]() | 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 -------------------- Regards, David Marten |
![]() Post#3 | |
![]() Posts: 412 Joined: 23-April 10 From: Thailand ![]() | Thanks, David. That is appreciated big-time. |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 14th December 2019 - 10:39 PM |