Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    7

    Unanswered: Unwanted quotes output from Excel Macro

    Greetings,

    This is my first post here. I have this macro working fine except for one result. I open a text file generated from AutoCad, then rearrange things to reuse in AutoCad in a Script file. Drawing coordinates come out with quotes around them. Other numbers in the file do not end up with quotes:

    Drawing coordinates come in like this:

    27.3125,1.6875
    4.375,0.6875

    And end up in the resulting script file with quotes as shown below in red:

    -attedit
    y
    DbrDSI-GVOR
    REV1_BY
    *
    c
    "27.3125,1.6875"
    "4.375,0.6875"

    v
    r
    DD

    Below is the last part of the code that exports the text to a script file. Hopefully someone can point out my stupidity:

    ActiveSheet.Range("b1:b485").Copy
    Open "c:\bogus\dsi_GVOR.scr" For Output As #1
    Close #1
    Range("B1:B485").Select
    Selection.Copy
    ChDir "C:\bogus"
    Workbooks.OpenText Filename:="C:\bogus\dsi_GVOR.scr"
    ActiveSheet.Paste
    Application.CutCopyMode = False

    ActiveWorkbook.Close savechanges:=True
    Application.DisplayAlerts = False
    MsgBox "A Script file was created" & Chr(13) & "To be used with DSI border Rev moving" & Chr(13) & "Found at C:\Bogus\DSI_GVOR.scr"
    End Sub

    -------------------EDIT-----------------------------
    Just in case my post was not clear, I don't want those quotes around the coordinates shown in red. I know I could remove them in the script file using FIND and REPLACE but I would like the macro to do all the work.
    -------------------EDIT-----------------------------
    I threw this part of the code in, I am thinking that this shows the offending part. Unfortunately, I don't know how to rewrite the line. My guess is that the parts in bold, green below should be written differently so that they don't end up with quotes around them in the resulting script file.

    Cells(6, 2).Value = "-attedit" 'Rev 2 to Rev 1 Number
    Cells(7, 2).Value = "y"
    Cells(8, 2).Value = Cells(16, 1).Value
    Cells(9, 2).Value = Cells(344, 1).Value
    Cells(10, 2).Value = "*"
    Cells(11, 2).Value = "c"
    Cells(12, 2).Value = "27.3125,1.6875"
    Cells(13, 2).Value = "4.375,0.6875"

    Cells(14, 2).Value = "v"
    Cells(15, 2).Value = "r"
    Cells(16, 2).Value = Cells(432, 1).Value
    Cells(17, 2).Value = ""

    -------------------EDIT-----------------------------

    Thanks for any help,
    Doug
    Last edited by DougRW; 10-20-13 at 00:29.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Curious, because your script looks okay to me!

    When you go look at the values in the cells in Excel (not in the scr file), are they surrounded by quotes?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2013
    Posts
    7
    gvee,

    Thanks for taking a look.

    The values in Excel are put in Column B before opening the script file and pasting them in there. In Column B There are no quotes around those coordinates.

    They look like this
    27.3125,1.6875
    4.375,0.6875

    Then after pasting the data into the script file, I open the script file and the coordinates look like this:

    "27.3125,1.6875"
    "4.375,0.6875"

    It did occur to me while answering this post that there are 2 sets of numbers that are pasted into the script file.
    1. The coordinates which end up with quotes. These numbers are directly supplied by the code.
    2. The REV number which doesn't end up with quotes. This number is obtained from the value of another cell.

    I don't know if that gives you any clues.

    Cells(6, 2).Value = "-attedit" 'Rev 2 to Rev 1 Number
    Cells(7, 2).Value = "y"
    Cells(8, 2).Value = Cells(16, 1).Value
    Cells(9, 2).Value = Cells(344, 1).Value
    Cells(10, 2).Value = "*"
    Cells(11, 2).Value = "c"
    Cells(12, 2).Value = "27.3125,1.6875" 'Coordinate1
    Cells(13, 2).Value = "4.375,0.6875" 'Coordinate2
    Cells(14, 2).Value = "v"
    Cells(15, 2).Value = "r"
    Cells(16, 2).Value = Cells(432, 1).Value 'Rev Number
    Cells(17, 2).Value = ""

    Thanks,
    Doug
    Last edited by DougRW; 10-26-13 at 01:43.

  4. #4
    Join Date
    Oct 2013
    Posts
    7
    Well I found the answer to the question, "how to eliminate unwanted quotation marks" at this Website:

    McGimpsey & Associates : Excel : Text Files
    Text files with no modification
    "This macro will output a text file without surrounding cells which
    have commas in quotation marks, or doubling quotation marks in the text:"

    I modified the file slightly for the filename I was using and changed the range but other than that, I used this part of the macro just as copied from the Website. I can't say that I know what every part of the following text means, but it works.

    Const DELIMITER As String = ","
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long
    Dim sOut As String

    nFileNum = FreeFile
    Open "c:\bogus\dsi_GVOR.scr" For Output As #nFileNum
    For Each myRecord In Range("b1:b" & _
    Range("b" & Rows.Count).End(xlUp).Row)
    With myRecord
    For Each myField In Range(.Cells(1), _
    Cells(.Row, Columns.Count).End(xlToLeft))
    sOut = sOut & DELIMITER & myField.Text
    Next myField
    Print #nFileNum, Mid(sOut, 2)
    sOut = Empty
    End With
    Next myRecord
    Close #nFileNum


    Application.DisplayAlerts = False
    MsgBox "A Script file was created" & Chr(13) & "To be used with DSI border, moving revs up, you know" & Chr(13) & "Found at C:\Bogus\DSI_GVOR.scr"

    Range("a1:b1062").Select
    Selection.ClearContents
    Cells(1, 1).Select
    Last edited by DougRW; 11-09-13 at 12:24.

Posting Permissions

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