ExcelBanter - Double Double Quotes

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Double Double Quotes (https://www.excelbanter.com/excel-programming/391601-double-double-quotes.html)

Double Double Quotes

I've written a VBA macro to convert a file from one format into
another.

It works OK but one thing I noticed is that all the double quotes in
the source file and duplicated in the object file, for example.

"ACCOUNT" becomes ""ACCOUNT""

Anyone know why this is happening and how I can stop it?

Thanks.

(I can post the code on here but I'm sure it's nothing I've done, no
really!)

Double Double Quotes

Probably because the code is adding quotes to a string that already has
quotes.

Post the code.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
oups.com...
I've written a VBA macro to convert a file from one format into
another.

It works OK but one thing I noticed is that all the double quotes in
the source file and duplicated in the object file, for example.

"ACCOUNT" becomes ""ACCOUNT""

Anyone know why this is happening and how I can stop it?

Thanks.

(I can post the code on here but I'm sure it's nothing I've done, no
really!)

Double Double Quotes

use a fixed width file format. For delimited, it is parking the delimiter.

--
Regards,
Tom Ogilvy

" wrote:

I've written a VBA macro to convert a file from one format into
another.

It works OK but one thing I noticed is that all the double quotes in
the source file and duplicated in the object file, for example.

"ACCOUNT" becomes ""ACCOUNT""

Anyone know why this is happening and how I can stop it?

Thanks.

(I can post the code on here but I'm sure it's nothing I've done, no
really!)

Double Double Quotes

Ok, here it is, thanks for looking: As you'll notice I'm not a great
VBA coder, please forgive my newbishness!

Public Sub ConvertFormat()
' This will convert the old format of FOS file into new format

' Read each cell in turn
iInsert = 0
iOffset = 0
iIndent = 0

For Each rCell In Range("A1:A500")

' first check which column we are getting the data from

sRowPrint = "True"

For iOffsetLoop = 0 To 2
iOffset = 99

If Len(rCell.Offset(0, iOffsetLoop).Value) 0 Then
iOffset = iOffsetLoop
sThisRow = rCell.Offset(0, iOffset).Value
iData = 1
iOffsetLoop = 2
End If

Next iOffsetLoop

If iOffset = 99 Then
' this must be a blank line, or the end of the file so don't
do anything
Else

' Check for brackets, semi colons etc

iOpenBrackets = InStr(1, sThisRow, "{")
iCloseBrackets = InStr(1, sThisRow, "}")

If iOpenBrackets 0 And iCloseBrackets = 0 Then
iIndent = iIndent + 1
End If

If iOpenBrackets 0 And iCloseBrackets 0 Then
' get the data, but only if there is something after the
bracket

If Len(sThisRow) = iOpenBrackets Then
' Nothing to do, it's the last char anyway
Else
' Set flag so the data isn't output twice
sRowPrint = "False"

sRemaining = sThisRow

sThisRow = Mid(sThisRow, 1, iOpenBrackets)

'Now loop through splitting up the string with ";" as
delimiter

For iColonLoop = 1 To 20

iOpenPos = InStr(1, sRemaining, "{")

If iOpenPos 0 Then

iIndent = iIndent + 1
iColonPos = 0
sNextRow = Mid(sRemaining, 1, iOpenPos)
sRemaining = Mid(sRemaining, iOpenPos + 1)
Else
iColonPos = InStr(1, sRemaining, ";")
End If

If iColonPos 0 Then
sNextRow = Mid(sRemaining, 1, iColonPos)
sRemaining = Mid(sRemaining, iColonPos + 1)

'see if that was the last ";"

iCheckEnd = InStr(1, sRemaining, ";")

If iCheckEnd = 0 Then
iIndent = iIndent - 1
End If

End If

If iColonPos = 0 And iOpenPos = 0 Then
'must be the closing "}"
sNextRow = "}"
'iIndent = iIndent - 1
iColonLoop = 20
End If

sOut = sOut & Trim(sNextRow) & Chr(10)

If iIndent = 1 Then
sOut = sOut & Chr(9)
End If

If iIndent = 2 Then
sOut = sOut & Chr(9) & Chr(9)
End If

If iIndent = 3 Then
sOut = sOut & Chr(9) & Chr(9) & Chr(9)
End If

Next iColonLoop

End If

End If

If iOpenOnly = 1 Then

Else

End If

If iOpenBrackets = 0 And iCloseBrackets 0 Then
iIndent = iIndent - 1
End If

If sRowPrint = "True" Then

If Mid(rCell, 1, 12) = "// Converted" Then
' this line wants omitting
Else
sOut = sOut & Trim(sThisRow) & Chr(10)
End If

If iIndent = 1 Then
sOut = sOut & Chr(9)
End If

If iIndent = 2 Then
sOut = sOut & Chr(9) & Chr(9)
End If

If iIndent = 3 Then
sOut = sOut & Chr(9) & Chr(9) & Chr(9)
End If

End If

End If

Next rCell

'get the filename to be saved

sFileName = Range("A6").Value
iFileNameLen = Len(sFileName) - 12
sFileName = Mid(sFileName, 11, iFileNameLen)

sFile = "z:\\" & sFileName & ".FOS"

iFileNum = FreeFile
Open sFile For Output As iFileNum
Write #iFileNum, sOut
Close #iFileNum

MsgBox "The file has been converted and saved in the new format as " &
sFile & ". Please load this file instead."

End Sub

Double Double Quotes

Try changing the line

Write #iFilenum, sOut

to

Print #iFilenum, sOut

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
ps.com...
Ok, here it is, thanks for looking: As you'll notice I'm not a great
VBA coder, please forgive my newbishness!

Public Sub ConvertFormat()
' This will convert the old format of FOS file into new format

' Read each cell in turn
iInsert = 0
iOffset = 0
iIndent = 0

For Each rCell In Range("A1:A500")

' first check which column we are getting the data from

sRowPrint = "True"

For iOffsetLoop = 0 To 2
iOffset = 99

If Len(rCell.Offset(0, iOffsetLoop).Value) 0 Then
iOffset = iOffsetLoop
sThisRow = rCell.Offset(0, iOffset).Value
iData = 1
iOffsetLoop = 2
End If

Next iOffsetLoop

If iOffset = 99 Then
' this must be a blank line, or the end of the file so don't
do anything
Else

' Check for brackets, semi colons etc

iOpenBrackets = InStr(1, sThisRow, "{")
iCloseBrackets = InStr(1, sThisRow, "}")

If iOpenBrackets 0 And iCloseBrackets = 0 Then
iIndent = iIndent + 1
End If

If iOpenBrackets 0 And iCloseBrackets 0 Then
' get the data, but only if there is something after the
bracket

If Len(sThisRow) = iOpenBrackets Then
' Nothing to do, it's the last char anyway
Else
' Set flag so the data isn't output twice
sRowPrint = "False"

sRemaining = sThisRow

sThisRow = Mid(sThisRow, 1, iOpenBrackets)

'Now loop through splitting up the string with ";" as
delimiter

For iColonLoop = 1 To 20

iOpenPos = InStr(1, sRemaining, "{")

If iOpenPos 0 Then

iIndent = iIndent + 1
iColonPos = 0
sNextRow = Mid(sRemaining, 1, iOpenPos)
sRemaining = Mid(sRemaining, iOpenPos + 1)
Else
iColonPos = InStr(1, sRemaining, ";")
End If

If iColonPos 0 Then
sNextRow = Mid(sRemaining, 1, iColonPos)
sRemaining = Mid(sRemaining, iColonPos + 1)

'see if that was the last ";"

iCheckEnd = InStr(1, sRemaining, ";")

If iCheckEnd = 0 Then
iIndent = iIndent - 1
End If

End If

If iColonPos = 0 And iOpenPos = 0 Then
'must be the closing "}"
sNextRow = "}"
'iIndent = iIndent - 1
iColonLoop = 20
End If

sOut = sOut & Trim(sNextRow) & Chr(10)

If iIndent = 1 Then
sOut = sOut & Chr(9)
End If

If iIndent = 2 Then
sOut = sOut & Chr(9) & Chr(9)
End If

If iIndent = 3 Then
sOut = sOut & Chr(9) & Chr(9) & Chr(9)
End If

Next iColonLoop

End If

End If

If iOpenOnly = 1 Then

Else

End If

If iOpenBrackets = 0 And iCloseBrackets 0 Then
iIndent = iIndent - 1
End If

If sRowPrint = "True" Then

If Mid(rCell, 1, 12) = "// Converted" Then
' this line wants omitting
Else
sOut = sOut & Trim(sThisRow) & Chr(10)
End If

If iIndent = 1 Then
sOut = sOut & Chr(9)
End If

If iIndent = 2 Then
sOut = sOut & Chr(9) & Chr(9)
End If

If iIndent = 3 Then
sOut = sOut & Chr(9) & Chr(9) & Chr(9)
End If

End If

End If

Next rCell

'get the filename to be saved

sFileName = Range("A6").Value
iFileNameLen = Len(sFileName) - 12
sFileName = Mid(sFileName, 11, iFileNameLen)

sFile = "z:\\" & sFileName & ".FOS"

iFileNum = FreeFile
Open sFile For Output As iFileNum
Write #iFileNum, sOut
Close #iFileNum

MsgBox "The file has been converted and saved in the new format as " &
sFile & ". Please load this file instead."

End Sub

Double Double Quotes

Many thanks (again), that fixed it.