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

    Adding Borders to Excel Export (2000)

    Very long piece of code (over 4,000 characters) moved to attachment by HansV.

    Hi,
    I have the below code that exports data from Access to Excel. I'd like the repeated data that is coming from the query to have grid lines around the cells starting with row 13 but do not know how to do that. I tried adding it to the spreadsheet but that didn't work. Thanks. Deb
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Borders to Excel Export (2000)

    Try adding the following code to the end of PopulateParts:

    Dim r As Object
    Set r = theWorksheet.Range(theWorksheet.Cells(13, 1), _
    theWorksheet.Cells(theRow - 1, rs.Fields.Count - 2))
    With r.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With r.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With r.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With r.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With r.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With r.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

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

    Re: Adding Borders to Excel Export (2000)

    Hans,
    I added the code after the Loop command and I am getting the following error: Run-time error 1004, application-define or object-defined error. When I click on debug it goes to the "With r.border(xlEdgeLeft) line". The fourth line.
    Thanks,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Borders to Excel Export (2000)

    I assume that "border" is just a typo - the code I posted has "Borders".

    Do you have a reference to the Microsoft Excel 9.0 Object Library? If not, you will have to replace all the xl... constants by their numeric value:
    xlEdgeLeft = 7
    xlEdgeTop = 8
    xlEdgeBottom = 9
    xlEdgeRight = 10
    xlInsideVertical = 11
    xlInsideHorizontal = 12
    xlContinuous = 1
    xlThin = 2
    xlAutomatic = -4105

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

    Re: Adding Borders to Excel Export (2000)

    Hans,
    That was a typo, I just copied your code, but I added the Object library and it works great. Thank you once again. Is there a way that when you export data to have the row size adjust if there is text that is too big to fit in the cell. What I'm doing right now is just making all rows a certain size but some don't need to be that big to show the data and some need to be bigger.
    Thanks,
    Deb

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Borders to Excel Export (2000)

    You can add this below the code I provided earlier:

    With r
    .VerticalAlignment = xlTop
    .WrapText = True
    .Rows.AutoFit
    End With

    The .Rows.AutoFit is probably superfluous, but it won't harm.

    By the way, both pieces of code I posted here were derived from code generated by recording a macro in Excel. That is often a good way to find out what the code you need looks like. I replaced the Selection in the recorded macro by a range derived from your code.

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

    Re: Adding Borders to Excel Export (2000)

    Thank you. I tried the record macro in Excel but couldn't figure out what to replace Selection with. Thanks a ton.
    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: Adding Borders to Excel Export (2000)

    I tell ya, you give people an inch and they want a yard. With the code that I posted earlier I have each excel sheet broken out as "OEM" or "Vendor". Well now they want it broken out by the VendorName, if a vendor name was selected in the vendor name field (I have a form with a drop down box for the user to possibly choose a vendor before we send out the quote). If there isn't a vendor name then they'd like to revert to how I have it now, all OEM on one sheet and all Vendor on the same sheet. The OEM and Vendor come from the RecSource Field. I'm having trouble with this change. Any ideas?
    Thanks,
    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Borders to Excel Export (2000)

    I'm sorry, this is probably because English is not my native language. What do you mean by 'broken out by the VendorName'? In that situation, you have only one vendor name, don't you? What sheet(s) should be created?

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

    Re: Adding Borders to Excel Export (2000)

    By broken out I mean separated, so all the records that have the RecSource field as OEM are exported to one spreadsheet and then all the records that have the RecSource field as Vendor are exported to another spreadsheet. Well I have a query that has many records. Some of the records could have data in the VendorID field and some could not. So basically the query matches the MachineID field and the RecSourceID field and exports accordingly so all parts for one machine that we labeled "OEM" are exported to one sheet and so on. Well now they want the Vendor ID to be included in that. If for a particular machine we have a bunch of records that have data in the VendorID field, export that to a sheet, then the same machine could have more records that have different data in the VendorID field and they want those broken out to different sheets. Meaning basically we can order parts from many vendors for 1 machine, but we have to get quotes first which is why we are exporting to excel.
    Does this help?
    Deb

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Borders to Excel Export (2000)

    No, I fear I understand less now than before. Perhaps somebody else can help. Sorry.

Posting Permissions

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