Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Puzzling Code

Status
Not open for further replies.
JDU

JDU

Technical User
Dec 23, 2002
182
US
When I run the following code from an external spreadsheet and supply the file name, I get the error Application-defined or object-defined error on the line
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous 'Error Here

However if I cut and paste this code in the local spreadsheet as opposed to running it from another spreadsheet it works just fine. Any ideas? Thanks.

Sub Borders()
Dim WB, objexcel, colp, colq, rowx, rowy, incrowy, stringrange, rwindex, filename

Set objexcel = CreateObject("Excel.Application")
filename = InputBox("Enter ANTIBIOGRAM File Name")
Set WB = objexcel.Workbooks.Open("C:\Documents and Settings\pasvarghes\Desktop\" & filename & ".xls")

With WB.Sheets(1)
colp = "A"
colq = InputBox("Enter Last Column")
rowx = 3
rowy = InputBox("Enter Last Row")

incrowy = rowx + 2

For rwindex = rowx To rowy Step 1

If incrowy > rowy + 1 Then
Exit For
End If

stringrange = colp & rowx & ":" & colq & incrowy

.Range(stringrange).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous 'Error Here
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

rowx = rowx + 4
incrowy = rowx + 2

Next rwindex

stringrange = "a1" & ":" & colq & "1"

.Range(stringrange).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End With

WB.Save
WB.Close
objexcel.Quit
Set WB = Nothing
Set objexcel = Nothing

MsgBox "Done"
End Sub
 
Sort by date Sort by votes
Hi!

I think there might be two issues here relating to automation. One is the implicit referencing of the sheet and range objects Excel Automation Fails Second Time Code Runs the second usage of With blocks Automation Doesn't Release Excel Object from Memory. It is probably the first one you're strugling with now, see the example in the first link on how to address workbooks, sheets and ranges explicitly.

Roy-Vidar
 
Upvote 0 Downvote
I changed the code to the following, but still get the same error. Thanks.

Sub Borders()
Dim WB, objexcel, xlSheet, colp, colq, rowx, rowy, incrowy, stringrange, rwindex, filename

Set objexcel = CreateObject("Excel.Application")
filename = InputBox("Enter ANTIBIOGRAM File Name")
Set WB = objexcel.Workbooks.Open("C:\Documents and Settings\pasvarghes\Desktop\" & filename & ".xls")
Set xlSheet = WB.Sheets(1)

colp = "A"
colq = InputBox("Enter Last Column")
rowx = 3
rowy = InputBox("Enter Last Row")

incrowy = rowx + 2

For rwindex = rowx To rowy Step 1

If incrowy > rowy + 1 Then
Exit For
End If

stringrange = colp & rowx & ":" & colq & incrowy

xlSheet.Range(stringrange).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous 'Error Here
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

rowx = rowx + 4
incrowy = rowx + 2

Next rwindex


WB.Save
WB.Close
objexcel.Quit
Set WB = Nothing
Set objexcel = Nothing

MsgBox "Done"
End Sub
 
Upvote 0 Downvote
Taking only a part:

[tt] xlSheet.Range(stringrange).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With[/tt]

vs

[tt] xlSheet.Range(stringrange).Select
objexcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
objexcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
objexcel.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeLeft).Weight = xlThin
objexcel.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic[/tt]

Don't know if this is correct syntax, I'm often mixing which objects, methods and properties belong together, but have removed the With blocks as per the second link, and explicit referenced the selection as per the first link.

I'm also a bit concervative on declarations:

[tt]dim objexcel as object
dim WB as object
dim xlSheet as object
dim strFilename as string
...[/tt]

This is late binding, and at least when automating from other applications (Access) one would need to replace the constants with their values, but I'm not sure this is necessay when running from Excel.

Roy-Vidar
 
Upvote 0 Downvote
Thanks so much, that solved the first problem. However I am getting the same error below (see code). I am sure it is related but don't know how to solve it.

Sub Borders()
Dim WB, objexcel, xlSheet, colp, colq, rowx, rowy, incrowy, stringrange, rwindex, filename

Set objexcel = CreateObject("Excel.Application")
filename = InputBox("Enter ANTIBIOGRAM File Name")
Set WB = objexcel.Workbooks.Open("C:\Documents and Settings\pasvarghes\Desktop\" & filename & ".xls")
Set xlSheet = WB.Sheets(1)

colp = "A"
colq = InputBox("Enter Last Column")
rowx = 3
rowy = InputBox("Enter Last Row")

incrowy = rowx + 2

For rwindex = rowx To rowy Step 1

If incrowy > rowy + 1 Then
Exit For
End If

stringrange = colp & rowx & ":" & colq & incrowy

xlSheet.Range(stringrange).Select

objexcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
objexcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
objexcel.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeLeft).Weight = xlThin
objexcel.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic

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

objexcel.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeBottom).Weight = xlThin
objexcel.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

objexcel.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeRight).Weight = xlThin
objexcel.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic

objexcel.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
objexcel.Selection.Borders(xlInsideVertical).Weight = xlThin
objexcel.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic

objexcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
objexcel.Selection.Borders(xlInsideHorizontal).Weight = xlThin
objexcel.Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic


rowx = rowx + 4
incrowy = rowx + 2

Next rwindex

stringrange = "a1" & ":" & colq & "1"

xlSheet.Range(stringrange).Select

objexcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
objexcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
objexcel.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeLeft).Weight = xlThin
objexcel.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic

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

objexcel.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeBottom).Weight = xlThin
objexcel.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

objexcel.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
objexcel.Selection.Borders(xlEdgeRight).Weight = xlThin
objexcel.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic

objexcel.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
objexcel.Selection.Borders(xlInsideVertical).Weight = xlThin
objexcel.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic

objexcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous 'ERROR HERE
objexcel.Selection.Borders(xlInsideHorizontal).Weight = xlThin
objexcel.Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic


WB.Save
WB.Close
objexcel.Quit
Set WB = Nothing
Set objexcel = Nothing

MsgBox "Done"
End Sub
 
Upvote 0 Downvote
Guessing you still have some instances of Excel in memory. Exit Excel, then use the Task Manager to kill the remaining instances and try again.

Housecleaning:

[tt]WB.Save
wb.Close
objexcel.Quit
set xlSheet = Nothing
Set WB = Nothing
Set objexcel = Nothing[/tt]

Roy-Vidar
 
Upvote 0 Downvote
BTW - you have checked the range is valid? Some of these properties need there to be cells in more than one column/row, else they trow 1004 (but on my setup with a meaningful errormessage, and I get it at the .Weight property)

Roy-Vidar
 
Upvote 0 Downvote
You are right, even when I close excel and look in task manager, there is an instance running. I continue to get the error message at the .LineStyle property. Would it be better to do the second set of borders (ie. the 1st row of the spreadsheet) in another macro? Thanks.
 
Upvote 0 Downvote
1 - Leaving an instance open is probably because of what I posted in my first reply, using implicit referencing - in this case, when you get errors before "the housecleaning", it won't release the variables neither.

I'd at least implement some errorhandling, and ensure the objects are closed and released prior to exiting the sub (and I'd say you need to verify the inputs. What happens if the users enter wrong arguements?).

2 - as (also) addressed above, some of these properties need more than one row/column to work. The error you're getting now, is probably because of trying to address "the middle lines" in a selection with only one row/column - it doesn't have "midle/inside" lines -> throwing an error.

Some more testing on the range would eliminate that, or just don't attempt addressing the innside lines when there's only one row or column.

Roy-Vidar
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
572
strongm
strongm
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
MattGreer
  • Locked
  • Question
VBA Code Deleting a Column instead of Rows...
Replies
6
Views
332
SkipVought
S
MattGreer
  • Locked
  • Question
Application Lockups During Code Execution 4
Replies
9
Views
442
MattGreer
MattGreer
EliseFreedman
  • Locked
  • Question
Running Code for every value in a column 1
Replies
4
Views
253
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top