wfm007 wrote:I am replacing double quotes with VBA code while saving excel file as a .txt file.
VBA Code:
Code: Select all
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Const MAX_PATH As Long = 260
'~~> Change this where and how you want to save the file
Const FlName = "C:\Users\.....\data.txt"
Sub Sample1()
Dim tmpFile As String
Dim MyData As String, strData() As String
Dim entireline As String
Dim filesize As Integer
tmpFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"
ActiveWorkbook.SaveAs Filename:=tmpFile _
, FileFormat:=xlText, CreateBackup:=False
Open tmpFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
filesize = FreeFile()
Open FlName For Output As #filesize
For i = LBound(strData) To UBound(strData)
entireline = Replace(strData(i), """", "")
Print #filesize, entireline
Next i
Close #filesize
End Sub
Function TempPath() As String
TempPath = String$(MAX_PATH, Chr$(0))
GetTempPath MAX_PATH, TempPath
TempPath = Replace(TempPath, Chr$(0), "")
End Function
Hum..., Okaaay..., OK-OK-OK, hum-hum...! (that's me "processing" your Post a bit loud, oops...!), so OK..., you "do" it from Excel "directly", from a 'VBA' Script, placed in a Macro, I suppose...
Yep..., why not..., interesting to know that's it's possible to do it this way, could be interesting as well for Users using the Scripting Interface with '.VBA' Code, even if that's not "exactly" (oops!) what I meant, and I'm afraid your Solution might be a little bit too "High Level" for many Users..., and I find it a little bit "cumbersome", as I think it can be done in 1 Line from iMacros directly with one fairly simple 'EVAL()' Statement with a Global 'replace()' on the Double Quotes... Even if handling Double Quotes from 'EVAL()' can sometimes be a little bit "tricky" as they need to be escaped and getting the Syntax to "work as expected" is not always completely "straightforward"...
The Solution I had in mind would be stg like...:
Code: Select all
VERSION BUILD=8820413 RECORDER=FX
TAB T=1
SET !DATASOURCE_DELIMITER #
SET !DATASOURCE Address.txt
SET !LOOP 2
SET Complete_Row EVAL("var c='{{!COL1}}'; var x,y,z; x=c.split('\"').join(''); z=x.split('#').join(','); z")
SET Complete_Col EVAL("var r='{{Complete_Row}}'; var x,y,z; z=r.split(',').join('<BR>'); z")
PROMPT COL1:<BR>_{{!COL1}}_<BR><BR>Complete_Row:<BR>_{{Complete_Row}}_<BR><BR>Complete_Col:<BR>_{{Complete_Col}}_
And yep...!, works directly, from the first Try...!
Content of the 'PROMPT':
COL1:
_Frank","Noris","42 iOpus Drive","San Diego","45001","California","United States","
fn@abc.com_
Complete_Row:
_Frank,Noris,42 iOpus Drive,San Diego,45001,California,United States,
fn@abc.com_
Complete_Col:
_Frank
Noris
42 iOpus Drive
San Diego
45001
California
United States
fn@abc.com_
For the DataSource, I used the Demo DataSource that I renamed to "Address.txt" and I (manually) added Double Quotes to the first Row of Data (=Row_2 as the File has a Header):
'Address.txt':
Code: Select all
FNAME, LNAME, ADDRESS, CITY, ZIP, STATE ID, COUNTRY ID, EMAIL
"Frank","Noris","42 iOpus Drive","San Diego","45001","California","United States","fn@abc.com"
(Tested on iMacros for FF v8.8.2, Pale Moon v26.3.3 (=FF47), Win10_x64.)
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...