Export to Excel Borders not working (2000)

**masterd** · 2004-09-01 12:30

Hi,
I have the following code attached and for some reason when it tries to run the code to add borders around the cells a second time I get the following error just for the inside borders. Run time error 1004 - Unable to set the LineStyle property of the Border class. When I comment out the line so it ignores the code it works great but of course no lines. The code that I'm having trouble with is commented out. It's part of the PopulatePartsByVendor sub procedure. Basically the code is to export data to excel spreadsheets. Any ideas?
Thanks,
Deb

**WendellB** · 2004-09-02 14:24

I'm not able to do much with code at this point as I am traveling, but since this is an excellent example of the use of Automation, one of the debugging techniques I frequently use is to take the code and debug it directly in Excel. It may or may not actually solve the problem, but in many cases because you can see what is going on directly, it helps.

**masterd** · 2004-09-04 00:15

Thanks for responding Wendell. I couldn't find anything goofy happening in Excel. It's weird because it will put the lines through ok in the first run through but bombs out on the second, and only the inside horizontal. Right now I'm at a lost.
Thanks,
Deb

**Francois** · 2004-09-04 07:57

Deb,

Could you post a stripped version of the db and the excel file so we can test it ?

**masterd** · 2004-09-07 00:45

I would love to, but just trying I can only get it down to 133kb. i've gotten rid of everything I can think of except the forms and queries and tables needed to show u the problem. I've also gotten rid of most of the fields and records. Any other suggestions.
Deb

**Francois** · 2004-09-07 07:14

Have a look at Hans' <post#=401925>post 401925</post#>. Converting the database to Access 97 can be a solution.
If you want you can send it to my e-mail (see my profile). I'm not an excel expert but will look if I can find something.

**masterd** · 2004-09-07 17:03

Ok, I finally got it scaled down. I'll be attaching the Excel file needed in the next post. Basically I have some of the code repeated and for some reason when it runs through the code that adds borders to the Excel file the second time it stops. But you'll notice once you run it (currently the code is commented out) that if there is a vendor name it works, if not, it doesn't.
Thanks,
Deb

**masterd** · 2004-09-07 17:06

Here's the Excel file, put this in the C: drive. Thanks,
Deb

**Francois** · 2004-09-08 19:25

In attachment the PopulatePartsByVendor Sub.
I comment out all the lines concerning With r.Borders(...
and I replace it with the code :<pre>Dim c As Object
For Each c In r.Cells
c.BorderAround ColorIndex:=xlAutomatic, Weight:=xlThin, LineStyle:=xlContinuous
Next</pre>

This seems to do the job. I know to little about Excel to explain why your code is not working

**masterd** · 2004-09-13 18:30

Thank you, that did the trick.
Deb

Thread: Export to Excel Borders not working (2000)

Thread Tools