Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Export fr Excel to txt file (VBA Excel 2003)

    Hi,

    Im trying to export three columns of data from (A-C) from excel to a text file. My script is as follows;

    Public Sub Exportcases()

    Dim StrData As String
    Dim StrSoldata As String
    Dim iDelimiter As Integer
    Dim iRow As Integer
    Dim iLength As Integer


    Open "C:ExportLog.txt" For Output As #1


    iRow = 1

    While ActiveSheet.Cells(iRow, 3) <> ""
    StrData = ActiveSheet.Cells(iRow, 2)
    StrSoldata = ActiveSheet.Cells(iRow, 3)
    iDelimiter = InStr(StrData, "")
    iLength = Len(StrData)
    StrData = Right(StrData, iLength - iDelimiter)
    Print #1, StrData & "|" & StrSoldata
    iRow = iRow + 1
    Wend

    Close #1

    MsgBox "data exported"

    I know this only exports columns B and C and its easy to change or amend to export all the three columns,i.e script fro each column, but Im sure there is a more straight forward script line to test how many columns there are and export each row across all columns, but Im not sure how to put this together.

    Any assistance would be grately appreciated.

    Regards.
    Lee

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Export fr Excel to txt file (VBA Excel 2003)

    Something like this should give you an idea:

    <pre>Public Function LastRowInSheet(wks As Worksheet) As Long
    LastRowInSheet = 1
    On Error Resume Next
    With wks.UsedRange
    LastRowInSheet = .Cells.Find(what:="*", After:=.Cells(1), _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    End With
    End Function
    Public Function LastColumnInSheet(wks As Worksheet) As Long
    LastColumnInSheet = 1
    On Error Resume Next
    With wks.UsedRange
    LastColumnInSheet = .Cells.Find(what:="*", After:=.Cells(1), _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    End With
    End Function

    Public Sub Exportcases()

    Dim strOutput As String
    Dim iDelimiter As Integer
    Dim lngRow As Long, lngCol As Long
    Dim lngLastRow As Long, lngLastColumn As Long
    Dim iLength As Integer

    lngLastRow = LastRowInSheet(ActiveSheet)
    lngLastColumn = LastColumnInSheet(ActiveSheet)
    Open "C:ExportLog.txt" For Output As #1

    With ActiveSheet
    For lngRow = 1 To lngLastRow
    strOutput = vbNullString
    For lngCol = 1 To lngLastColumn
    strOutput = strOutput & "|" & .Cells(lngRow, lngCol).Value
    Next lngCol
    Print #1, Mid$(strOutput, 2)
    Next lngRow
    End With

    Close #1

    MsgBox "data exported"
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Export fr Excel to txt file (VBA Excel 2003)

    Thanks for the help Rory.

    I was under the impression though that I only needed an extra line or two of script to check data in each column of a row and the moment it finds an empty cell in a column then the macro moves to the next row.....It doesnt need to check each column of the entire workbook, which I may have inadvertently suggested in my original post.

    Am I correct to assume the code you kindly provided checks all the columns in the worksheet? As I only need to export the data until an empty column cell is found and then move to the row below.

    Regards.
    Lee

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export fr Excel to txt file (VBA Excel 2003)

    It might help if you explained why you're doing this. Can't you just save the worksheet as a text file?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Export fr Excel to txt file (VBA Excel 2003)

    Yup - I misunderstood your requirement!
    How about this version:
    <pre>Public Function LastRowInSheet(wks As Worksheet) As Long
    LastRowInSheet = 1
    On Error Resume Next
    With wks.UsedRange
    LastRowInSheet = .Cells.Find(what:="*", After:=.Cells(1), _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    End With
    End Function

    Public Sub Exportcases()

    Dim strOutput As String
    Dim iDelimiter As Integer
    Dim lngRow As Long, lngCol As Long
    Dim lngLastRow As Long
    Dim iLength As Integer

    lngLastRow = LastRowInSheet(ActiveSheet)
    Open "C:ExportLog.txt" For Output As #1

    With ActiveSheet
    For lngRow = 1 To lngLastRow
    strOutput = vbNullString
    lngCol = 1
    Do Until Len(.Cells(lngRow, lngCol).Value) = 0
    strOutput = strOutput & "|" & .Cells(lngRow, lngCol).Value
    lngCol = lngCol + 1
    Loop
    Print #1, Mid$(strOutput, 2)
    Next lngRow
    End With

    Close #1

    MsgBox "data exported"
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Export fr Excel to txt file (VBA Excel 2003)

    Yup, thats the one. Many thanks for your time and help Rory, it works a treat.

    Regards.
    Lee

  7. #7
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Export fr Excel to txt file (VBA Excel 2003)

    Hi Hans,

    I basically need to populate a text file with delimited data as the text file is then going to be used to populate another workbook with data. Ordinarily I would just copy and paste but I need to insert a delimiter between the differing data.

    Once established, the text file in question will constantly have data being sent to it from today onwards. It is just the initial data that has been produced to date that
    needs to be moved.

    I hope the above doesnt sound too confusing !!

    Rory's macro has done the job just right.

    Thanks for your time though Hans.

    Regards.
    Lee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •