Solved

Write Data into file

Posted on 2007-04-04
5
191 Views
Last Modified: 2010-04-30
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
Comment
Question by:mustish1
  • 3
5 Comments
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 18852534
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
 

Author Comment

by:mustish1
ID: 18852579
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18852620
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 18852622
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18852636
Happy to help - thanx for the grade! :^)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now