www.webdeveloper.com
Results 1 to 6 of 6

Thread: Help required - Exporting data from Access to Excel

  1. #1
    Join Date
    Mar 2006
    Posts
    194

    Help required - Exporting data from Access to Excel

    Hi Guys,

    I wonder if someone can help with with an issue regarding exporting data from Access to Excel?

    I'm trying to export some data, and on the whole everything is going OK. The only problem I am having is that in some instances the data appears in the wrong location.

    I've cheched some of the instances, and in each case it seems to be that some of the data is on a new line (in these instances, the content which is on the new line in Access, appears in a new row (first column) in Excel)

    I've tried using the following to replace any line breaks:

    Code:
    var_string = replace(var_string, "\n", "<br />")
    var_string = replace(var_string, "vbLf", "<br />")
    var_string = replace(var_string, "vbCrLf", "<br />")
    var_string = replace(var_string, "vbCr", "<br />")
    ... and although that has resolved the issue in some cases, there are still a few instances where content is appearing on the wrong line.

    I've also tried using the appropriate chr() attributes, but I get the same results.

    Does anyone have any ideas?

    Thanks
    Kessa

  2. #2
    Join Date
    Mar 2006
    Posts
    115
    I am fighting this same exact problem. Any help would be appreciated!

    Have you found any solutions Kessa?

    -chad

  3. #3
    Join Date
    Mar 2006
    Posts
    194
    no, not yet.

  4. #4
    Join Date
    Mar 2006
    Posts
    115
    I have been thinking about a workaround I will try this afternoon. I will let you know if it works. I am planning on writing a php script to connect to the access db and nl2br() all of the fields of all of the records that contain any unwanted line breaks.

    -chad

  5. #5
    Join Date
    Mar 2006
    Posts
    194
    Hi Chad,

    Just had some help on another forum (no offence to the guys here ) and have a solution:

    There seemed to be 2 issues.

    * The first is that I had double quotes around the vb items.
    * The second is that the order in which you specify the vb items seems to be important.

    The code below now works OK for me:

    Code:
    var_string = replace(var_string, "\n", "<br />")
    var_string = replace(var_string, vbcrlf, "<br />")
    var_string = replace(var_string, vblf, "<br />")
    var_string = replace(var_string, vbcr, "<br />")
    I hope that helps you with your problem too
    Kessa

  6. #6
    Join Date
    Mar 2006
    Posts
    115
    Thanks Kessa,

    Orginally I was just trying to write a query to fix the fields, but I could never find a way to update the fields that way.

    I might stay away from vb to do fix the fields and stick with php. But what I am doing will actually change the line breaks in the database and replace them with <br />. Your code just gets the db values at runtime and replaces them.

    If you went ahead and updated the linebreaks in the db with <br /> it would help your code on performance, because you could get rid of the string replace functions.

    Thanks again for your help.

    -chad

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles