Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export to Excel Borders not working (2000)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export to Excel Borders not working (2000)

    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.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    Deb,

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

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    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.
    Francois

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    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

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

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

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    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
    Francois

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel Borders not working (2000)

    Thank you, that did the trick.
    Deb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •