Results 1 to 5 of 5
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209

    Manipulate Excel From Access


    Hello - I know how to open Excel From Access VBA, but what is the VBA syntax to remove all carriage returns?

    I use this to open the Excel - but not sure how to manipulate and remove the carriage returns?

    Code:
    Function openExcel()
    
    Dim xlApp As Object
    Dim wb As Object
    
    Set xlApp = CreateObject("excel.application")
    Set wb = xlApp.Workbooks.Open("C:\Test\test.csv")
    
    'here is where we would make the change
    
    wb.Save
    wb.Close
    Set wb = Nothing
    
    xlApp.Quit
    Set xlApp = Nothing
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you may not be able to, CRLF is an invisible character and the excel replace may reject it.
    try in excel, record a macro, and do a normal replace with regular letters, change A to Z.
    end recording.
    then edit the macro code and change "A" to chr(13)
    then run it

    if that works then the vb code should too.

  3. #3
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    what is chr(13)? it replaced my A with what appears to be an empty cell

    The code the macro recorder gave me was
    Code:
    Cells.Select
    Selection.Replace What:="A", Replacement:=Chr(13), LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    CRLF stands for
    CR = carriage return
    LF = line feed.

    Think of a manual typewriter. The carriage is what holds the sheet of paper. After typing approx 80 characters, you had to sent the carriage back to the left edge of the paper. That is returning the carriage (a CR). The ASCII code for a CR is Chr(13).
    The lever (manual typewriter, remember?) that returns the carriage also has to move the paper so the typing won't type over the previous line. That is a line feed (LF). The ASCII code for a LF is Chr(10).

    I don't understand why you would want to do this as all of the lines of the CSV file would run together.

    You might try
    Code:
    Cells.Select
    Selection.Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    You know that the ASCII code for a CR is Chr(13).
    Chr(32) is the character code for a space.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In vba, to get rid of a line feed (which usually means the carriage return as well) you have to remove both the carriage return and the line feed, not just Chr(13) - and it has to be done in a particular order. IIRC, it is chr(13) first, then chr(10). No idea if Excel will behave any differently.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Manipulate Excel Data
    By jo15765 in forum Programming
    Replies: 6
    Last Post: 05-15-2017, 11:39 AM
  2. Replies: 5
    Last Post: 05-13-2016, 10:37 AM
  3. manipulate excel iconset
    By xopherira in forum Forms
    Replies: 1
    Last Post: 02-13-2016, 10:03 AM
  4. Manipulate Forms
    By Milade8080 in forum Forms
    Replies: 6
    Last Post: 07-14-2014, 12:51 PM
  5. Manipulate DB through VB ...
    By Zoroxeus in forum Programming
    Replies: 2
    Last Post: 12-18-2005, 01:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums