Why is part of my translated Excel macro not working?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following is a code snippet. The "With .Selection.Font" portion works
fine. I get an error on the "With Selection.Borders(xlEdgeLeft)" portion.

The Excel macro from which I imported the code works fine. What, if
anything, am I doing wrong?

Thanks in Advance

Set xlApp = CreateObject("excel.Application")
Set xlBook = xlApp.Application.Workbooks.Open(strFileLoc & ".xls")

With xlApp
With .Selection.Font
.Name = "Arial Narrow"
.Size = 8
End With

With .Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
 
Are you using Early or Late Binding? In other words, have you set a
reference to Excel or not?

If you don't have a reference, Access has no way of knowing what xlEdgeLeft,
xlContinuous, xlMedium and xlAutomatic are. You need to use the actual
values of the constants:

xlEdgeLeft = 7
xlContinuous = 1
xlMedium = -4138
xlAutomatic = -4105

If that's not the issue, what exactly is the problem? Do you get an error
message? If so, what is it?
 
Set xlBook = xlApp.Application.Workbooks.Open(strFileLoc & ".xls")

With xlApp
With .Selection.Font
Click to expand...

If you look at help, the .Selection is a property of a Window object, not
the Application object. You have two problems here: you either need to
activate a window and then use xlApp.ActiveWindow.Selection (which is
pretty ugly from the user's point of view) or you find another way. The
much much much better way is to decide what range you want to work on:

With xlBook.Worksheets("SheetTwelve")
With .Range(.Cells(1,1), .Cells(11,24)).Font
.Name = "Arial"
.Size = 8
End With
End With


etc etc. You will probably get more help on basic Excel scripting from
one of the Excel newsgroups.

Best wishes



Tim F
 
Back
Top