2

I am trying to overwrite a line to a text file using Excel VBA.
My goal is to create a line in the text file that reads: 

$bdate June 14, 2016

But I get: 

"$bdate June 14, 2016"

Removing the comma will produce a line without doublequotes, but I need the comma to be a part of the string.

Here is what I am working with: 

strstatementdate = Format(Date, "mmmm, d yyyy")

Workbooks.OpenText Filename:=strExportDir & strMacroName, Origin:=437, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
    Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
    TrailingMinusNumbers:=True

Range("A36").Value = "$bdate " & strstatementdate

ActiveWorkbook.Save

I've tried making some changes to the Workbooks.OpenText line based on Workbooks.OpenText Method (Excel) and suggestions from Opening CSV in Excel using VBA causes Data to appear in two columns instead of one
but I have been unsuccessful solving the issue.

Does anyone have any tips to solve this issue?

Community
  • 1
  • 1
Josh K.
  • 21
  • 3
  • What happens if you do `Range("A36").Value = Replace(Range("A36").Value, Chr(34), vbNullString)` instead? – Matt Cremeens Jun 14 '16 at 20:14
  • What happens if you change the text qualifier? Try using xlTextQualifierNone instead of xlTextQualifierDoubleQuote. – Ryan Wildry Jun 14 '16 at 20:17
  • Are you trying to create something like a text log file? If you are not dealing with a spreadsheet text file, like CSV or space-delimited, you shouldn't use the Workbooks class; instead look for the Microsoft Scripting Runtime library. It has a lot of basic file/folder manipulation classes. https://msdn.microsoft.com/en-us/library/bkx696eh%28v=vs.84%29.aspx – JamesFaix Jun 14 '16 at 20:21
  • Matt, Ryan, there is no change when trying. Thank you for the ideas. Jeeped, thanks for directing my attention to that thread. The PRN File type answer you linked to solves this for me. – Josh K. Jun 14 '16 at 21:42
  • Range("A36").Value = "$bdate " & strstatementdate is correct. You don't have to add the quotes because you are not writing directly to a CSV file. You are writing to an Excel Range. When you save if Excel will put in all the delimiters for you –  Jun 14 '16 at 23:44

0 Answers0