Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Function (E2000)

    I am trying to create a function that will automatically export a range of data from a workbook to a text file. As a user closes this XL book, I want a predetermined range to append to a text file. I modified some code from Microsoft and it works great except it overrides the data currently in the text file. Here is the code:

    >>>>>>>>>>>
    Sub QuoteCommaExport()
    ' Dimension all variables.
    Dim DestFile As String
    Dim FileNum As Integer
    Dim ColumnCount As Integer
    Dim RowCount As Integer

    ' Select the Range to Export.
    Range("A1:B9").Select

    ' Target the Export file.
    DestFile = "Catatest.csv"

    ' Obtain next free file handle number.
    FileNum = FreeFile()

    ' Turn error checking off.
    On Error Resume Next

    ' Attempt to open destination file for output.
    Open DestFile For Output As #FileNum

    ' If an error occurs report it and end.
    If Err <> 0 Then
    MsgBox "Cannot open filename " & DestFile
    End
    End If

    ' Turn error checking on.
    On Error GoTo 0

    ' Loop for each row in selection.
    For RowCount = 1 To Selection.Rows.Count

    ' Loop for each column in selection.
    For ColumnCount = 1 To Selection.Columns.Count

    ' Write current cell's text to file with quotation marks.
    Print #FileNum, """" & Selection.Cells(RowCount, _
    ColumnCount).Text & """";

    ' Check if cell is in last column.
    If ColumnCount = Selection.Columns.Count Then
    ' If so, then write a blank line.
    Print #FileNum,
    Else
    ' Otherwise, write a comma.
    Print #FileNum, ",";
    End If
    ' Start next iteration of ColumnCount loop.
    Next ColumnCount
    ' Start next iteration of RowCount loop.
    Next RowCount

    ' Close destination file.
    Close #FileNum
    End Sub
    >>>>>>>>>>>>

    As indicated, if I do it more than once, the data exported the first time are overwritten. Can I modify this code to append or is there other code I should use?
    Thanks!

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

    Re: Export Function (E2000)

    Try changing the line

    Open DestFile For Output As #FileNum

    to

    Open DestFile For Append As #FileNum

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Function (E2000)

    Hans,
    Thanks a bunch...it worked great!!! One final question, in XL, how can I get that to fire as the user closes the Excel workbook?
    Thanks again!

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

    Re: Export Function (E2000)

    Put the code in the Workbook_BeforeClose event:

    - Activate the Visual Basic Editor.
    - Make sure that the Project Explorer is visible (View | Project Explorer or Ctrl+R)
    - Double click the ThisWorkbook item under Microsoft Excel Objects for the workbook you are designing.
    - Enter this:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    QuoteCommaExport
    End Sub

    The BeforeClose event will be executed each time the user closes the workbook. It will call your QuoteCommaExport routine.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Function (E2000)

    Cheers Hans! That's a big help!!

Posting Permissions

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