No announcement yet.

Macro To Export Sheet1 As Csv File

  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro To Export Sheet1 As Csv File

    There is a similar post here:

    but the question doesn't seem to be answered.
    Mine is related but slightly different.

    What I want is to be able to call a Sub (through a shortcut), that would grab the current state of my Sheet1 (or whatever I happen to call it) and silently export its contents as a CSV file with a name that I would set in the Sub.

    (Naturally, the last thing I'd want excel to do is throw a complaint window at me about CSV file only being able to hold one sheet of data etc.)

    Then, once the csv file is exported, I'd like excel to create a dummy file - like c:\temp.txt - giving me a clear cue that the writing of the csv file has closed.


  • #2
    Re: Macro To Export Sheet1 As Csv File


    How about this?

    Sub csvfile()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile("c:\tmp.txt", True)
    For r = 1 To Range("A65536").End(xlUp).Row
        s = ""
        c = 1
            While Not IsEmpty(Cells(r, c))
                s = s & Cells(r, c) & ","
                c = c + 1
        a.writeline s 'write line
    Next r
    End Sub


    • #3
      Re: Macro To Export Sheet1 As Csv File

      This is great - thank you.

      I have added the following to the Sub:
      Set b = fs.CreateTextFile("c:\temp.txt", True)
      and at the end
      b.writeline s
      to create that dummy file I was talking about.

      I didn't know about this CreateObject function.

      I wonder if there is also a way to get the script to stop exporting rows which are more than 100 after the current cursor position.

      In other words, if my cursor is at row 300, then when I call this Sub, tmp.txt is closed after the Sub has exported rows 1 through 400.
      Last edited by al1; October 9th, 2006, 01:59.


      • #4
        Re: Macro To Export Sheet1 As Csv File

        This should do as you requested

         For r = 1 To activecell.Row +100


        • #5

          Re: Macro To Export Sheet1 As Csv File

          Your response and me finding out about ActiveCell.Row on my own were concurrent.