[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Write Data into file

In this program i read the data from an excel file and then before the transfer jumps to the next row i am trying to write into a text file. Each TxtString is representing each row in the text file. so once i read the first row of excel sheet i need to create 8 rows in the text file. Dont know how to write the TxtString into a textfile within the loop Needs help.

Private Sub readExcelFile()
Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset
Dim strFileName As String
Dim strSheet As String, TxtString
Dim arrData As Variant
Dim mPriority As Integer
Dim mNextContactDate As Date
Dim mDebitID As String, mFirst1, mLast1, mName1, mSSN1, mFirst2, mLast2, mName2, mSSN2, mHomePhone, mWorkPhone, _
mBalance, mAddress1A, mAddress1B, mCity1, mState1, mZip1, mAccntNumber, mOrig_Clt, mList_Date, mChargeOff_Date, _
mAgency, mPlacement_Date, mBatchName, mAssignment, mDopenDate, mDLPayDate, mSortName
strFileName = "C:\REMITandNSF\SAMPLEPLACEMENTFILE_mapping.xls"
strSheet = "Sheet1"   'name of worksheet within the Excel file
Set objConn = New ADODB.Connection
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFileName & ";Extended Properties=""Excel 8.0;"""
mNextContactDate = Date
Set objRec = New ADODB.Recordset
strtest = "SELECT * FROM [" & strSheet & "$]"
objRec.Open "SELECT * FROM [" & strSheet & "$]", objConn, adOpenKeyset, adLockReadOnly
Do While Not objRec.EOF
    If Not IsNull(objRec.Fields(0).Value) Then
        mDebitID = objRec.Fields(0).Value
    Else
        mDebitID = ""
    End If

    If Not IsNull(objRec.Fields(1).Value) Then
        mFirst1 = objRec.Fields(1).Value
    Else
        mFirst1 = ""
    End If

    If Not IsNull(objRec.Fields(2).Value) Then
        mLast1 = objRec.Fields(2).Value
    Else
        mLast1 = ""
    End If

    If Not IsNull(objRec.Fields(3).Value) Then
        mName1 = objRec.Fields(3).Value
    Else
        mName1 = ""
    End If

    If Not IsNull(objRec.Fields(4).Value) Then
        mSSN1 = objRec.Fields(4).Value
    Else
        mSSN1 = ""
    End If

    If Not IsNull(objRec.Fields(5).Value) Then
        mFirst2 = objRec.Fields(5).Value
    Else
        mFirst2 = ""
    End If

    If Not IsNull(objRec.Fields(6).Value) Then
        mLast2 = objRec.Fields(6).Value
    Else
        mLast2 = ""
    End If

    If Not IsNull(objRec.Fields(7).Value) Then
        mName2 = objRec.Fields(7).Value
    Else
        mName2 = ""
    End If

    If Not IsNull(objRec.Fields(8).Value) Then
        mSSN2 = objRec.Fields(8).Value
    Else
        mSSN2 = ""
    End If

    If Not IsNull(objRec.Fields(9).Value) Then
        mHomePhone = objRec.Fields(9).Value
    Else
        mHomePhone = ""
    End If

    If Not IsNull(objRec.Fields(10).Value) Then
        mWorkPhone = objRec.Fields(10).Value
    Else
        mWorkPhone = ""
    End If

    If Not IsNull(objRec.Fields(11).Value) Then
        mBalance = objRec.Fields(11).Value
    Else
        mBalance = ""
    End If

    If Not IsNull(objRec.Fields(12).Value) Then
        mAddress1A = objRec.Fields(12).Value
    Else
        mAddress1A = ""
    End If

    If Not IsNull(objRec.Fields(13).Value) Then
        mAddress1B = objRec.Fields(13).Value
    Else
        mAddress1B = ""
    End If

    If Not IsNull(objRec.Fields(14).Value) Then
        mCity1 = objRec.Fields(14).Value
    Else
        mCity1 = ""
    End If

    If Not IsNull(objRec.Fields(15).Value) Then
        mState1 = objRec.Fields(15).Value
    Else
        mState1 = ""
    End If

    If Not IsNull(objRec.Fields(16).Value) Then
        mZip1 = objRec.Fields(16).Value
    Else
        mZip1 = ""
    End If

    If Not IsNull(objRec.Fields(17).Value) Then
        mAccntNumber = objRec.Fields(17).Value
    Else
        mAccntNumber = ""
    End If

    If Not IsNull(objRec.Fields(18).Value) Then
        mOrig_Clt = objRec.Fields(18).Value
    Else
        mOrig_Clt = ""
    End If

    If Not IsNull(objRec.Fields(19).Value) Then
        mList_Date = objRec.Fields(19).Value
    Else
        mList_Date = ""
    End If

    If Not IsNull(objRec.Fields(20).Value) Then
        mChargeOff_Date = objRec.Fields(20).Value
    Else
        mChargeOff_Date = ""
    End If

    If Not IsNull(objRec.Fields(21).Value) Then
        mAgency = objRec.Fields(21).Value
    Else
        mAgency = ""
    End If

    If Not IsNull(objRec.Fields(22).Value) Then
        mPlacement_Date = objRec.Fields(22).Value
    Else
        mPlacement_Date = ""
    End If

    If Not IsNull(objRec.Fields(23).Value) Then
        mBatchName = objRec.Fields(23).Value
    Else
        mBatchName = ""
    End If

    If Not IsNull(objRec.Fields(24).Value) Then
        mAssignment = objRec.Fields(24).Value
    Else
        mAssignment = ""
    End If

    If Not IsNull(objRec.Fields(25).Value) Then
        mDopenDate = objRec.Fields(25).Value
    Else
        mDopenDate = ""
    End If

    If Not IsNull(objRec.Fields(26).Value) Then
        mDLPayDate = objRec.Fields(26).Value
    Else
        mDLPayDate = ""
    End If
    mPriority = 2
    mDebitID = ""
    mFirst1 = ""
    mLast1 = ""
    mName1 = ""
    mSSN1 = ""
    mFirst2 = ""
    mLast2 = ""
    mName2 = ""
    mSSN2 = ""
    mHomePhone = ""
    mWorkPhone = ""
    mBalance = ""
    mAddress1A = ""
    mAddress1B = ""
    mCity1 = ""
    mState1 = ""
    mZip1 = ""
    mAccntNumber = ""
    mOrig_Clt = ""
    mList_Date = ""
    mChargeOff_Date = ""
    mAgency = ""
    mPlacement_Date = ""
    mBatchName = ""
    mAssignment = ""
    mDopenDate = ""
    mDLPayDate = ""
    objRec.MoveNext
Loop
TxtString = "DBTRADDR" & "," & mAddress1A & "," & mAddress1B & "," & mCity1 & "," & mState1 & "," & mZip1 _
& "," & """"""
TxtString = "DBTREMPL" & mName1 & "," & mAddress1A & "," & mAddress1B & "," & mCity1 & "," & mState1 _
& mZip1 & "," & "" & "," & """"""
TxtString = "DBTRHPHN" & "," & mHomePhone
TxtString = "DBTRPHON" & "," & mHomePhone & "," & "Home"
TxtString = "DBTRPHON" & "," & mWorkPhone & "," & "Work"
TxtString = "DBTRPRIM" & "," & mName1 & "," & mSSN1 & "," & """""" & """""" & "," & "C" & "," & "OXF" _
& "N" & "," & mPriority & "," & mName1 & "," & mNextContactDate & "ENG"
TxtString = "DBTRSCND" & "," & mName2 & "," & mSSN2 & "," & """""" & """"""
TxtString = "DBTRWPHON" & "," & mWorkPhone

objConn.Close
Set objRec = Nothing
Set objConn = Nothing
End Sub
   
Private Sub Form_Load()
readExcelFile
End Sub
0
mustish1
Asked:
mustish1
  • 3
1 Solution
 
sirbountyCommented:
You're trying to create a file based upon the read data?
Open "C:\Output.txt" For Append As #1  'open the file


Then for every time you want to write, use

Print #1, TxtString

'dont' forget to close:
Close #1
0
 
mustish1Author Commented:
Thanks. Just want to know is this is correct
Open "C:\Output.txt" For Append As #1  'open the file
  Print #1, TxtString
  Print #1, TxtString
  Print #1, TxtString
  Print #1, TxtString
  Print #1, TxtString
  Print #1, TxtString
  Print #1, TxtString
  Print #1, TxtString
Close #1
0
 
sirbountyCommented:
Well, you probably want to place this first:
TxtString = "DBTRADDR" & "," & mAddress1A & "," & mAddress1B & "," & mCity1 & "," & mState1 & "," & mZip1 _
& "," & """"""
TxtString = "DBTREMPL" & mName1 & "," & mAddress1A & "," & mAddress1B & "," & mCity1 & "," & mState1
Then write TxtString (for your 'header').

Then in your loop repeat the process (to write the data lines).

If you're wanting to write a specific 8 lines, use a loop:

For x = 0 to 7
  Print #1, "Data" 'TxtString?
Next x
0
 
VBRocksCommented:
A good idea would be to use the FileSystemObject.  All you have to do is add a reference to your project as follows:

Click the Project menu, then "References..."  Scroll down until you find Microsoft Scripting Runtime, and check that to add it.

Then, in your code you can use it as follows:

Private FSO As New FileSystemObject
Dim ts as TextStream
Set ts= FSO.OpenTextFile("C:\Output.txt", ForWriting , True)  'You can also use "ForAppending" instead of "ForWriting"

'Then, write it out like this:
ts.WriteLine TxtString

'When you are all done writing, close the file:
ts.Close

'Cleanup:
Set ts = Nothing
0
 
sirbountyCommented:
Happy to help - thanx for the grade! :^)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now