Solved

Write Data into file

Posted on 2007-04-04
5
196 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 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

20 Experts available now in Live!

Get 1:1 Help Now