Excel export as TXT w/o the "" (quotes) 1

[mattloflin]

Hey guys I currently have a macro and it sets three columns to a certain name

    Range("A1:A" & newlastrow).Value = "PROBEPOINTTEST,"
    Range("C1:C" & newlastrow).Value = ",COLOR,"
    Range("E1:E" & newlastrow).Value = ",LABEL,"

once it runs I save this as a .TXT and then I am going to import it into a program.

However this program doesn't like the "" I have to go manually delete the "" from how ever many lines.

My question:

Is there any way I can delete the "" from the export file?

Thanks guys!

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mattloflin]

if I need to rewrite my code to just write to a TXT file that might make it work. I'd need help with that too. To get you guys to understand the code. It's not the best but it works

have a look:

Sub GTS()
'
' GTS Macro
' Macro recorded 2/25/2008 by Matt Loflin
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
    
Dim NewName As String
Dim x As String

x = Application.ActiveWorkbook.FullName
OldX = Application.ActiveWorkbook.Name
    
    ' Rename Full Filename
    If Right((x), 8) = " GTS.xls" Then
        NewName = Left((x), Len(x) - 8) & " GTS" & ".xls"
    Else
        NewName = Left((x), Len(x) - 4) & " GTS" & ".xls"
    End If
    
    'Add New Workbook / Save New Name
    Workbooks.Add
    ActiveWorkbook.SaveAs NewName
    NewX = Application.ActiveWorkbook.Name
    
    'Copy and Paste Columns
    Windows(OldX).Activate
    Columns("A:A").Copy
    Windows(NewX).Activate
    Columns("F:F").Select
    ActiveSheet.Paste
    Windows(OldX).Activate
    Columns("M:M").Copy
    Windows(NewX).Activate
    Columns("B:B").Select
    ActiveSheet.Paste
    Windows(OldX).Activate
    Columns("J:J").Copy
    Windows(NewX).Activate
    Columns("D:D").Select
    ActiveSheet.Paste
        
    'Delete Empty Rows
    newlastrow = Range("b" & ActiveSheet.Rows.Count).End(xlUp).Row
    Range("A1:A" & newlastrow).Value = "PROBEPOINTTEST,"
    Range("C1:C" & newlastrow).Value = ",COLOR,"
    Range("E1:E" & newlastrow).Value = ",LABEL,"
    
    
    Dim ocell As Range
    
    For i = newlastrow To 2 Step -1
        If Left(Range("b" & i), 2) <> "J1" And _
           Left(Range("b" & i), 2) <> "J2" And _
           Left(Range("b" & i), 2) <> "J3" Then
               Range(i & ":" & i).EntireRow.Delete
        End If
        If Left(Range("b" & i), 2) = "J1" Or _
           Left(Range("b" & i), 2) = "J2" Or _
           Left(Range("b" & i), 2) = "J3" Then
            Cells(i, 2).Value = "$" & Cells(i, 2).Value
        End If
    Next i
    Rows(1).Delete
    lastrow = Range("b" & ActiveSheet.Rows.Count).End(xlUp).Row
    Rows(lastrow + 1).Delete
    Rows(1).Insert shift:=xlDown
    Cells(1, 1) = "Begin"
    
    Rows(1).Insert shift:=xlDown
    Cells(1, 1).Value = "INSTRUCTIONS"
    Cells(lastrow + 3, 1).Value = "END"
    
    Columns("A:F").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
ActiveWorkbook.Save

End Sub

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[SkipVought]

Hi,

Have you tried SaveAs a text file? There are a number of options; comma separated, tab separated, fixed width.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[ck1999]

Hi Matt,

Is this a 1 time deal? If not do you want to code this?

The culprit with the "" is the , If you remove the ,s from the code and then export you will not have an issue with "". However, this may not give you a , deliminated file (records seperated with a comma). My saveas does not list a comma deliminated text file. But under help menu it tells you how to change a deliminator. If you need help finding it in the help menu post back but search of export text file.

An alternative would be to save as a .csv file and then after the file is saved close the file and change the extension to txt.

Hope this helps you some.
ck1999

 

[mattloflin]

Yes skip I tried that, hope everything goes/went well I wasn't sure when your surgery is. But good luck on whichever.

ck1999 This is not a one time deal it will be a constant deal actually.

I think i figured it out actually:

Open NewName For Output As #1
    For i = 1 to newlastrow
       keyword = cells(i,1).value & cells(i,2).value...etc
       Print #1, keyword
    next i
Close #1

that's not the exact code but it works for what I need it to do

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mattloflin]

I read somewhere that Print instead of Write keeps the "" out in that process. It takes out saving a new workbook and all you do is click play and it saves it to the right location and from there I just import it into the application. Nothing else to it.

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mattloflin]

i'm back at work here is the actual coding

    Dim record As String
    Open NewName For Output As #1
    newlastrow = Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row
    
    For i = 1 To newlastrow
        record = Cells(i, 1).Value & Cells(i, 2).Value & Cells(i, 3).Value & Cells(i, 4).Value & Cells(i, 5).Value & Cells(i, 6).Value
    Print #1, record
    Next i
    Close #1

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mscallisto]

'Create an text file with blank delimeters
'of course you know that any cell with an embedded blank
'will be a problem

Sub BlankDelimeter()
'Create a text file with each row cell delimited with blank

Dim intUsedrows As Integer
Dim intUsedcolumns As Integer

Open "C:\OutputFile.txt" For Output As #1

With Worksheets(1).Range("a:f")
intUsedrows = ActiveSheet.UsedRange.Rows.Count
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count

For i = 1 To intUsedrows
  For j = 1 To intUsedcolumns - 1
   Print #1, .Cells(i, j); " ";
'  or you may substitute " " above with any other delimeter "|"
  Next j
   Print #1, .Cells(i, intUsedcolumns)
  Next i
End With
Close #1
MsgBox ("Done Successfully")
End Sub

 

[mscallisto]

Sorry the above also uses the following to selectively save
a defined range to export. Remove it if you don't need it.

With Worksheets(1).Range("a:f")

 

[mattloflin]

nice. That's kind of what I got going with the code right above yours. But it does give me a different look at that kind of stuff!

Thanks!

- Matt

"Never Give a Sword to a Man Who Can't Dance