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!)

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!)

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!)

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

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

Many thanks (again), that fixed it.