VBA, Reading File As Binary
 

I needed to change Unix EndOfLine style "only CR" to Win style "LFCR"
so I tried to run following code in VBA:

Sub Modification()
Dim ff1, ff2 As Byte
ff1 = FreeFile
ff2 = FreeFile + 1
Dim a, b As Double
Open "original.csv" For Binary As #ff1
Open "modified.csv" For Binary As #ff2

Do Until EOF(ff1)
Get #ff1, , a
If a = 10 Then
b = 13
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub

but when it gets to Get command it says "Run-time error 458, Variable
uses an Automation type not supported in Visual Basic". Can someone,
please, help me.

VBA, Reading File As Binary
 

Petr:
You were allocating incorrect data types for the variables.

Try the following, you have to define each variable seperately and the file
indicators should be at least integers and I set the input variable to a
byte.

This assumes that the unix file is not a double byte unicode file.


Sub Modification()

Dim ff1 As Integer, ff2 As Integer ' these are integers
Dim a As Byte, b As Byte 'dim each one seperately as byte

b = 13

ff1 = FreeFile
Open "c:\original.csv" For Binary As #ff1
ff2 = FreeFile ' no need +1 as gets the next free number
Open "c:\modified.csv" For Binary As #ff2

Do Until EOF(ff1) = True
Get #ff1, , a
If a = 10 Then
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub


Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Petr Bazant" wrote:

I needed to change Unix EndOfLine style "only CR" to Win style "LFCR"
so I tried to run following code in VBA:

Sub Modification()
Dim ff1, ff2 As Byte
ff1 = FreeFile
ff2 = FreeFile + 1
Dim a, b As Double
Open "original.csv" For Binary As #ff1
Open "modified.csv" For Binary As #ff2

Do Until EOF(ff1)
Get #ff1, , a
If a = 10 Then
b = 13
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub

but when it gets to Get command it says "Run-time error 458, Variable
uses an Automation type not supported in Visual Basic". Can someone,
please, help me.

VBA, Reading File As Binary
 

You don't need to read it binary if it is a text file (which you imply by
saying it is CSV in your code)


Something like the following pseudo code:

Line Input ff1, l
put #ff2, , l & vbCrLf

Untested, but I believe that should work.

Anyway, shouldn't you do

Dim ff1 as Long, ff2 As Long
Dim a as Byte, b as byte

And Just for Info:

Dim a, b as Double

Declares a as Variant, b as double

--
Regards,
Tom Ogilvy



"Petr Bazant" wrote in message
ups.com...
I needed to change Unix EndOfLine style "only CR" to Win style "LFCR"
so I tried to run following code in VBA:

Sub Modification()
Dim ff1, ff2 As Byte
ff1 = FreeFile
ff2 = FreeFile + 1
Dim a, b As Double
Open "original.csv" For Binary As #ff1
Open "modified.csv" For Binary As #ff2

Do Until EOF(ff1)
Get #ff1, , a
If a = 10 Then
b = 13
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub

but when it gets to Get command it says "Run-time error 458, Variable
uses an Automation type not supported in Visual Basic". Can someone,
please, help me.

VBA, Reading File As Binary
 

Thank you for help, it works.

Martin Fishlock napsal:
Petr:
You were allocating incorrect data types for the variables.

Try the following, you have to define each variable seperately and the file
indicators should be at least integers and I set the input variable to a
byte.

This assumes that the unix file is not a double byte unicode file.


Sub Modification()

Dim ff1 As Integer, ff2 As Integer ' these are integers
Dim a As Byte, b As Byte 'dim each one seperately as byte

b = 13

ff1 = FreeFile
Open "c:\original.csv" For Binary As #ff1
ff2 = FreeFile ' no need +1 as gets the next free number
Open "c:\modified.csv" For Binary As #ff2

Do Until EOF(ff1) = True
Get #ff1, , a
If a = 10 Then
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub


Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Petr Bazant" wrote:

I needed to change Unix EndOfLine style "only CR" to Win style "LFCR"
so I tried to run following code in VBA:

Sub Modification()
Dim ff1, ff2 As Byte
ff1 = FreeFile
ff2 = FreeFile + 1
Dim a, b As Double
Open "original.csv" For Binary As #ff1
Open "modified.csv" For Binary As #ff2

Do Until EOF(ff1)
Get #ff1, , a
If a = 10 Then
b = 13
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub

but when it gets to Get command it says "Run-time error 458, Variable
uses an Automation type not supported in Visual Basic". Can someone,
please, help me.

VBA, Reading File As Binary
 

Thanks for information.

Tom Ogilvy napsal:
You don't need to read it binary if it is a text file (which you imply by
saying it is CSV in your code)


Something like the following pseudo code:

Line Input ff1, l
put #ff2, , l & vbCrLf

Untested, but I believe that should work.

Anyway, shouldn't you do

Dim ff1 as Long, ff2 As Long
Dim a as Byte, b as byte

And Just for Info:

Dim a, b as Double

Declares a as Variant, b as double

--
Regards,
Tom Ogilvy



"Petr Bazant" wrote in message
ups.com...
I needed to change Unix EndOfLine style "only CR" to Win style "LFCR"
so I tried to run following code in VBA:

Sub Modification()
Dim ff1, ff2 As Byte
ff1 = FreeFile
ff2 = FreeFile + 1
Dim a, b As Double
Open "original.csv" For Binary As #ff1
Open "modified.csv" For Binary As #ff2

Do Until EOF(ff1)
Get #ff1, , a
If a = 10 Then
b = 13
Put #ff2, , b
End If
Put #ff2, , a
Loop
Close
End Sub

but when it gets to Get command it says "Run-time error 458, Variable
uses an Automation type not supported in Visual Basic". Can someone,
please, help me.