UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code Automating Excel Fails Alternate Runs, Access 2016    
 
   
expatriate
post Nov 14 2019, 04:22 AM
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 frown.gif

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
Go to the top of the page
 
cheekybuddha
post Nov 14 2019, 05:18 AM
Post#2


UtterAccess Moderator
Posts: 11,921
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
Go to the top of the page
 
expatriate
post Nov 14 2019, 05:40 AM
Post#3



Posts: 412
Joined: 23-April 10
From: Thailand


Thanks, David. That is appreciated big-time.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th December 2019 - 03:47 AM