Announcement

Collapse
No announcement yet.

Macro To Export Sheet1 As Csv File

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro To Export Sheet1 As Csv File



    There is a similar post here:
    http://www.ozgrid.com/forum/showthre...CSV+file+macro

    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.

    Thanks

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

    Hello,

    How about this?

    Code:
    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
            Wend
        a.writeline s 'write line
    Next r
       
    End Sub

    Comment


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

      This is great - thank you.

      I have added the following to the Sub:
      Code:
      Set b = fs.CreateTextFile("c:\temp.txt", True)
      and at the end
      Code:
      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.

      Comment


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

        This should do as you requested

        Code:
         For r = 1 To activecell.Row +100

        Comment


        • #5


          Re: Macro To Export Sheet1 As Csv File

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

          Comment

          Working...
          X