Create lines in Excel Sheet using Access VBA

[skipjakk]

Is there a way to create lines(borders) in an excel worksheet from Access VB? I've tried this:


With myExcel.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

But just generates an error '1004 - Application defined or Object Defined Error'

Any help would be appreciated, thanks!

 

[jebry]

Hi!

What's the rest of the code? What you posted looks good.



Jeff Bridgham
bridgham@purdue.edu

 

[skipjakk]

This is the all the code, in Access 97:

myExcel.worksheets(1).Range("A1:O1").Select

With myExcel.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myExcel.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myExcel.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myExcel.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[jebry]

Hi!

Is there somewhere that you set myExcel to reference the workbook you want?



Jeff Bridgham
bridgham@purdue.edu

 

[skipjakk]

Creating Excel Worksheet:

Set myExcel = CreateObject("excel.application")
myExcel.Application.workbooks.Add
myExcel.Application.Visible = True
myExcel.Activewindow.zoom = 75

 

[jebry]

Well, I don't see why that doesn't work but you can try a couple of other things:

1.

With myExcel.worksheets(1).Range("A1:O1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

2.

Dim myExcelSheet As Excel.Worksheet

Set myExcelSheet = myExcel.worksheets(1)
With myExcelSheet.Range("A1:O1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

hth


Jeff Bridgham
bridgham@purdue.edu

 

[skipjakk]

Still no luck, using access 87, not sure if that makes a difference or not....

 

[deboyz]

Why don't you try creating a workbook object and range object first and then setting the gridlines, maybe the program is just fussy:

dim objwkb as excel.workbook, objrange as excel.range
set objwkb = myexcel.workbooks.add
set objrange = objwkb.range("A1:O1")
With objRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = intWeight
.ColorIndex = ColorConstant(strLineColor)
End With
etc.

Goodluck!

 

[PHV]

Seems the OP use late binding, so the xl constants are undefined.
Const xlEdgeTop = 8, xlEdgeBottom = 9, xlEdgeRight = 10
Const xlInsideVertical = 11, xlThin = 2, xlContinuous = 1
Const xlAutomatic = &HFFFFEFF7

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886