Solved

Write Data into file

Posted on 2007-04-04
5
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

689 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