Forum Discussion

JoAvg's avatar
JoAvg
Brass Contributor
Dec 07, 2022
Solved

Help modifying macro - Delete double quotes when saving *.txt

I have this following macro.

When saving I get double quotes in every line of the *.scr or *.txt file.

Tried with both concatenating data in single cell with line breaks and splitting data in single rows - same outcome.

Can this macro be modified to delete the quotes?

File is dropped as executable in Autocad and quotes are not recognised, thus breaking the code.

 

 

Sub SaveRangeAsText()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set ws = Worksheets("SHEET")
    Set wb = Workbooks.Add(xlWBATWorksheet)
    Set wt = wb.Worksheets(1)
    wt.Range("A1:A10").Value = ws.Range("W30:W40").Value
    For r = 1 To 15
        wt.Range("A" & r).Value = wt.Range("A" & r).Value
    Next r
        wb.SaveAs Filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\FILE\EXEC.scr", _
        FileFormat:=xlText
    wb.Close Savechanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

 

  • JoAvg 

    Strange - when I run your code it does not produce double quotes.

    Does this work for you?

    Sub SaveRangeAsText()
        Dim ws As Worksheet
        Dim f As Integer
        Dim r As Long
        Set ws = Worksheets("SHEET")
        f = FreeFile
        Open CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
            "\FILE\EXEC.scr" For Output As #f
        For r = 30 To 40
            Print #f, ws.Range("W" & r).Value
        Next r
        Close #f
    End Sub
  • JoAvg 

    Strange - when I run your code it does not produce double quotes.

    Does this work for you?

    Sub SaveRangeAsText()
        Dim ws As Worksheet
        Dim f As Integer
        Dim r As Long
        Set ws = Worksheets("SHEET")
        f = FreeFile
        Open CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
            "\FILE\EXEC.scr" For Output As #f
        For r = 30 To 40
            Print #f, ws.Range("W" & r).Value
        Next r
        Close #f
    End Sub
    • JoAvg's avatar
      JoAvg
      Brass Contributor
      Works like a charm, thank you!

      Issue probably has to do with Locale settings since I have to import Greek characters into Autocad.

Resources