Removing double quotes when saved to text file

[Deon Lye]

I am facing some issues on removing the double quotes when saved to text file, i am still learning by the way, i am not really good in VBA, most of the time i just google and try to learn it from there. I think i almost solve the problem, but i am still having problems at the looping part where i copied all the details from another workbook 1 to workbook 2, the info from workbook 1 is having double quotes. This is the part where i don't get it. Hopefully there are someone who is willing to enlighten me for this. Thank you in advance.

I will be uploading 4 files
1. VBA code with one button.xlsm ( where all the codes and one button in here)
2. BAdvice02.txt
3. BFile02.txt
4. testing.txt(the output file where there are still a a few lines got double quotes( from Badvice02.txt))

Option Explicit

Sub test()

'Declaration
Dim b1 As Workbook
Dim b2 As Workbook
Dim b3 As Workbook
Dim s1 As Worksheet
Dim s2 As Worksheet
Dim s3 As Worksheet
Dim ADWS As Worksheet
Set b1 = Workbooks.Open("C:\New\BAdvice02.txt")
Set b2 = Workbooks.Open("C:\New\BFile02.txt")
Set s1 = b1.Sheets("BAdvice02")
Set s2 = b2.Sheets("BFile02")
Set ADWS = b2.Sheets.Add
Set s3 = b2.Sheets("Sheet1")

Dim reshlr2 As Long
Dim lr2 As Long
lr2 = s2.Cells(Rows.Count, 1).End(xlUp).Row
Dim rangecopy As Range
Dim i As Long
For i = 1 To lr2  ' looping part to get all those line into the workbook sheets for the desired outcome
   
    Set rangecopy = s2.Range(s2.Cells(i, 1), s2.Cells(i, 5))
    rangecopy.Copy

 
    reshlr2 = s3.Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox reshlr2
   

    s3.Range(Cells(reshlr2 + 1, 1), Cells(reshlr2 + 1, 5)).PasteSpecial xlPasteValues
    Set rangecopy = s1.Range(s1.Cells(i, 1), s1.Cells(i, 5))
    rangecopy.Copy
 

    s3.Range(Cells(reshlr2 + 2, 1), Cells(reshlr2 + 2, 5)).PasteSpecial xlPasteValues


Next i


b1.Close
ActiveWorkbook.SaveAs Filename:="C:\New\testing.txt", FileFormat:=xlText, CreateBackup:=False ' this is the part where how i saved it to get rid of the double quotes

End Sub

 

[p45cal]

Try this:

Sub blah()
Dim OutFilePath As String, InputFilePath1 As String, InputFilePath2 As String
Dim OutFile, InFile1, InFile2, x1, x2
'file path and name for the new text file:
OutFilePath = "C:\New\testing.txt"
OutFile = FreeFile
Open OutFilePath For Output As OutFile

'file path and name for the 1st input file:
InputFilePath1 = "C:\New\BAdvice02.txt"
InFile1 = FreeFile
Open InputFilePath1 For Input As InFile1

'file path and name for the 2nd input file:
InputFilePath2 = "C:\New\BFile02.txt"
InFile2 = FreeFile
Open InputFilePath2 For Input As InFile2

Do Until EOF(InFile2)
  Line Input #InFile1, x1
  Line Input #InFile2, x2
  Print #OutFile, x1
  Print #OutFile, x2
Loop
Close  ' OutFile
End Sub

There are also solutions using the File System Object.
I've done nothing about the possibility of there being a different number of lines in the two files - it looks to BFile02.txt to decide how many lines to process (as your code does).
I note also that your code copies 5 columns of data at a time, even though your sample data only ever occupies one column. I suspect if there are more columns my current offering will copy them all. You may need to test and confirm this.
This code will silently overwrite an existing testing.txt file.

 

[Deon Lye]

OMG, it works perfectly man, Thank you so much. Can you explain to me on how this part works?

DoUntil EOF(InFile2)
LineInput #InFile1, x1
LineInput #InFile2, x2
Print #OutFile, x1
Print #OutFile, x2
Loop

 

[p45cal]

Do Until EOF(InFile2) 'keep looping until we get to the End Of File of BFile02.txt
  Line Input #InFile1, x1 'take next line of BAdvice02.txt and put it into variable x1
  Line Input #InFile2, x2 'take next line of BFile02.txt and put it into variable x2
  Print #OutFile, x1 'take contents of x1 and add it as the next line of testing.txt
  Print #OutFile, x2 'take contents of x2 and add it as the next line of testing.txt
Loop

I suppose I could have used one variable:

Do Until EOF(InFile2)
  Line Input #InFile1, x1
  Print #OutFile, x1
  Line Input #InFile2, x1
  Print #OutFile, x1
Loop