Works for Ilogic but not VBA?
iLogic is a bastardized combination of .NET and COM.
I would avoid using the As New statement. Instead, declare your variables and then Set New in a separate line.
Here's a quick and dirty example from my project file where I read data from a carriage return delimited text file:
Private Sub load_Click()
Dim TA As Double
Dim TB As Double
Dim TC As Double
Dim TD As Double
Dim TE As Double
Dim BA As Double
Dim BB As Double
Dim BC As Double
Dim BD As Double
Dim BE As Double
Dim job As String
Dim FSO As FileSystemObject
Dim FSOfile As TextStream
Dim filePath As String
Dim box As Boolean
job = jobNum.value
filePath = "R:\Products\DWG-2007\DM~Drawer Magnets(STN)\Flange Text Files\" & job & ".txt"
Set FSO = New FileSystemObject
On Error GoTo fileNotFound
Set FSOfile = FSO.OpenTextFile(filePath)
On Error GoTo fileNotRight
If FSOfile.ReadLine <> "UDR" Then
box = MsgBox("This flange file does not match this CDM design (Round UnDrilled)", vbOKOnly, "Error")
Exit Sub
End If
TA = FSOfile.ReadLine
TB = FSOfile.ReadLine
TC = FSOfile.ReadLine
TD = FSOfile.ReadLine
TE = FSOfile.ReadLine
BA = FSOfile.ReadLine
BB = FSOfile.ReadLine
BC = FSOfile.ReadLine
BD = FSOfile.ReadLine
BE = FSOfile.ReadLine
botEqTop.value = FSOfile.ReadLine
boxTA.value = TA
boxTB.value = TB
boxTC.value = TC
boxTD.value = TD
boxTE.value = TE
boxBA.value = BA
boxBB.value = BB
boxBC.value = BC
boxBD.value = BD
boxBE.value = BE
FSOfile.Close
box = MsgBox("Flange data was loaded successfully", vbOKOnly, "Success!")
Exit Sub
fileNotFound:
box = MsgBox("Job file not found.", vbOKOnly, "Error")
Exit Sub
fileNotRight:
box = MsgBox("There was a problem reading from the job file.", vbOKOnly, "Error")
Exit Sub
End Sub
I looked at the link but I'm not sure I fully understand what you're trying to do. But first to explain a bit about why iLogic code doesn't work in VBA. iLogic is based on VB.Net and VBA is using the same engine as Visual Basic 6. They're very similar but still two different flavor of the Visual Basic language. One very big difference is that the full .Net Framework is accessible from VB.Net. The object you were trying to use in VBA is a .Net Framework object and is not available in VBA.
Here's a pure VBA program that does something somewhat similar to what you asked. It reads in a file, extracts pieces it's interested in and writes it out in a reformatted way.
First, here's the original input data.
Block, Steel, 5, Red
Cylinder, Copper, 2, Yellow
Cone, Glass, 8, Green
Mushroom, Steel, 59, Blue-Green
Tree,Mud,33, Purple
Sphere, Titanium, 11, Silver
Pyramid, Carbon Fiber, 2, Black
And here's the result file that the program creates.
Block 5 Red
Cylinder 2 Yellow
Cone 8 Green
Mushroom 59 Blue-Green
Tree 33 Purple
Sphere 11 Silver
Pyramid 2 Black
Here's the program:
Public Sub Reformat()
Dim newFilename As String
newFilename = "C:\Temp\Newfile.txt"
Dim newfile As Integer
newfile = FreeFile
Open newFilename For Output As newfile
Dim existingFilename As String
existingFilename = "C:\Temp\Test.txt"
Dim existingFile As Integer
existingFile = FreeFile
Open existingFilename For Input As existingFile
Do While Not EOF(existingFile)
' Read the existing line from the file.
Dim inputData As String
Line Input #existingFile, inputData
' Extract the bits out of the line that you want. This
' example assumes they're comma delimited.
Dim pieces() As String
pieces = Split(inputData, ",")
' Clean up any white space
Dim i As Integer
For i = 0 To UBound(pieces) - 1
pieces(i) = Trim$(pieces(i))
Next
' Using the wanted pieces, build the output string.
Dim result As String
result = pieces(0) & Space(25 - Len(pieces(0)))
result = result & pieces(2) & Space(10 - Len(pieces(2)))
result = result & pieces(3)
' Write the line, reformatted.
Print #newfile, result
Loop
Close #newfile
Close #existingFile
End Sub
